Google Apps Script beginner.
In Google Spreadsheet,

There are two files named "TEST" and "TEST2".

Sheet names "Sheet 1", "Sheet 2" and "Sheet 3" are in each file.

"TEST" and "TEST2" have the same table.

"TEST" and "TEST2"
With sheet "Sheet 2"

Add one column between "E column" and "F column",
I want to put the item name in "E1".

Current status In the script below, only the last opened sheet is changed.
Also, when the script in "TEST" is executed, "TEST2" is of course unchanged.

"TEST" and "TEST2"
Make one row between row E and row F of "Sheet 2",
On that basis, I would like to set the value of "E1" to "Additional Item".

Applicable source code
function myFunction () {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet ();
  var sheet = spreadsheet.getActiveSheet ();
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet ();
  var objSheet = objSpreadsheet.getActiveSheet ();
  objSheet.insertColumnAfter (4);
  var range = sheet.getRange ("E1"). setValue ("Additional items");

I entered a sheet name in (), but

ReferenceError: "Sheet 2" is not defined.

It will become like this.

Thanks for your advice.

  • Answer # 1

    When working with other spreadsheets,
    Do not usegetActiveSpreadsheet ()orgetActiveSheet (),
    UseopenById (id)(oropenByUrl (url)) orgetSheetByName (name).

    function myFunction () {
      var sheets = [];
      sheets.push (SpreadsheetApp.openById ("TEST ID"). getSheetByName ("Sheet 2"));
      sheets.push (SpreadsheetApp.openById ("TEST2 ID"). getSheetByName ("Sheet 2"));
      for each (var sheet in sheets) {
        sheet.insertColumnAfter (4);
        sheet.getRange ("E1"). setValue ("Additional item");

Related articles