A short story (really!) about errors in Google Sheets formulas, the Sheets API, and being a GDE

So short that hopefully It won't even need a TL;DR! But despite its brevity, I couldn't miss the chance to share some thoughts that have recently stirred me.

I joined the Google Developer Experts program in February 2020. I vividly recall the wave of imposter syndrome that hit me as I entered the program's chat space for the firt time and scrolled through the member list. There they all were, my professional role models, people I had followed and whose work I deeply respected.

All in all, I can only say that it has been truly an honor to be part of it for the past four years. 

Being a Google Developer Expert (GDE) really supercharges your work in spreading the word about Google technologies in the community through the company's channels. It is also a fantastic opportunity to get up close and engage with the designers and developers of the tools you love, giving you a direct line to share detailed feedback that might shape what's coming next for them.

But my primary focus has never been on fame or exposure.

My driving force to stay in the program is to share my insights and projects, my knowledge, big or small, to support others, particularly in the Spanish-speaking education sector, and to make connections with people more knowledgeable than me to learn from. The GDE community is bursting with incredibly talented individuals 🙏🏼! 

And here is where this little Apps Script story starts to unfold ✨...

A few weeks back, a GDE colleague named Ben (I am 100% sure you're well familiar with him, especially if you are a spreadsheet enthusiast 😉) raised in our Google Chat space the topic of capturing, using Apps Script, the error message displayed by Google Sheets in a pop-up panel when you hover over a formula that throws an error.

At the time, I was preparing a post about custom functions that involved displaying specific error messages, and was pretty sure it was not feasible by just using the default Spreadsheet service, so I boldly stepped in, without further checking the Google Sheets API more thoroughly, and replied that I did not think that was possible.

Luckily, my response was not too definitive; I prefaced it with "as far as I know..." 😅.

Thankfully, a few days later, another fellow GDE entered the discussion and shared an already old technique that uses the Sheets API to successfully extract those error messages, confirming a persistent hunch that had been lingering in my mind.

It turns out that the Sheets API features the quite straightforward spreadsheets.values.get endpoint, which is utilized to retrieve cell values. This methods yields a ValueRange object.

However, the actual data from the cells is solely contained within its values property.

But diving into the Sheets API is like going down a rabbit hole — it gets way deeper than you'd think.

It turns out that there is another path, through the spreadsheets.get method, that leads to our coveted goal.

Let's delve deeper into the deeply-nested Spreadsheet resource that is returned by this method:

Spreadsheet → Sheets → GridData → RowData → CellData → ExtendedValue → ErrorValue

The message property holds the information we are looking for, also when the formula inside the cell involves a custom function.

The Apps Script setup is rather effortless:

// Retrieves the error information of a formula error
function getFxErrorMessage() {

    const id = SpreadsheetApp.getActiveSpreadsheet().getId();
    const response = Sheets.Spreadsheets.get(
        id,
        { 
            ranges: ['Sheet1!A1', 'Sheet1!A2'],
            includeGridData: true
        } 
    );
    
    console.info(response.sheets[0].data[0].rowData[0].values[0].effectiveValue);
    console.info(response.sheets[0].data[1].rowData[0].values[0].effectiveValue);

}

When dealing with requests that cover multiple ranges, setting includeGridData: true in the request could be excessive. This is because you end up retrieving not just the ErrorValue object but the entire Spreadsheet resource, complete with its comprehensive hierarchy of child properties. In such cases, it's more efficient to use a field mask to narrow down and fetch only the specific information you need.

const response = Sheets.Spreadsheets.get(
	id,
    { 
    	ranges: ['Sheet1!A1', 'Sheet1!A2'],
        fields: 'sheets(data(rowData(values(effectiveValue(errorValue)))))'
    } 
);

This is the original code sample that demonstrates this technique, as gently provided by the one and only Apps Script sensei, Kanshi Tanaikesan 🙌🏼. As crafting the fields parameter can become a bit tricky, he has also arranged this handy builder web app.

🔔 Don't forget to enable the Google Sheets API as an advanced service prior to trying these code snippets!

It seems important to mention that this API method requires at least this authorization scope:

https://www.googleapis.com/auth/spreadsheets.readonly

If you are planning on using it in an Apps Script project that has only been granted a more limited scope, such as this one, it will fail:

https://www.googleapis.com/auth/spreadsheets.currentonly

Now you might have a better idea of why I love being a GDE so much, right? The opportunity to share space with brilliant and generous individuals like Ben Collins and Kanshi Tanaike is, without a doubt, the highlight of the GDE program for me.