Write multiple rows of data to Google Sheets using Apps Script

A very common use case when you're working with Apps Script is writing multiple rows of data to a Google Sheets spreadsheet. In this tutorial, I will show you two methods to append multiple rows to a spreadsheet and help you understand which method you should use and why.

Method 1: Append a single row at a time to a Google Sheets spreadsheet using Apps Script (Not recommended 👎)

In the code below, the function writeMultipleRows() appends a single row of data at a time. This is not recommended since it will result in very poor performance. A better approach is to write all rows at once to the spreadsheet. This is called a 'batch operation' or a 'batch write' and is the recommended method for writing large amounts of data to a spreadsheet.

The code uses the appendRow() method of the Sheet object to write a single row of data to the spreadsheet. To append a row, pass an array of values (corresponding to the columns) to the appendRow() method.

For example, the code below appends a row containing two values: First name and Last name.

var row =  ["First name", "Last name"];
SpreadsheetApp.getActiveSheet().appendRow(row);

//@onlyCurrentDoc

function writeMultipleRows() {
 var data = getMultipleRowsData();
 for(var i = 0; i < data.length; i++) {
   SpreadsheetApp.getActiveSheet().appendRow(data[i]);
 }
}

function getMultipleRowsData() {
 var data = [];
 for(var i =0; i < 1000; i++) {
   data.push([Math.random(), Math.random(), Math.random(), Math.random()]);
 }
 return data;
}

The video below shows you how inefficient and slow this method is. It is slow even when the number of rows being written is just 1000, which is relatively small.

Method 2: Append multiple rows at once to a Google Sheets spreadsheet using Apps Script (Recommended 👍)

The recommended way to write multiple rows of data to a Google Sheets spreadsheet using Apps Script is by using the setValues() method of a Range object.

The idea is to access the range in the spreadsheet where the data needs to be written and write all of the data to that range at once.

The function writeMultipleRows() in the code below implements this idea. First, we find out the number of existing rows in the spreadsheet using

SpreadsheetApp.getActiveSheet().getLastRow(). Then we add 1 to this value to get the row at which we should begin appending data. Then we access the range based on the size of the two-dimensional array of data that needs to be written to the spreadsheet. The value data.length tells us how many rows need to be written and data[0].length tells us how many columns are in each row.

Once we access the range, we write data to the range using the setValues() method.

//@onlyCurrentDoc

function writeMultipleRows() {
 var data = getMultipleRowsData();
 var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
 SpreadsheetApp.getActiveSheet().getRange(lastRow + 1,1,data.length, data[0].length).setValues(data);
}

function getMultipleRowsData() {
 var data = [];
 for(var i =0; i < 1000; i++) {
   data.push([Math.random(), Math.random(), Math.random(), Math.random()]);
 }
 return data;
}

The video below shows you how fast this method is. All 1000 rows are written to the spreadsheet almost instantaneously. This is because all rows are written in a single batch operation versus one by one. The concept of a batch operation is important to understand when you code to improve the performance of your scripts.

Conclusion

In this tutorial you learned how to efficiently write multiple rows of data to a Google Sheets spreadsheet using Apps Script.

This tutorial also gave you a glimpse into how much more efficient batch operations are when compared to doing things one at a time. Instead of writing rows one at a time, write all rows at once to the spreadsheet using a batch operation. This will improve performance and your users will thank you for making their workflows faster.

Thanks for reading!