Deep Dive: Google Apps Script - Testing APIs and Automating Sheets

In this article, we will explore how to test your Google Apps Script which uses external APIs to get data and then populate Google Sheets with it.

We will use a trivial processing function which transforms the data we receive from the API. The point of this article is to focus on writing automated testing of the solution and not on the processing of the data, therefore we keep the processing as simple as possible.

Let’s start by understanding the requirements:

Story:

As an HR manager
I want to easily see all public holidays in the UK in my spreadsheet
So that I use it for data analysis

Scenario 1 - get data:

Given I have access to the Public Holidays API
When I change the year and country in the dropdown
Then I can see all related public holidays in the spreadsheet
And I will see the Date, Name and Counties for each

Scenario 2 - data processing:

When the public holidays are loaded in the spreadsheet
Then the dates which are invalid are not displayed

We will use this Public Holidays API endpoint:
https://date.nager.at/Api

Setup

We have already created a spreadsheet with the two input fields, year and country.

We will use the built-in lookup function to find the country code based on the list of countries in another sheet.

=VLOOKUP(B2,Countries!A:B,2,FALSE)

We have also added a Submit button and assigned a script to call our submit function.

Finally, in order to make it easier to work with the input and output ranges, we have created three named ranges, two for the inputs called Year and CountryCode and one for the output called Holidays. The Holidays range starts on the second row and does not include the heading.

This will make it easier to clear and replace the content.

Now let’s have a look at the code.

Initial code and basic workflow

First we need to get the input values from the sheets, the year and the country code.

function submit() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const year = sheet.getRangeByName('Year').getValue()
  const countryCode = sheet.getRangeByName('CountryCode').getValue()
  // …
}

Let’s create a new function to do the processing, in order to separate the concerns of getting inputs and creating the output. We will call it getAndProcessData and pass the two input variables.

function submit() {
  // …
  getAndProcessData(year, countryCode)
}

const getAndProcessData = (year, countryCode) => {
  // …
}

Note: For simplicity, I will omit the JavaScript documentation, but for a production grade code you may want to add the documentation (see jsdoc.app website for more).

Let’s elaborate on the workflow. We need to call an external API to fetch the data, if we get the data successfully then we also need to clear the range to ensure we don’t keep any previous data in it, and finally we need to process the data and populate the range.

const getAndProcessData = (year, countryCode) => {
  const data = getData(year, countryCode)
  if(data) {
    // clear target range
    // process data
  }
}

Fetching data from API

Getting the data is trivial, using the Url Fetch App and fetch function. In order to process the data as a JSON object we need to parse the text response. Handling issues with fetching and parsing the data is outside of the scope of this video, but feel free to comment and ask for another deep dive on how to handle and test that.

const getData = (year, countryCode) => {
  // see https://date.nager.at/Api
  const url = `https://date.nager.at/api/v3/publicholidays/${year}/${countryCode}`
  const response = UrlFetchApp.fetch(url)

  const json = response.getContentText()
  const data = JSON.parse(json)
  return data
}

Now we have the data as a JSON object.

Testing the code with AAA

Disclaimer:
Not strictly following the TDD (Test Driven Development) in this simple example, we are writing some skeleton code first and test it next and then complete the implementation to make the tests pass. This can illustrate how this process would work when adding tests to an existing code.

It is a good idea to explore the API and get some sample data for testing.

> curl https://date.nager.at/api/v3/publicholidays/2024/GB | jq

[
  {
    "date": "2024-01-01",
    "localName": "New Year's Day",
    "name": "New Year's Day",
    "countryCode": "GB",
    "fixed": false,
    "global": false,
    "counties": [
      "GB-NIR"
    ],
    "launchYear": null,
    "types": [
      "Public"
    ]
  },
  {
    "date": "2024-01-01",
    "localName": "New Year's Day",
    "name": "New Year's Day",
    "countryCode": "GB",
    "fixed": false,
    "global": false,
    "counties": [
      "GB-ENG",
      "GB-WLS"
    ],
    "launchYear": null,
    "types": [
      "Public"
    ]
  },
…

Let’s create our first test. It will be failing at this point, but that’s OK, because we have not completed the implementation yet.

Let’s follow the triple A pattern for unit testing - Arrange, Act, Assert. This pattern is popular amongst .NET developers (see e.g. here).

const test_getAndProcessData = () => {
  Logger.log('test_getAndProcessData')

  // Arrange
  // Act
  // Assert
}

First we Arrange or setup the test - we need a test year, test country code and mocked sample data.

const test_getAndProcessData = () => {
  // …

  // Arrange
  const year = 2024
  const countryCode = 'UK'
  const sampleData = [
    {
      "date": new Date("2024-05-27"),
      "localName": "Spring Bank Holiday",
      "name": "Spring Bank Holiday",
      "countryCode": "GB",
      "fixed": false,
      "global": true,
      "counties": null,
      "launchYear": null,
      "types": [
        "Public"
      ]
    },
    {
      "date": new Date("2024-08-05"),
      "localName": "Summer Bank Holiday",
      "name": "Summer Bank Holiday",
      "countryCode": "GB",
      "fixed": false,
      "global": false,
      "counties": [
        "GB-SCT"
      ],
      "launchYear": null,
      "types": [
        "Public"
      ]
    }
  ]
  // …
}

Reusable Functions

We also need the target range, which we call Holidays. That is a separate concern so we create a separate function just to get that range. Let’s call it getDataRange. Let’s use the built-in SpreadsheetApp.getActiveSpreadsheet() function. Then we use getRangeByName(…), and pass the argument, the name is "Holidays".

const test_getAndProcessData = () => {
  // …

  // Arrange
  // …
  const range = getDataRange()
}

const getDataRange = () => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  return sheet.getRangeByName('Holidays')
}

