Search code examples
javascriptnode.jsherokugoogle-sheets-api

Highly variable latency when making Google Sheets V4 API call

So I have this code:

var latency = Date.now();
  const sheetFile = await google.sheets({version: 'v4', auth});
  var result = await sheetFile.spreadsheets.values.get({spreadsheetId: sheetID,range: "TabName!A1:ZZ",});
console.log("tab read latency:",Date.now() - latency)

which I'm calling in a node.js app running in a Heroku dyno. I'm seeing latencies that vary from <500ms to >50000ms, reading the same data. Heroku times out and issues a 503 "Application error" to the browser if I don't finish the transaction in 30 seconds, and we're seeing that error a lot.

This app has been running for several years and the latency issue has only appeared in the past two weeks. This happens to coincide with an increase in our production rate, but not so much (~X3) that I can imagine we're putting a load on their servers!

The spreadsheet in question is about 500 rows X 20 columns and we're requesting it maybe 4 or 5 times per minute.

My options are to somehow reduce the latency or to refactor (code and management :-) to eliminate Sheets from the picture altogether. Is there a way to pay Google for improved latency? The Sales people at Google referred me to the Developer people, who referred me to the Sales people, so I'm trying here.

Here's a sample of the latencies I've seen: Screenshot of logs showing latency times

Solution

  • The spreadsheet in question turned out to have a lot of formulas, imports from other spreadsheets (which in turn had formulas and imports, etc.) and so the sheet itself is just slow to respond. This slowness increased with our production increase, as more data was being pushed in. (I did not make this spreadsheet :-)

    The latency issue appears to be related to queueing/locking of the spreadsheet. There are two ways to fix the latency problem:

    1. Reduce the amount of work being done by the spreadsheet
    2. Stop reading from the spreadsheet

    I am adopting the second solution, as management really likes the spreadsheet as they've designed it (as is the wont of management) and with a bit of extra work I can get the info I need from elsewhere.