Get Values from Spreadsheet in Parent Folder That is Located by Name

0
votes

I need to search the parent folder of the active spreadsheet to locate file with the specified name "event_form_responses" to get values from it and use those values in another sheet. Debugging the script below returns the following error, "Cannot find function getSheetByName in object export_form_responses." What am I doing wrong?

function SSbyName(){

//get current folder id
var ss = SpreadsheetApp.getActive();
var directParents = DriveApp.getFileById(ss.getId()).getParents();

while( directParents.hasNext() ) {
  var folder = directParents.next();
  var folderId = folder.getId();


//get files in folder
var parent = DriveApp.getFolderById(folderId);
var search = 'title contains "export_form_responses"';
var specificFile = parent.searchFiles(search);


while (specificFile.hasNext()) {
  var file = specificFile.next();
  var filesheet = file.getSheetByName("Sheet1");
  var responserow = filesheet.getLastRow();
  var responsecolumn = filesheet.getLastColumn();
  var rawform = ss.getSheetByName("RAWFORM");
  var target = rawform.getLastRow();
  var content = filesheet.getRange(responserow, 1, 1, responsecolumn).getValues();

  rawform.getRange(1, 1, 1, responsecolumn).setValues(content);

}
}
}
1
google-apps-scriptgoogle-sheets

1 Answers

2
votes
var filesheet = file.getSheetByName("Sheet1");

This line seems to be wrong, because file object (of Drive File class) has no method "getSheetByName". Try to use this code instead:

var filesheet = SpreadsheetApp.openById(file.getId()).getSheetByName("Sheet1");