Append – Google Sheets API Advanced Service for Apps Script

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:

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:

The Video Tutorial

You can grab a copy of the starter sheet here!

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.

Google Sheets API advanced service example sheet

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:

Google Sheets API USER_ENTERED
Google Sheets API USER_ENTERED

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:

Google Sheets API RAW
Google Sheets API RAW

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:

Google Sheets API insertDataOption OVERWRITE
Google Sheets API insertDataOption OVERWRITE

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:

Google Sheets API insertDataOption INSERT_ROWS
Google Sheets API insertDataOption INSERT_ROWS

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.

Clear and Set Conditional Formatting Rules to a Specific Range in Google Sheets with Apps Script

I’ve created a small (pseudo) class that more easily clears and creates conditional formatting rules in a Google Sheet tab with Google Apps Script.

Why?

Well in Google Apps Script, conditional formatting rules are all or nothing.

You can only ever set, get or clear ALL rules in a specified Google Sheet tab.

So if you add a single rule to a sheet tab, all existing rules will be removed.

This means that each time you need to clear a rule or a portion of a rule or add in a new rule, you need to rebuild the entire rule set for that sheet tab.

It is an unpleasant experience.

The script below simplifies this process into some common clear and create processes for your conditional formatting.

Let me know in the comments below if you have a suggestion for another method for the class.

One last thing.

If you want to understand what is going on with the code (You are my kind of peeps), check out the video tutorial series.

The Code: Range_ConditionalFormatting()

To add this script to your own projects, I recommend that you create a new Google Apps Script file (page) and paste the code in there.

Check out the chapters below on how to use the class.

Note! Square brackets around parameters indicate optional parameters.

Class

The pseudo-class Range_ConditionalFormatting(sheet) tales one argument:

Video link: Range_ConditionalFormatting(sheet) 

Methods

Method Return Type
clearRule()
Clears a rule or rules by a single range or array or ranges, based on 3 clearing approaches.
setRule()
Sets a rule or rules while maintaining the existing rules in the Sheet tab.

clearRule(rangeOrRanges, [clearType])

Clears the conditional formatting rules in a Google Sheet tab by the selected range or ranges and clear type while maintaining the existing conditional formatting rules in the Google Sheet tab.

Video link: clearRule method

Parameters

Name Optional Type Description
rangeOrRanges Range or Range Array A SpreadsheetApp.Range or array of ranges that will be the target locations to clear.
clearType Number The way the method should clear the range (see below)

Default is clearType = 0

Clear types

  • 0 – Clears any conditional formatting rule that exactly matches the range.
  • 1 – Clears any conditional formatting rule that has a range within the target range.
  • 2 – Clears any conditional formatting rule or portions of the range of a rule that overlap the target range.

Examples

clearRule(range) – Single range and no optional clear type

Clears a conditional formatting range that exactly matches the single range provided.

Video link: clearRule(ranges) – Ranges and no optional clear type

clearRule(ranges) – Multiple ranges and no optional clear type

Clears the conditional formatting that exactly matches multiple ranges.

 

clearRule(range, clearType = 0) – Single range with clear type zero- Exact match

Clears a conditional formatting range that exactly matches the array of ranges provided. Clear type zero (0) is the default clear type.

Video link: clearRule(ranges, 0) – Exact match

clearRule(ranges, clearType = 0) – Multiple ranges with clear type zero – Exact match

Clears the conditional formatting that exactly matches multiple ranges.

clearRule(range, clearType = 1) – Single range with clear type one – Within range

Clears a conditional formatting rule range that has a range equal to or within the target range.

Video link: clearRule(ranges, 1) – Within range

clearRule(ranges, clearType = 1) – Multiple ranges with clear type zero- Within range

Clears all conditional formatting rule ranges equal to or within the target array of ranges.

clearRule(range, clearType = 2) – Single range with clear type one – Overlaps

Clears a conditional formatting rule range that has a range equal to or within the target range or rebuilds the range where any range overlaps the target range removing that portion of the range.

Video link: clearRule(ranges, 2) – Overlapping ranges

clearRule(ranges, clearType = 2) – Multiple ranges with clear type zero- Overlaps

Clears all conditional formatting rule ranges equal to or within the target array of ranges or rebuilds the range where any range overlaps the target range removing that portion of the range.

setRule(rules, [position])

The setRule method adds a conditional formatting rule or rules to an existing Google Sheet tab. It does not remove any existing rules in the selected tab.

