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);
}

There are several reasons. First of all getRangeList does not return a range and both setValue() and setValues() are range methods. — Cooper
– Cooper, Commented Jun 14, 2020 at 21:02
In addtion setValues() takes a two dimensional array and writes it into a rectangular range. The size of the range and size of the array have to be an exact match. — Cooper
– Cooper, Commented Jun 14, 2020 at 21:04

Cooper · Accepted Answer · 2020-06-14 21:20:31Z

4

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:

edited Jun 14, 2020 at 21:20

answered Jun 14, 2020 at 21:09

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Over a year ago

this is a beautiful solution, thank you so much

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related