英文:
Highly variable latency when making Google Sheets V4 API call
问题
我有这段代码：
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)
我正在调用这段代码在一个运行在Heroku dyno中的node.js应用程序中。我看到的延迟时间从小于500毫秒到大于50000毫秒不等，即使读取相同的数据。如果我在30秒内没有完成交易，Heroku会超时并向浏览器发出503错误“应用程序错误”，我们经常看到这个错误。
这个应用程序已经运行了几年，延迟问题只在过去的两周内出现。这似乎与我们生产速率的增加有关，但增加幅度不大（约为3倍），我无法想象我们会对他们的服务器施加负载！
涉及的电子表格大约是500行x20列，我们可能每分钟请求4到5次。
我的选择是以某种方式减少延迟，或者重构（包括代码和管理）以完全消除Sheets的影响。是否有办法向Google支付以改善延迟？Google的销售人员把我推荐给开发人员，开发人员又把我推荐给销售人员，所以我在这里尝试一下。
以下是我看到的延迟示例：
英文:
So I have this code:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->
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)
<!-- end snippet -->
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:
答案1
得分: 0
被提到的电子表格事实上有很多公式，从其他电子表格导入（这些电子表格又有公式和导入等），因此表格本身反应迟钝。随着我们的生产增加，这种迟钝性增加了，因为有更多的数据被推送进来。 （我没有制作这个电子表格
延迟问题似乎与电子表格的排队/锁定有关。解决延迟问题有两种方法：
- 减少电子表格的工作量
- 停止从电子表格中读取数据
我采取了第二种解决方案，因为管理层非常喜欢他们设计的电子表格（这是管理层的习惯），而且通过一些额外的工作，我可以从其他地方获取所需的信息。
英文:
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:
- Reduce the amount of work being done by the spreadsheet
- 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论