Tag: Google Apps Script SpreadsheetApp

In my last written tutorial, I covered how to create a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script and the very last task of this service was to append rows into an archive Google Sheet.

The standard approach to appending data in Google Apps Script is to use the apendRow() method inside the Sheet class using the SpreadsheetApp Service.

This approach is limited to appending a single row to the bottom of the selected sheet and the append will only occur after the last row in the entire sheet where there is text.

There are, of course, some other approaches to appending data and finding the last row. Here are some that I have covered:

• Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Mar 2022]
• YouTube: Append a Range of Cell Values to a new Google Sheet Tab with Apps Script
• YouTube: Append range values in Google Sheets with Extra Column Data – Apps Script
• YouTube: Append Range Values to Messy Ranges with Gaps in Google Sheets – Apps Script

Sheets API Spreadsheets.values.append()

As of the time of this tutorial, there are a number of documentation errors and bugs in the constructor classes in Google Apps Script for the append method for the Sheets API advanced service.

You can find more here on my bug reports:

• Documentation Issue
• Method Issue

The Video Tutorial

You can grab a copy of the starter sheet here!

• The starter sheet

The video:

The Benefits of Sheets API Append Method

Append multiple rows

With the advanced service, you can append multiple rows of data to your sheet.

Insert Rows or Overwrite Existing Rows

We can decide if we want to insert new rows after the end of our existing data or overwrite the existing empty spaces. This is particularly handy when used in conjunction with the below.

Select a target range

Say you have a dataset that you want to update that has a bunch of summary data below it. With the append method, you can select the range of the data all the way down to just above your summary data and append to it.

Insert data as Raw Data or User Formatted

You can also choose to either display the data as raw data input as is, or allow Google Sheets to format the data as if you are manually inputting it into each cell.

An Example

We will use this example to illustrate how the method works in the remainder of the tutorial.

Here we have a sheet of appendix data (Get it).

The most recent data ends on line 24. We also have a set of summary data starting on line 44.

Our doctor usually sends batches of data about the appendix of each patient they see in a week. They will append that cumulative data to the sheet at the end of each week.

Now have a quick look at the code.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The Append Method Parameters

The basic append method looks like this:

Sheets.Spreadsheets.Values.append({values: 2d_array}, sheet_ID, range_to_append, payload_object)

In our example, you can see that we have placed this method in a JavaScript Try…Catch statement to handle any returned errors gracefully (lines 20-31).

The ‘append’ method returns an object that looks like this:

Probably the most important bit of information that you will get back from this object is the updatedRanges property. Everything else you will be able to get before making the append request. This property will give you the range of the updated data in A1Notation. From this, you may want to apply formatting or data validation.

2d Array of Data to Append

The first parameter is a little counterintuitive in that you must first declare an object which contains the values property. This property contains the 2d array of values that will be appended.

In our example, we mix up our 2d array of data to illustrate how some of the parameters may affect the data presented in the sheet. (lines 3-8)

More on this later

Spreadsheet ID

Next, we insert the Google Sheet ID of our spreadsheet.

We set this in our ssID variable. Line 2

Range To append to

The third parameter is the range to append to. This one is a little weird too but after you understand it, you’ll see that it is super handy.

Providing this range allows the Sheets API to search for the first empty row after the last row of data only in the range that you select.

This means that it will ignore other columns not within its search range that may have an end position much further down the sheet.

It also means that you can set your append between two sets of data in the same column. I illustrate this in our example above where we have a set of data running from A4:D24 and then a set of summary data from A44:B50, but we will append only after the first set of data.

In our example, we set this range to Sheet1:A5:A45.

Payload Object

The payload object or as Google calls it, the parameters, contain an object of optional parameters that you can include in your project to alter the outcome of the append or return your data in a different way.

After some considerable tests, I found that many of the parameters do not affect how the data is displayed on the sheet. However, there are three of note.

valueInputOption (mandatory)

One mandatory parameter must be included in each append call:

valueInputOption: "USER_ENTERED" // USER_ENTERED or RAW line 12

Using the USER_ENTERED method will evaluate the 2d array and append it as if the user manually entered it into Google Sheet. So this 2d array:

… will look like this:

Here you can see that the string number has been converted to an actual number in the sheet and all of the formulas have been executed.

Whereas, if we selected the RAW option then the data would look like this:

Now, the data is represented almost exactly as we had it in the code. The only difference is that the numbers have been influenced by our Google Sheets format to display ‘cm’ after the number in column B when a number is found. Note that the number in B28 is in fact text because it was a string in the code.

Further, all formulas are escaped with a “'” to display them as text.

Finally, the dates are now strings and no Google Sheets Date Number Values. These too are escaped with a  “'“.

insertDataOption (optional)

One of the cool things with this ‘append’ method is that you can choose to either overwrite existing empty cells or insert whole new rows.

The insertDataOption allows us to do this by selecting between either “OVERWRITE” or “INSERT_ROWS”.

“OVERWRITE” simply appends over the current rows so our result in the example would look like this:

Notice that the new data is written over the empty spaces below the orange line at A25:D28.

Now, let’s run the script again with “INSERT_ROWS” this time noting where the orange line is and also where the summary down the bottom starts:

You can see now that 4 rows have been inserted directly below the last row of data. This has pushed the orange line down 4 rows. The data is then inserted into these new rows.

You can also see now that the summary data down the bottom has also moved down 4 rows.

includeValuesInResponse

You can also opt-in to include the values that you just added to your sheet back into your response object. So it might look like this:

If you chose the “USER_ENTERED” valueInputOption then the returned results will be transformed as you can see in the sample above.

By default, this option is set to false.

Also, note that using this option will slow down your process because it needs to bring the appended array back in its response to you.

Conclusion

As you can see the Google Sheets API Append method is a much more versatile approach to appending data in a Google Sheet in Apps Script. Of course, the API is not limited to Apps Script, the API can be used in other programming languages too.

I would love to hear how you used this ‘append’ method in your own projects. It really gives inspiration and insight for others. Feel free to make a comment below.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~ Yagi.