Writing in a newly created Google spreadsheet using an external script

Question

I have built a short script that takes inputs from a Google Form and creates a new spreadsheet. But when I try to set that values inside the sheet, nothing happens. Not sure if this is due to my code or to the lack of authorization given this is a newly created file. Here is my code:

 var templates = DriveApp.getFilesByName('Template'); // get the files named Template (only one)
 var template = templates.next(); // get the first files in the list
 var newFile = template.makeCopy(name,newFolder); // Make a copy of the Template file and put it in NewFolder
 
 var ss = SpreadsheetApp.open(newFile);
 var sheet = ss.getSheets()[0];
 sheet.getActiveRange('B1').setValue(name);

Thanks for your help

Answer 1

I think that in your script, an error occurs at getActiveRange('B1'). Because the method getActiveRange() of Class Sheet has not arguments. Ref I think that this is the reason of your issue. In this case, an error like The parameters (String) don't match the method signature for SpreadsheetApp.Sheet.getActiveRange. occurs. I thought that the reason of nothing happens might be that from your question, the script is run by the OnSubmit event trigger might be used. In this case, please modify as follows.

From:

sheet.getActiveRange('B1').setValue(name);

To:

sheet.getRange('B1').setValue(name);

• When you modify like above and run again, the value of name is put to the cell "B2" on the 1st tab in the created Spreadsheet.

Note:

• If you want to append the values when the script is run, please modify sheet.getActiveRange('B1').setValue(name); as follows.

   sheet.appendRow([, name]); // In this case, the value is append to the column "B".
  

References:

• getActiveRange()
• getRange(a1Notation)