The method has an optional position parameter that allows the user to customise where they wish to order the rule in relation to the existing rules on the Sheet tab.

Video link: setRule method

Parameters

Name Optional Type Description
rules Conditional Formatting Rules A

array of objects. This is an array of rules built with the Apps Script conditional formatting rule builder.

position Number The position of the new rule in the existing set of rules.

Default is position = -1, or the bottom of the rules array.

A position of zero (0) sets the rule to the top of the array set.

Positive numbers apply position up to max length of the rule set.

-1 for end of the rule

Rules with negative numbers are position from the last rule in reverse order up to zero.

Examples

Video Tutorial Series

You can check out the playlist for Conditional formatting here.

Apps Script – Google Sheets Conditional Formatting Playlist

The video tutorials:

  1. Clear Conditional Formatting Rules by Exact Match in Google Sheets with Apps Script
  2. Clear Conditional Formatting Rules Within a Range in Google Sheets with Apps Script
  3. Clear Conditional Formatting Rules that Overlap a Target Range in Google Sheets with Apps Script
  4. Add Conditional Formatting Rules to a Google Sheet Tab with Apps Script

Conclusion

Being such a short pseudo-class it is hardly worth slowing down your script by making this into a library. I recommend you just add it to your existing project for simplicity and performance.

If you think there are any other methods worth adding, please let me know in the comments.

I would also love to hear how you used this in your own projects.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi.

Prevent consecutive clicks of Google Sheets Image Buttons in with Lock Service and Google Apps Script

Ever seen an overzealous user madly clicking on an image button in Google Sheets to run an automation script? It’s maddening, frustrating and most importantly, may cause errors in your beautifully crafted code.

In this short tutorial, we explore how to use Google Apps Script’s Lock Service to prevent users from executing your code from your image button while the original instance of the code is still running.

Grab a copy of the starter sheet below to play along.

To the Starter Sheet

Continue reading “Prevent consecutive clicks of Google Sheets Image Buttons in with Lock Service and Google Apps Script”

Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script

This tutorial is for Google Workspace Domain accounts.

Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets.

This can be incredibly handy for project managers to see when a user completes a task or, at a glance, who edited a row.

Of course, there are some simple ways of doing this out-of-the-box within Google Sheets.

  1. A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
    Show cell history in Google Sheets
  2. If you want a more detailed history of edits on your Google Sheet workbook then you can always select the version history button in the top right of your Sheet.

    Google Sheets Version History
    The ‘clock’ icon here opens the version history page.

The problem with these options is that it is not there on the screen for the user to quickly see who edited what line.

In this tutorial, we are going to use some Google Apps Script magic to automatically add the editor’s email to the row when they click that checkbox.

Let’s dive in!

Continue reading “Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script”

How to Sort Tabs in Google Sheets with Google Apps Script

Sometimes your Google Sheet tabs can get out of hand. They can be mixed up and confusing to users. It’s often necessary to simply sort them in ascending or descending order.

In this tutorial, we will use Google Apps Script to sort tabs in Google Sheets. We’ll also use a handy menu bar to quickly run the sort.

The approach below relies on a natural sort. In a normal sort where you also have numbers say:

"2. Cheese", "10. Pizza", "1. Bananas"

Then you ran your sort, you would not get what your expected but rather:

"1. Bananas", "10. Pizza", "2. Cheese"

With a natural sort, we consider the number numerically, rather than as characters. This means our sort would come out as expected:

"1. Bananas", "2. Cheese", "10. Pizza"

You can learn more about creating the Apps Script by following the video below or you can jump down and grab a copy of your code and run it in your own project.

 

You can grab the starter Google Sheet Here:

Sorting Google Sheet Tabs – Starter Sheet 

 

Sort Tabs in Google Sheets – Video

https://youtu.be/a7l6OEkMkIg

The Code

Step 1: In your Google Sheet, Go to Extensions > Apps Script.

Step 2: copy the code from below and paste it into the edits and save.

Step 3: Next, reload the Google Sheet. You will see a menu item called Extras. Click it and you will see your two sort options.

Step 4: Go ahead and click an option.

You should see your tabs being sorted.

Custom Google Sheet tab sort menu

Step 5: You will be prompted to run authorisation of the script the first time the script is run. You can learn more about this here:

Running Google Apps Script for the first time. 

