Write Data to Google Sheets using Apps Script

Photo by Lucrezia Carnelos on Unsplash

Use batch operations

If you are reading data from a Google Sheet — or writing data to it — you should not loop through the cells. Instead, you should use batch operations. (See this best practices guide for Google Apps Script.)

Writing a defined array to a sheet

To write multiple rows and columns of data to a sheet, you need to set up the data in an array. Here is an array of pets and their owners:

var array = [['Asterix', 'Tintin', 'Goofy'],
             ['Dogmatix', 'Snowy', 'Pluto']];

The first sub-array has the name of the owners and this will go in row one. The second one has the name of the dogs and this will go in row two.

Owners and Dogs

To post this array to the sheet, get the active spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Then the sheet you want to write to:

var sheet = ss.getSheetByName("Sheet1");

The range of cells you want to write to:

var range = sheet.getRange(1, 1, 2, 3);

The getRange() method uses rows and columns like this:

getRange(starting_row, starting_column, number_of_rows, number_of_columns)

And then use the setValues() method to write the array to the sheet:

range.setValues(array);

Be careful and make sure that the size of the array and the range are the same.

All together now

function definedArray() {
   var array = [['Asterix', 'Tintin', 'Goofy'],
                ['Dogmatix', 'Snowy', 'Pluto']];
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName("Sheet1");
   var range = sheet.getRange(1, 1, 2, 3);
   range.setValues(array);
}

Writing an undefined array to a sheet

What if your array does not have a fixed size? How do you make sure that no matter what the dimensions of the array, your script posts it to the sheet properly?

The trick lies in how you define your range:

var range = sheet.getRange(1, 1, array.length, array[0].length);

In the getRange() method, getRange(starting_row, starting_column, number_of_rows, number_of_columns)

• the number_of_rows is the length of your array: array.length
• the number_of_columns is the length of the first row in the array: array[0].length

All together now (again)

function definedArray() {
   var array = [['Asterix', 'Tintin', 'Goofy'],
                ['Dogmatix', 'Snowy', 'Pluto']];
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName("Sheet1");
   var range = sheet.getRange(1, 1, array.length, array[0].length);
   range.setValues(array);
}

What else would you like me to write about in the Google Apps Script world?