Creating a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script

I have a client project coming up shortly where I will be diving back into the Google Sheets API Advanced Service in Google Apps Script. As part of my refamiliarisation with the API, I was thinking about the minimum number of API calls it would take to efficiently create a Google Sheets line archiver based on a set of multi-column parameters.

Here’s the example and the starter sheet if you want to play along.

The Sample Data Google Sheet: The Space Ship Manufacturing Fleet Requests

Stardate 78183.10, it’s surprising spreadsheets have not been improved upon in the almost half millennia since their origin, but they are still everpresent while the inevitable decay of other software products is a little more than the dust of a bygone memory.

We’ve decided to create an archiving automation for our spaceship-building bids for our new space fleet. We want to remove any bids from the sheet that have been declined and are before “2025-04-30”, (Damn it! Timeline inconsistencies already!!!!).

Once, we find these rows to archive, we want to remove the selected rows and append them to our ‘Archive’ sheet.

Check out the sheet:

The Starter Sheet

So, how many API calls?

Well, it ends up being 5 API calls. This is not as great as I had hoped but, perhaps significantly faster using Google Sheets APIs batching capabilities over the standard SpreadsheetApp approach.

What are the API calls?

1. Get Basic Sheet data: First, we have a general call to get some general spreadsheet data, including an array of all Sheet tabs by ID and name and the Google Sheet’s timezone offset to ensure consistency in date processing.
2. Find Rows: Next, we need to find the target rows of data to remove and archive.
3. Copy Rows: Once the rows are found, we will need to copy those rows.
4. Append Rows: With the rows copied, we need to archive those rows by appending them to our ‘Archive’ Sheet tab.
5. Remove Rows: Finally, we need to remove the rows from our main ‘Bid’ Sheet tab.

Script Structure

As is my experience with most REST APIs, I end up building a service to handle the calls I need and slowly build them out as more services are required. In the end, the script looks like I am reinventing something like the SpreadsheetApp service; a factory data structure to work with Google Sheets easily. The key benefit here is that we can better manage batch API calls rather than relying on SpreadsheetApps service and the flush() method.

So our main script will end up being a class containing a number of access points to the Sheets API that are much easier to implement over building error-prone objects each time.

Then, we can use a procedural function to step through the stages that we want to complete in our process.

A bonus of this factory approach is that each step can be tested or used in another process on its own.

It’s probably best to first take a look a the main run code.

Before we start

You will need to add the Sheets API advanced service to your Google Apps Script project.

1. In your IDE, select Services from the sidebar.

A dialogue will appear.

1. Scroll down until you find ‘Google Sheets API’ and select it.

2. Make sure V4 is selected in the version and  (4)the identifier is ‘Sheets’.

3. Click ‘Add’.

The Main Run Code

As mentioned above, this is a procedural function that steps through each stage of the process of archiving our data.

Let’s take a look:

At a glance, you can probably already see that this function could easily be modified into another process or indeed into a UI for the spreadsheet for users to archive their own data how they would prefer using a dialogue or sidebar or indeed using another Google Sheet tab and a button image.

Building a Data Log in Google Sheets with Google Apps Script

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

Code Breakdown

Queries

Lines 8-22 – First, we set the queries for our archiving. As a part of our Class build for our Sheets API factory, we will create some JSDoc type definitions, to help us maintain our code structure and provide code completion hints.

The query consists of an array of FindQueries objects. Each query object requires a column letter, query string or number a type of query (For example, equals, less than, not equal to greater than, etc) and an optional boolean if the query is a date.

Our first query ensures that we check column ‘J’ for any cell that has been marked “Declined”.

The next, query, checks column ‘F’ for any cell that has a Bid Submission Date that is less than or equal to 30 May 2025.

These queries will occur sequentially from the first query set in the array. So first we will find the declined rows and then find any of those rows that are below or equal to the target date.

Variables

Lines 23-24: Next, we set our variables identifying the source sheet name, ‘Bids’ and the destination sheet name ‘Archive’. We also set the start row for our query search so that we are not wasting processing time or confusing the query with any potential summary or header data that we may have.

Line 28: Now, we add the ID for the spreadsheet.

Line 29: Finally, we create a new instance of our Sheets API factory class that we have named, ‘SsReq’. This class requires our spreadsheet ID as a parameter.

Query the Bid Sheet

Now we can send our query to the Sheets API via our ssReq instance.

