Apps Script: How to Append Multiple Rows in Google Sheets

It is often necessary to append multiple rows to a sheet in Google Sheets via Apps Script. Unfortunately, the built-in function Sheet.appendRow() can append only one row at a time.

Of course, you could loop through the rows and append them one by one like this:

1const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Some Sheet")
2const rows = [...]
3
4rows.forEach(row => sheet.appendRow(row))

However, this approach has several drawbacks:

1. It is slow: each append operation needs time, which is manageable when appending 2-3 rows but becomes inefficient with 100 or more.

2. Each append will trigger formulas that use this range making it even slower and delaying next append operations. further slowing down the process and delaying subsequent append operations. In cases with abundant data/formulas, the delay can stretch into tens of seconds or even minutes.

3. There’s no guarantee that the script will successfully append all rows. Each Apps Script operation, especially interacting with external resources, is prone to random failures. It’s crucial to consider: if the script fails on line X, what will be the state of the data. Reducing the number of operations enhances reliability.

Better Solution: Custom Function

The following function implements a more efficient solution, which appends all rows in a single operation—drastically improving speed and reliability:

 1/**
 2 * Appends rows to the given sheet with the specified values.
 3 *
 4 * @param {SpreadsheetApp.Sheet} sheet - The sheet to which rows will be appended.
 5 * @param {Array<Array<*>>} rows - A 2D array containing the values to be appended. Each inner array represents a row, and each element within an inner array represents a cell value.
 6 */
 7const appendRows = (sheet, rows) => {
 8  // Early exit if there are no values to append
 9  if (rows.length === 0) return
10
11  // Get the range where the new rows should be appended:
12  // starting from the row following the last row with data, column 1,
13  // with dimensions matching the size of 'values'
14  sheet.getRange(
15    sheet.getLastRow() + 1,
16    1,
17    rows.length,
18    rows[0].length         
19  ).setValues(rows)  // Set the values in the specified range
20}

Utilizing this function is straightforward:

1const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Some Sheet")
2const rows = [...]
3
4appendRows(sheet, rows)

The preceding JSDoc annotation provides a helpful message and type hints (albeit without IDE validation) when utilizing the function.

Optimizing the process of appending multiple rows in Google Sheets is crucial for maintaining efficient and reliable script operations. While the built-in Sheet.appendRow() function serves its purpose for appending single rows, the appendRows function shared above vastly improves performance and reliability when dealing with multiple rows.

By reducing the number of operations and avoiding formula triggers on each append, this function significantly speeds up the process, especially in sheets with a large amount of data or formulas. This example demonstrates the importance of fine-tuning Google Apps Script operations to better handle larger datasets, ensuring your scripts run smoothly and reliably.