Google Apps Script – How to Write Data to a Single Range

google sheetsgoogle-apps-script

I'm new here (second post) and also new in Google Apps Script.

I'm trying to find out how to write data to a single range in a spreadsheet using Google Apps Script.

Looks like Google Help in the section Writing data
is not good enough

to explain to beginners, for example, how to write TEXT in cell B5.

Best Answer

You start by getting a reference to a Range with the getRange function. Once we have that, and since this range consists of a single cell, we can use the setValue function:

var range = SpreadsheetApp.getActiveSpreadsheet().getRange("B5");
range.setValue("TEXT");

... which will write TEXT to cell B5.

If you have a multi-cell range (which is more common), and you don't want to write the same value to all of the cells, you need to use the setValues function (note the plural), which takes an array of arrays ([[]]) as argument:

var range = SpreadsheetApp.getActiveSpreadsheet().getRange("B5:C5");
range.setValues([ ["This is column B", "This is column C"] ]);

In this case, we have two cells on the same row, so the outer array has a single element: the inner array, which again have two elements - one for each cell.

Compare to the next example, where we want to write to a range that spans multiple rows:

var range = SpreadsheetApp.getActiveSpreadsheet().getRange("B5:B7");
range.setValues([ ["This is row 5"], ["This is row 6"], ["This is row 7"] ]);

And finally, let's write to a multi-column, multi-row range:

var range = SpreadsheetApp.getActiveSpreadsheet().getRange("B5:C7");
range.setValues([ ["This is B5", "This is C5"], ["This is B6", "This is C6"], ["This is B7", "This is C7"] ]);

I have created an example spreadsheet to demonstrate these snippets - feel free to copy it.

Please note that any function that directly fetches or manipulates spreadsheet data directly, is slow, regardless of the amount of data involved. So keep the number of spreadsheet function calls low. If you want to update multiple cells, it is usually better to fetch the full array of values first, then update the values you want to change, and write back the whole array.

In this example, we have a spreadsheet with data from A1 to H8, but we want to update only two cells: D7 and G6. The rest of the cells should be unchanged.

var range = SpreadsheetApp.getActiveSpreadsheet().getRange("A1:H8");
var values = range.getValues();
values[6][3] = "This is D7";
values[5][6] = "This is G6";
range.setValues(values);

Note how we use range.getValues() to get an array of the existing values. Then we set the individual values in that array. Since JavaScript array indexes are 0-based, row 7 will be 6, and column D, being the 4th column, will be 3 - hence values[6][3]. Similarly, G6 will be values[5][6].

Finally, we write the array back to the range. We can update as many values we like in the array, while keeping the number of spreadsheet function calls low - only a single getValues and a single setValues.

From your comment it is probably worth mentioning how a pure function can be used from a spreadsheet. A pure function is a function that has no side effects, that is, its return value depends only on its arguments.

A pure function can be called from the spreadsheet with =functionName. Let's say you want a function multiplies an input value with 2, and returns its result. Enter this in the script editor:

function multiplyByTwo(num) {
   return num * 2;
}

In your spreadsheet, let's say in cell A2, you would enter the formula =multiplyByTwo(4). A2 will then display 8.
You could also use other cells as arguments. Let's change the formula to =multiplyByTwo(B2), and in cell B2 enter the number 6. Cell A2 will now display 12.
Here's another example for pure functions, and read more about Custom functions in Google's documentation.

You can also return a multi-dimensional array, in which case the result will span several rows/columns in the spreadsheet. The function

function multiplyByTwoAndFour(num) {
    return [[num * 2], [num * 4]];
}

can be used in cell D2 as =multiplyByTwoAndFour(3), which will cause D2 to display 6 and D3 to display 12.

Related Topic