Google Sheet script won't work? (Arrays to change values in other tabs)

Question

I can't seem to get this bit of code to work in google sheets, any pointers would be greatly appreciated :)

If I am on the "SETTINGS" Sheet and change a value within the range F9:F19, then I need to save the values of F9:F19 (in Array "Results"), open the corresponding Sheets in the Array "SheetsList" and paste the corresponding values from F9:F19 to cell C34.

i.e. "Settings F9" -> "1s C34", "SETTINGS F10" -> "2s C34", "SETTINGS F11" -> "3s C34" ... etc

function rsltButton() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();  

  var dataSheet = SpreadsheetApp.getActive().getSheetByName("SETTINGS");

  var Results = dataSheet.getRange(9, 6, 11).getValues();

  var rslt = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(34, 3).getValue();

  var SheetsList = ['1s','2s','3s','4s','5s','PG','GS','W2s','W3s','BS','U15s'];

  if (activeSheet === "SETTINGS"){

    for (i=0; i<11; i++){

      SpreadsheetApp.getActive().getSheetByName(SheetsList[i]).getRange(34, 3).setValue(Results[i]);

    }
  }
}

Th script above however doesn't cause any change to the Sheets in the Array "SheetList"???

I tested your script and it works fine for me. Provided your `Results` array is not empty, the most likely reason for the sheets not being populated is that the condition `if (activeSheet === "SETTINGS")` is not fulfilled. Insert logs in your script to verify this. — ziganotschka, Aug 15 '19 at 09:43

score 0 · Answer 1 · edited Aug 15 '19 at 08:13

We need to include the var statement inside the for to use the i iterator. The final code should look similar to this:

function rsltButton() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
    .getName();
  var dataSheet = SpreadsheetApp.getActive().getSheetByName("SETTINGS");
  var Results = dataSheet.getRange(9, 6, 11).getValues();
  var rslt = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(34,
    3).getValue();
  var SheetsList = ['1s', '2s', '3s', '4s', '5s', 'PG', 'GS', 'W2s', 'W3s',
    'BS', 'U15s'
  ];
  if (activeSheet === "SETTINGS") {
    for (var i = 0; i < 11; i++) {
      SpreadsheetApp.getActive().getSheetByName(SheetsList[i]).getRange(34, 3)
        .setValue(Results[i]);
    }
  }
}

I hope that this is useful for you. Please, don't hesitate to offer us more information for further help.

While it is good practice to declare a variable as `var`, this is not something that would cause the script to fail in the present case. https://stackoverflow.com/questions/1470488/what-is-the-purpose-of-the-var-keyword-and-when-should-i-use-it-or-omit-it — ziganotschka, Aug 15 '19 at 09:47

Google Sheet script won't work? (Arrays to change values in other tabs)

1 Answers1