Lines 31-34: Here, we create a ‘rows’ variable that will store an array of row numbers for any met queries.
To do this, we call the findRows method and pass in our ‘Bids’ sheet name, our queries array and the row start.

Before we move on, we check to see if we did not find any rows matching our query. If so, we just return the function. We don’t need to do anything else.

Copy the Target Rows

With our new list of found target rows, we can now copy them to add them to our archive sheet.

Lines 36-37: Calling the copyRows method providing the ‘Bids’ sheet name and our found rows, we can now store the data of those rows in a 2d array in our variable rowVals.

Appending the old rows to our Archive Sheet

Next, we can append our array of old row data to the ‘Archive’ sheet.

Lines 39-40:  Here we use the appendRows  method providing the ‘Archive’ sheet name and the 2d array of row values. This will append the array to the bottom of this sheet.

Remove the Old Rows from the Bids Sheet

Finally, we remove the old data from our working ‘Bids’ sheet.

Lines 42-23: We call the removeRows() method passing in the ‘Bids’ sheet name and the array of found row numbers. This will delete these target rows from the sheet.

Some things to consider

If you have a very active sheet with multiple editors, it may be important for you to prevent users from editing the target and destination sheets or running the script simultaneously. Here you may need to consider implementing:

1. LockService: This prevents concurrent access to sections of the code based on certain permission types. More examples here.
2. Protection Class: Here you can temporarily (or more permanently) protect ranges and sheets using the SpreadsheetApp Protection Class. Note that this may significantly slow down the processing of your script.

The Sheets API Advance Service factory class SsReq

Let’s now take a look at the SsReq class that we built to interface with the Sheets API advanced service.

The first thing you will probably notice is that it is far more verbose than using SpreadSheetApp. Probably, the best way to consider this class is that it is similar to what SpreadsheetApp would look like behind the scenes.

The code

As you can see, there is a lot of clicky-clacky here that we have neatly abstracted away from the main archiveBids() function.

In the following sections, I will briefly cover what is going on in each method, constructor and pseudo-enumerator. However, I intend to go into detail on each method as its own process in future tutorials, so stay tuned.

The main Constructor

Lines 21-50

When an instance of SsReq is created (new SsReq(ssID)),  we store the spreadsheet ID for other other calls to Google Sheets API.

Reducing API Calls

Calls to the API take time and we are all about speeding up our script processing when moving from SpreadsheetApp to Google Sheets API Advanced Service.

As a part of our initial process, we will want to retrieve and store some basic spreadsheet data locally to apply to other services.

First, we store the spreadsheet’s timezone. This is particularly important when working with international teams that use Google Sheets in different timezones as your Google Apps Script project. We will need to convert timezones to make any comparisons among dates for our queries without making timezone errors because we did not check this correctly.

Secondly, we will retrieve a list of all Google Sheet tabs by their name and their id. Weirdly, the Google Sheets API uses either the Sheet Names OR the Sheet ID depending on the type of request. So instead of having to make an additional request for the Sheet ID before completing another API request, we will store all of these sheet name-id values in an array at the beginning.

More on getting Sheet Names and IDs with Sheets API Advanced Service:

Get Google Sheet Tab Name and ID with Google Sheets API Advanced Service with Apps Script

Converting dates between JS date and Google Sheets date-types dateJStoSheetsDateVal_(date)

Lines 52-79

In Google Sheets, dates are stored as floating point numbers where the whole number is a day since 1899-12-30 (based on the old Lotus 123 convention). We can choose to retrieve the dates as displayed in the sheet tab or as this date-number.

Display dates can be unreliable based on how the user formats the date. Indeed, a classic example here is the U.S. preference for month, day year format over everywhere else’s day, month and year (Let’s just all go year-month-day – much easier for sorting).

Anyway, we need to convert the Google Sheets date-number to a JS date time number and we do this with the method function dateJStoSheetsDateVal_(date). This method is called from findRows method any time a date query is detected.

Retrieving range values with Google Sheets API getRangeVals()

Lines 83-104

We don’t directly, call the getRangeVals() method in our archiveBids() function. However, this is a useful access point to have and is well worth making it available should we wish to reuse our class in other tasks or projects.

Instead, we will call this method from the findRows() method to get our target range to search.

The getRangeVals() method takes a target range in A1 notation and returns a 3d array of values from the range. That is sets of 2d ranges by row and column. If a value is a date, then that value will be returned as a date-number.

To retrieve values from Sheets API we use the Values.batchGet method. This takes a spreadsheet ID and a payload of options.

