sheet.appendRow()偶尔会静默失败，并导致一个空行 [英] sheet.appendRow() sporadically fails silently and results in an empty row

问题描述

我正在使用google-spreadsheet中的google-apps-script，我尝试使用fetchUrl获取几百行数据，然后使用sheet.appendRow将其粘贴到我的工作表中.

Using google-apps-script from google-spreadsheet, I'm trying to fetch some hundreds of rows of data using fetchUrl and paste them into my sheet using sheet.appendRow.

获取数据并将其拆分为数组是可以的.但是将它们逐行地附加到工作表上会失败，没有任何错误，并导致行为空.

Fetching data and splitting to arrays are OK. But appending them to a sheet row by row ocasionally fails without any errors and resulting in empty rows.

这是一个很简单的例子来重现问题.

Here's quite simple example to recreate the problem.

function appendTest() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[2]);
  for (var i = 0; i <= 100; i++) {
      sheet.appendRow([i, 1, 2, 3, 4, 5]);
  }
}

大约5或10行，有时会不一样，变成空的. 插入Utilities.sleep()似乎也不起作用.

About 5 or 10 rows, differs time to time, become empty. Inserting Utilities.sleep() seems to be not working, either.

有人有摆脱这种情况的想法吗? 预先谢谢你.

Does anybody have any idea to get rid of this situation? Thank you in advance.

推荐答案

您可以尝试像这样使用批处理写入工作表

You could try to use batch writing to the sheet like this

function appendTest() {
  var target = new Array()
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[2]);
  for (var i = 0; i <= 100; i++) {
      target.push(['some data','in some columns','bla bla']);
  }
sheet.getRange(sSheet.getLastRow()+1,1,target.length,target[0].length).setValues(target);
}

请注意，如果您认为工作表太短"，则也可以在循环中添加空行.

Note that if you think the sheet will be too 'short' you could append empty rows in the loop as well.

:按照迈克尔的相关答案，使用sheet.insertRowsAfter(sheet.getMaxRows(), target.length)

EDIT : following Michael pertinent answer, it would be a much better idea to add the necessary rows using sheet.insertRowsAfter(sheet.getMaxRows(), target.length)

这篇关于sheet.appendRow()偶尔会静默失败，并导致一个空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！