英文:
Google Apps Script Spreadsheets.Values.update function writing only blank cells
问题
我有一个函数，它复制并修改大量数据，然后用结果覆盖一个工作表。当我使用 range.setValues(data) 时，我的代码能够正确工作，但速度非常慢，有时因此出错。我已经将我的代码切换到使用 Values.update() 函数，但遇到了一个奇怪的问题。
当我的代码是：
const result = Sheets.Spreadsheets.Values.update(valueRange, ss.getId(), "工作表名称", {valueInputOption: 'RAW'});
值被复制，但存在数据类型的问题（货币被解释为日期等）。我知道解决方法是更改 valueInputOption。但是，当我使用以下代码：
const result = Sheets.Spreadsheets.Values.update(valueRange, ss.getId(), "工作表名称", {valueInputOption: 'USER_ENTERED'});
该函数执行完毕，没有错误，但工作表中全部是空单元格。有任何想法为什么会这样吗？
谢谢！
英文:
I have a function that copies and alters a large amount of data, then overwrites a sheet with the results. My code works correctly when I use range.setValues(data), but it is exceedingly slow and sometimes errors out for that reason. I have switched my code to instead use the Values.update() function, but I am encountering an odd problem.
When my code is:
const result = Sheets.Spreadsheets.Values.update(valueRange, ss.getId(), "Sheet Name", {valueInputOption: 'RAW'});
the values are copied, but there are issues with the data types (currencies being interpreted as dates, etc.). I know the solution is to change the valueInputOption. However, when I use the following code:
const result = Sheets.Spreadsheets.Values.update(valueRange, ss.getId(), "Sheet Name", {valueInputOption: 'USER_ENTERED'});
the function finishes executing without an error but sheet is filled with entirely blank cells. Any idea why this may be?
Thanks!
答案1
得分: 0
重新发布这里以便我可以标记为已解决：我实际上已经找出了问题所在！在执行上述代码之前，我有一行代码来清除范围，使用 range.clearContent();。在清除范围之后，我会使用数据更新表格。然而，Google Apps Script 等待清除范围直到我写入内容后，所以表格最终变成了空白。我已通过在 range.clearContent() 之后直接添加 SpreadsheetApp.flush(); 调用来解决了这个问题。现在它按预期工作。
英文:
Reposting here so I can mark resolved: I've actually just figured out the issue! Before the above code was executing, I had a line to clear the range, using range.clearContent();. After clearing the range, I update the sheet with the data. However, Google Apps Script was waiting to clear the range until after I had written the content, so the sheet was ending up blank. I have fixed this issue by adding a SpreadsheetApp.flush(); call directly after range.clearContent(). It is now working as intended.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论