Finding Rows based on a query array findRows()

Lines 107-195

Find rows does not directly access Sheets API, rather it calls the getRangeVals method of the SsReq class.

The method takes the following parameters:

• sheetName (string): The name of the string.
• queries (array of queries): The array of queries used to find the rows.
• rowStart (number): The starting row of the range of the target sheet.
• rowEnd (number): An optional row end to limit the search range and perhaps improve performance.

The method first iterates over the queries and retrieves as a batch of ranges all of the query columns.

Next, the method iterates over each query. First, we check if the query column is a date and try and validate a date from the query input before converting the JS date to a Google Sheets date-value.

We then validate the query type. If it is a string then we want to ensure the query type is either ‘equal’ or ‘not equal to’. Throughout the query, we use an enumerator to set the query type. This helps with auto-completion and reduces errors.

Now, we iterate over each row in the range, carrying out matching checks based on the query type that was assigned to the query. If the current query matches the current row values, then it is stored and then the next query is checked. If all queries are matched then the current row number is pushed to the rows variable.

Copying the Target Rows copyRows()

The copyRows() method copies the values of each item in a row in a target Google Sheet and an array of row numbers.

Typically, my best practice in Google Sheets management, means that I remove any extraneous columns in a Google Sheet. This means that when copying rows I will have no cells where empty columns might have resided.

This method copies all the cell values in each of the target rows and then combines them together into a 2d array that is returned from the method.

Again, we use the Values.batchGet method of the Sheet API class to retrieve these rows.

To save on batch calls, and improve efficiency, we combine any adjacent row number into a single range. So for example, if we wanted to collect rows, 1,2,3,6, 10, and 11, we would have only 3 requests in our batch request (“1:3”, “6” and “10:11”).

Appending the Archive Sheet with the old data appendRows()

You can learn more about the append method in this tutorial:

Append – Google Sheets API Advanced Service for Apps Script

Lines 246-269

While there are a number of issues with the Google Sheets API examples and application of the Values.append() method (Docs issues, Method issues), once you understand how it works, it has some awesome advantages over the SpreadSheetApp.appendRows() method:

1. You can customise where to start your last-row search from.
2. You can append multiple ranges.

For us, the appendRows method takes a sheet name and the row numbers we found in findRows().

The method, once it has updated the sheet also conveniently returns an object with some data about the range it applied.

From this object, we can see:

• updatedColumns: The number of columns that were updated. Just the count and not the end row.
• updatedRows: The number of rows updated.
• updatedRange: The actual range that was updated. This is really the most hand property. From here we can update the formatting of the target values or use it in some other API request.
• updatedCells: A weird one, but this is the total number of cells in the rows and columns appended.
• tableRange: This was the range from the start row all the way down to the last value.

Removing the archived rows removeRows()

Lines 272-330

Removing rows again requires our ‘Bid’ or target sheet and a list of rows.

Just like in our copyRows() method, we will combine any adjacent rows into a row range before making a batch request.

The Sheets API request uses the batchUpdate method with the deleteDimension request as its request payload.

Confusingly, the term dimension is used to explain what to delete. The dimension is either a ‘ROW’ or a ‘COLUMN’. For us, as the name of the method suggests, we will be setting this to ‘ROW’.

Further confusing things, the deleteDimension request requires the sheet identifier to be the sheet ID rather than the sheet name.

This request can also return an array of dimension ranges that it deleted.

The QueryType Enumerator

Line 355-352

I really do love enumerators as a way to efficiently autocomplete and avoid errors. While JavaScript and Google Apps Script do not have ‘real’ enumerators, we can simulate what we need from these by providing a frozen object of values.

Because there is more than one query type for our query object we can use the static get QueryType to provide options for this type for our developers.

Conclusion

This was quite a fun exercise for me to get my head back into using the Google Sheets API advanced service. I hope you can see that the real power of using the API over Spreadsheets is the control you have over making batch requests to do a whole bunch of things at once.

Having said that, don’t for one minute discount the SpreadsheetApp service. For 99% of cases, it is perfectly fine to use. Plus SpreadsheetApp does have the ability to carry out a number of batch methods, most notably getRangeList(). Further, SpreadsheetApp will try and do some ‘magic’ batching for you. And if you don’t want some processes batched together you can always use the flush() method.

Have fun playing with this. I would really love to see what you do with it and why you decided to go with the Sheets API approach over the Spreadsheet App approach. Feel free to add your thoughts in the comments below.

~Yagi