Step 6: Select an option again and you will see the tabs being sorted.

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

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Code Breakdown

onOpen()

The onOpen() function is a Google Apps Script simple trigger that runs automatically when the user opens the Google Sheet.

Line 5: In our example, we call the Spreadsheet App UI class, so that we can build the menu.

Line 8: To create a menu we use the Create Menu method and add the menu label “Extras” as an argument.

Lines 9-10: Next, we can add menus to the main menu header with the Add Item method. These menus take a menu label and then a function or an object method that will run when the menu is selected. Ensure that you don’t include parentheses in these arguments and contain them in a string.

Line 11: Finally, we need to build the menu by adding all the menu items to the UI.

You can learn more about building menus in Google Sheets, Docs and Slides here:

How to build a menu item in Google Sheets

The Sheet Tab Sorter object

The tabSorter object contains 3 properties:

  • ascending
  • descending
  • sortTab

Ascending and Descending Property Methods

Both of these property methods run the sortTabs method providing either true for ascending or false for descending as the argument.

We need to encapsulate these method calls inside their own anonymous functions so that they don’t run automatically when the menu is loaded.

ascending: function(){this.sortTabs(true)},
descending: function(){this.sortTabs(false)},

The sort Google Sheet Tabs machine (sortTabs)

The sortTabs property does all the heavy lifting in this object.

It takes a boolean as an argument that will determine if the sort is ascending or descending.

Get a list of sheets

Our first task is to get an array containing all the Google Sheets in the currently active sheet. We can do this with the SpreadSheet App Get Sheets method. This will contain an array with all the methods available for each found sheet tab.

Using Intl Collator for natural sorting

We want to sort our sheet tabs naturally just in case we have numbers in the name of the tab (e.g. “1”, “2”, “4. Cake”). We can do this with the JavaScript Intl.Collator object to sort the sheet tabs in a natural order, meaning that numeric values are sorted in numerical order and text values are sorted alphabetically. You can modify this function to use a different sorting algorithm or change the sorting order.

Line 2: Here, we create a new Intl.Collator constructor. The first optional parameter of the constructor defines the locale or language tab. We don’t need this option, so we set it to ‘undefined’.

The second parameter sets the options for the collator.

Line 3: First, we use the numeric property and set it to true. This will allow us to order the numbers properly so that 1 < 2 <10.

Line 4: Secondly, we set the sensitivity to base. This means that we do not take into account any accents or other markings on letter characters. If you are using this Google Sheets sort tab code in other languages, then you might want to modify this.

Sort Google Sheet tabs Apps Script-side

Now that we have our collator set up we use the JavaScript sort method to sort through the array of sheet names.

Line 2: Here, we use an arrow function with a standard a, b parameter set. These parameters refer to the first and second elements of comparison respectively.

Line 3: Use our collator and apply the compare method to compare the sheet names against each other.

Reversing the sort

If isAscending parameter of the sortTabs method is set to false, then we use JavaScript reverse() to reverse the array order of the sheetNames.

Updating the Google Sheet tabs to the desired order

Finally, once our array is in the desired order we need to update the Google Sheet with the new tab order.

Line 2: First, we loop through the sheet names array with the JavaScript forEach() method.

Line  3: Inside each loop, we need to set the active sheet to the currently iterated sheet.

Line 4: Finally, we move the active sheet to the next position in the sheet tabs. Note that sheet indexes start from one (1) however our array index starts from zero (0). This is why we need to add one to the index.

 

Starting the sort after a set number of tabs

Perhaps you have an instruction tab or dashboards that you want to leave to the left of your sort. Here you can update the moveActiveSheet() by increasing the value to add to the index by the desired number plus 1.

Let’s say I have an ‘Instructions’ tab and a ‘Dashboard Tab’ that I want to keep to the far left and is not included in my sort list. my moveActievSheet() function would look like this:

Here I add 1 to the zero-based index and another 2 for the two tabs I want to keep in position.

Google Sheet Tabs by Date

Check out my bonus video on sorting tabs by leading date.

The Starter Sheet

 

That’s all there is to it.

If you want to learn more about how to modify Google sheets tabs with Apps Script, check out these tutorials:

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

Changelog

2024-05-31

  • Added table of contents.
  • Added video on sorting Google Sheet Tabs by leading date.
  • Added chapter on keeping static sheet tabs to the right and sorting after them. Thanks to Cassidy for the question.