The test function will test the getAndProcessData function, so that goes under the Act section.

const test_getAndProcessData = () => {
  // …

  // Act
  getAndProcessData(year, countryCode)

  // …
}

Mocking

This is where the testing gets exciting!

We don’t want to be calling a real API in our unit tests! But if I would execute this test, it would call the real API. How to handle it?

We are going to refactor the getData function and instead of calling the UrlFetchApp.fetch function, we pass in a fetch function as a parameter into the getData function. During normal runs, this would be the UrlFetchApp.fetch, and during test runs it would be our mocked fetch function, which will not call the actual external API and which we will have fully under control in the test code.

Let’s go back to the test. Let’s create a mocked fetch function, which returns a response object, which then has a getContentText property, which is a function, which then returns a JSON string, just like the UrlFetchApp.fetch.

const test_getAndProcessData = () => {
  // …

  // Arrange
  // …
  const mockedFetch = (url) => {
    return {
      getContentText: () => JSON.stringify(sampleData)
    }
  }

  // …
}

Let’s change the call to pass this mocked fetch function as a parameter.

function submit() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const year = sheet.getRangeByName('Year').getValue()
  const countryCode = sheet.getRangeByName('CountryCode').getValue()
  getAndProcessData(year, countryCode, UrlFetchApp.fetch)
}

const getAndProcessData = (year, countryCode, fetchFn) => {
  const data = getData(year, countryCode, fetchFn)
  if(data) {
    clearRange()
    processData(data)
  }
}

const getData = (year, countryCode, fetchFn) => {
  // see https://date.nager.at/Api
  const url = `https://date.nager.at/api/v3/publicholidays/${year}/${countryCode}`
  const response = fetchFn(url)

  const json = response.getContentText()
  const data = JSON.parse(json)
  return data
}

… and in the test:

const test_getAndProcessData = () => {
  // …

  // Act
  getAndProcessData(year, countryCode, mockedFetch)

  // …
}

Assertion

Now, we can assert the output, to ensure that the actual behavior meets the expected behavior.

To make the assertions easier, add variables for the two expected rows we should see in the target range. These match the mocked input JSON with its two objects.

const test_getAndProcessData = () => {
  // …

  // Arrange
  const expectedRow1 = [new Date("2024-05-27"), "Spring Bank Holiday", 'null']
  const expectedRow2 = [new Date("2024-08-05"), "Summer Bank Holiday", '["GB-SCT"]']

  // …
}

We get the actual values from the range and create the expected data range which has the same size and set its rows to the expected values - we have just the two rows.

const test_getAndProcessData = () => {
  // …

  // Assert
  const actual = range.getValues()
  const expected = [] // TODO: create array with the same shape as the actual array
  expected[0] = expectedRow1
  expected[1] = expectedRow2
  // TODO: assert that actual equals expected

  // …
}

Multidimensional arrays

Creating a multidimensional array of a certain size is again a different concern, so let’s create a new function for that. It will take just one parameter since we only need to create some number of rows but for this example we will hard-code the number of columns to three. Use the spread operator to generate an array of a desired size, and a map function to generate a new array where each item is another array with three empty strings.

const createArrayWithThreeColumns = (rows) => {
  return [...Array(rows).keys()].map(() => ['', '', ''])
}

This isn’t a trivial code, so let’s add a simple test for that. Just a single test will do for now, let’s say with two rows. Run the test… and it’s passing. Great!

const test_createArrayWithThreeColumns = () => {
  // Arrange
  const rows = 2
  const expected = [
    ['', '', ''],
    ['', '', ''],
  ]
  // Act
  const actual = createArrayWithThreeColumns(rows)
  // Assert
  assertEquals(actual, expected)
}

Main test

Now back to the main test.

We can compare the actual and expected values. Since these are multidimensional arrays, the easiest way to compare them is to convert them to strings and compare the strings. Comparing the values is a good candidate for another reusable function to keep clean separation of concerns, so let’s go ahead and create a new function for that. It will log a passed message if the test fails and log an error if it fails. It’s useful to also log the expected and actual values. We are not going to write a test for that though.

