Adding Criteria to Multiple Sheets imported from Google Drive

35 Views Asked by At

I have the below string which is working, I would like to see if I can filter the output by changing a cell in the destination sheet. All of my files in the folder are week names formatted WXX and I would like my sheet users to be able to update a cell in the reference sheet and return only the data in the file with that name.

For example in my destination sheet B5 has W09, the script would then only return data from the file named "W09" as opposed to all weeks as it is currently doing.

function getdata() {
  //declare multiple variables in one statement - Initial value will
  //be undefined
  var destinationSpreadsheet,destinationSS_ID,destsheet,destrange,file,files,
      sourcerange,sourceSS,sourcesheet,srcSheetName,sourcevalues;

  srcSheetName = "SOURCE TAB NAME";
  destinationSS_ID = "DESTINATION ID";
  files = DriveApp.getFolderById("DRIVE ID").getFiles();
  destinationSpreadsheet = SpreadsheetApp.openById(destinationSS_ID);
  destsheet = destinationSpreadsheet.getSheetByName('DESTINATION SHEET NAME');  

  while (files.hasNext()) {
    file = files.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") {
      continue;
    };
    sourceSS = SpreadsheetApp.openById(file.getId());
    sourcesheet = sourceSS.getSheetByName(srcSheetName);
    //sourcesheet.getRange(start row, start column, numRows, number of Columns)
    sourcerange = sourcesheet.getRange(1,1,sourcesheet.getLastRow()-1,12);
    sourcevalues = sourcerange.getValues();

    //Write all the new data to the end of this data
    destrange = destinationSpreadsheet.getSheetByName("DESTINATION SHEET NAME")
        .getRange(destsheet.getLastRow()+1,1,sourcevalues.length,sourcevalues[0].length);

    destrange.setValues(sourcevalues);         
  };
};
0

There are 0 best solutions below