Write data to multiple non-consecutive cells in google app script

Question

I've been looking on here, and youtube, and google for ages, and I still can't seem to get my script to work. here is a link to a test sheet :

test sheet

Simply put, I have lots of data in non adjacent cells that needs to be changed, and want to write a script to do it (if possible, I have lots of sheets to do). So, my thinking was to do something like this :

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheetByName("Sheet1").getRangeList(['A1','B2','C3','D4','E5']).setValues([10,9,8,7,6]);

}

Code obviously doesn't work, not sure why though. could anybody enlighten me?

Thanks,

EDIT : The only way I can manage to do it is cell by cell, like this :

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheetByName('Sheet1').getRange('A1').setValue(10);
  ss.getSheetByName('Sheet1').getRange('B2').setValue(9);
  ss.getSheetByName('Sheet1').getRange('C3').setValue(8);
  ss.getSheetByName('Sheet1').getRange('D4').setValue(7);
  ss.getSheetByName('Sheet1').getRange('E5').setValue(6);
}

user_7215091 · Answer

You can do it like this:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dA=[10,9,8,7,6];
  ss.getSheetByName("Sheet1").getRangeList(['A1','B2','C3','D4','E5']).getRanges().forEach(function(rg,i){
    rg.setValue(dA[i]);
  });
}

A method of rangeList is getRanges() and it returns an array of ranges which plays very nicely into using javascripts array methods like forEach. Within the forEach you can set each value and in this case I put the values into an array so that I could select them with the index which is the forEach's second parameter.

And the end result looks like this:

In the future I recommend that you pay close attention to the types of objects that methods return. Also if you lose your content assist display it's a sign that you are doing something and you should go back to a point where it begins again and somewhere between there and where you are right now you have made an error in your coding.

This is what content assist looks like:

Write data to multiple non-consecutive cells in google app script

1 Answers