const test_getAndProcessData = () => {
  // …

  // Assert
  const actual = range.getValues()
  const expected = createArrayWithThreeColumns(range.getNumRows())
  expected[0] = expectedRow1
  expected[1] = expectedRow2
  // assert actual equals expected
  assertEquals(actual, expected)
}

const assertEquals = (actual, expected) => {
  if(JSON.stringify(actual) === JSON.stringify(expected)) {
    Logger.log('- passed')
  } else {
    Logger.log(new Error(`- failed; expected: ${expected} actual: ${actual}`))
  }
}

Back to the main test. Run it and… it should fail. That is expected. Let’s move on.

Clearing the table

Let’s go back to the top. We need a way to clear any existing data in the target range which we named Holidays. Get the active spreadsheet, get the range by name and call the clear function.

const getAndProcessData = (year, countryCode) => {
  const data = getData(year, countryCode)
  if(data) {
    clearRange()
    // process data
  }
}

…

const clearRange = () => {
  getDataRange().clear()
}

This is trivial, should we test it too?

Yes. It is a part of the overall solution and there are already quite a few moving parts.

Let’s create a new test function. Under the Arrange section, get the range and set the value to something like a 'TEST'. This is referencing the actual spreadsheet, so be aware that when this test is run, it will overwrite the data in the spreadsheet. But I am OK with that for now. Under the Act section, call the clearRange() function. Finally under the Assert section, check that the range is empty again.

The actual data is the range values and the expected data is an empty array; we will reuse the functions created above to create the array and to compare the actual and expected values.

const test_clearRange = () => {
  Logger.log('test_clearRange')

  // Arrange
  const range = getDataRange()
  range.setValue('TEST')

  // Act
  clearRange()

  // Assert
  const actual = range.getValues()
  const expected = createArrayWithThreeColumns(range.getNumRows())
  assertEquals(actual, expected)
}

Does it work? Let’s put a breakpoint to see it in action. You can select this test function in the menu and click on the Debug button. Then wait for a while and the debugger will pause on the breakpoint. Switch to the spreadsheet and you will see that the target range is populated with the TEST text.

Continue running the code. The test has passed! Switch back to the spreadsheet and you will see that the target range is now empty. Great!

Processing

The final step is processing of the data and populating the target range.

Get the target range using the getDataRange() function.

In order to make it easier to populate the whole range at once, instead of row by row or cell by cell, create a multidimensional array that has the same size as the target range and populate it with the values we get from the API call. Reuse the function created earlier.

const processData = (data) => {
  const target = getDataRange()
  const rows = createArrayWithThreeColumns(target.getNumRows())
  // …
}

Iterate over the data and for each value and index pair, add the desired elements to the row on the relevant index.

Here is where our data processing logic would be added - for simplicity again I have only a quick check that the date value is a valid date and if not it will be ignored. A production code would at least log a message as well for better observability but I’m OK with keeping this simple for now.

const processData = (data) => {
  // …

  data.forEach((value, index) => {
    // business logic - e.g. ignore invalid dates
    if(new Date(value.date) == 'Invalid Date') return

    // populate the output array
    rows[index] = [value.date, value.name, JSON.stringify(value.counties)]
  })
}
  // …

The last instruction is to set the values on the target range. Since we have a multidimensional array with a matching dimension as the target range, we can simply call a set values function. If the dimensions didn’t match we would get an error.

The complete code for the processing looks like this:

const processData = (data) => {
  const target = getDataRange()
  const rows = createArrayWithThreeColumns(target.getNumRows())

  data.forEach((value, index) => {
    // business logic - e.g. ignore invalid dates
    if(new Date(value.date) == 'Invalid Date') return

    rows[index] = [value.date, value.name, JSON.stringify(value.counties)]
  })

  target.setValues(rows)
}

Note: I can see potential issues here - for example the target range could have less rows that the data we get from the API call. It would be good to write another test for that and refactor the code to make it more robust, but that is outside of the scope of this article. Feel free to comment and ask for a more detailed deep dive.

Final test

Great!

Let’s go back to the main test and run it. Is it going to pass now?

Yes! Well done!

For convenience, it's useful to create a function which runs all the tests to ensure that the whole solution is still working. This is something that a good IDE could do for us, unfortunately Google Apps Script doesn't provide this out-of-the-box.

const test_runAll = () => {
  test_getAndProcessData() 
  test_createArrayWithThreeColumns()
  test_clearRange()
}

We can go back to the spreadsheet and try this again with different inputs.

Conclusion

In this article we covered how to test Google Apps Script which gets data from an external API and stores it into Google Sheets. We have quality testable code, applied single responsibility principle, and written unit tests for some functions and integration tests for others, using mocks instead of real services.

Thanks for reading!

You can also view this as a video on my YouTube Channel

Blog