google-apps-script - 谷歌应用脚​​本优化

Question

好的，我需要一些帮助来优化(如果可能)和错误检查我的代码。

我的代码已经运行了 20 多个星期没有出错。现在突然间，脚本在第 190 行执行 .setvalues 时“挂起”。这是存档信息的部分。

收到的错误是“服务超时:电子表格”和“异常:服务错误:电子表格”。

脚本在周日凌晨 2 点到 3 点之间运行，此时服务器应该不那么拥挤。脚本在手动运行时也从未超时。我无法重现此错误，即使将工作数据增加三倍或四倍也是如此。

那么，我要开始了。

我的脚本分为 4 个部分。

第 1 部分:

验证信息 - 删除过滤器、取消隐藏行/列并删除空白行。

第 2 部分:

将选定的工作表复制到新的电子表格，并将其作为 Excel 格式的附件通过电子邮件发送给选定的用户。

第 3 部分:

清除原始表中的数据以防止重复的可能性。

第 4 节:

这是失败的部分，请尝试将复制的值粘贴到存档的电子表格中。

以前，没有循环来重新尝试此操作。如果失败，我会收到一封包含 excel 文档的电子邮件。

循环似乎没有帮助。除此之外，它在过去的这个周末将一半的信息粘贴到我的文件中。

如果这有帮助，正在移动的数据大约有 8000 行和 15 列，因此大约有 120,000 个单元格。 (没那么多)

如果有人可以提出任何修改或改进建议，请随意。

完整代码如下。

//******************** Menu Start ************************//

   function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Admin')
      .addItem('Update to Raw', 'moveData')
      .addSeparator()
      .addSubMenu(ui.createMenu('Authorise')
          .addItem('Authorise Scripts', 'Auth'))
      .addToUi();
}

//******************** Menu End ************************//

//******************** Authorisation Start ************************//

function Auth(){

  var email = Session.getActiveUser().getEmail();
var temp = new Date();
  if (temp == "Blank") {
    // These calls will never be visited
onOpen();
moveData();
clearData();
RemoveFilter();
DeleteBlankRows();
UnhideAllRowsAndColumns();
UnhideAllRowsAndColumnsRaw();
clearDataRaw();

  } else {
    Browser.msgBox("The Backup script has now been authorized for "+email+". Each user only has to do this once.");
  }
}

//******************** Authorisation End ************************//

//******************** Clear Source Sheet Start ************************//

function clearData() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

  source.deleteRows(2,source.getLastRow()-1);

}

//******************** Clear Source Sheet End ************************//

//******************** Copy Data Start ************************// 

function ArchiveData() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");
  var targetkey = Spreadsheet.getSheetByName("Archive").getRange("C1").getValue();
  var tSpreadsheet = SpreadsheetApp.openById(targetkey);
  var target = tSpreadsheet.getSheetByName("Raw");

try{

  // Information Quality Checks

RemoveFilter();
UnhideAllRowsAndColumns();
DeleteBlankRows();


  var storedata = source.getRange(2,1,source.getLastRow(),source.getLastColumn()).getValues();
  var sn = Spreadsheet.getName();
  var URL = Spreadsheet.getUrl();
  var message = URL;
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd-MM-yyyy HH:mm");

  var subject = sn + " - Script Complete :  " + date;

  var emailTo = ["Recipient1@gmail.co.uk","Recipient2@gmail.co.uk",
                "Recipient3@gmail.co.uk","Recipient4@gmail.co.uk","Recipient5@gmail.co.uk"];

  // Google Sheets Extract Sheet Hack //
  // Create a new Spreadsheet and copy the current sheet into it//

  var newSpreadsheet = SpreadsheetApp.create("Call Log Script Export");
  source.copyTo(newSpreadsheet);

  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();
 // newSpreadsheet.getSheetByName('!Copied Sheet Name!').setName("Source Export") //

  var ssID = newSpreadsheet.getId();
  var url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=xlsx&id=" + ssID;

  var requestData = {"method": "GET","headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};

  var result = UrlFetchApp.fetch(url , requestData); 
  var contents = result.getContent();

  MailApp.sendEmail(emailTo, subject, message, 
  {attachments:[{fileName:"Call Log Script Export.xls", content:contents, mimeType:"application//xls"}]});




//------------------------- Move Data -------------------------//

  var senddata = target.getRange(target.getLastRow()+1, 1, source.getLastRow(),source.getLastColumn() );

//------------------------- Clear Data Call -------------------------//
        // ------------- Clears Source Sheet ------------- //

clearData();

var retryLimit = 4;
var retryDelay = 1000;
var retry;
    for (retry = 0; retry <= retryLimit; retry++) {
      try {
        // do the spreadsheet operation that might fail
        senddata.setValues(storedata);

          // Delete the wasted sheet we created, so our Drive stays tidy
        DriveApp.getFileById(ssID).setTrashed(true);
        SpreadsheetApp.flush();
        break; 
      }
      catch (e) {
        Logger.log('Failed on try ' + retry + ', exception: ' + e);
        if (retry == retryLimit) {
          throw e; 
        }
        Utilities.sleep(retryDelay);
      }  
  }

//------------------------- Copy Data Mid -------------------------//

}

//------------------------- Catch and Send Error Start -------------------------//


catch(err){
var error = err.lineNumber + ' - ' + err;
var URL = Spreadsheet.getUrl();
var sn = Spreadsheet.getName();
var date = Utilities.formatDate(new Date(), "GMT+1", "dd-MM-yyyy HH:mm");

var emailadd = ["Recipient1@gmail.co.uk","Recipient2@gmail.co.uk",
                    "Recipient3@gmail.co.uk","Recipient4@gmail.co.uk","Recipient5@gmail.co.uk"];
var subject = sn + " : Archive Script Error";
var body = URL + " - - - Date - - - " + date + " - - - Error Code - - - " +     error

MailApp.sendEmail(emailadd,subject,body);

 }

//------------------------- Catch and Send Error End -------------------------//

}

//******************** Copy Data End ************************//

//******************** Unhide Start ************************// 

function UnhideAllRowsAndColumns() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

var fullSheetRange = source.getRange(1,1,source.getMaxRows(), source.getMaxColumns() )  
source.unhideColumn( fullSheetRange );
source.unhideRow( fullSheetRange ) ;  
  }  

//******************** Unhide End ************************// 

//******************** Delete Blank Start ************************// 

function DeleteBlankRows() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

  var rows = source.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[1] == '') {
      source.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
}

//******************** Delete Blank End ************************// 

//******************** Remove Filter Start ************************// 

function RemoveFilter(){

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

var row = 1 //the row with filter 
var rowBefore = row 

source.insertRowBefore(row); //inserts a line before the filter
row++;

var Line = source.getRange(row + ":" + row); //gets filter line in A1N
Line.moveTo(source.getRange(rowBefore + ":" + rowBefore)); //move to new     line in A1N
source.deleteRow(row); //deletes the filter line

}

//******************** Remove Filter End ************************// 

Answer 1

我仔细检查了代码，没有发现任何明显的问题。但我对这一行很好奇:

var senddata = target.getRange(target.getLastRow()+1, 1, source.getLastRow(),source.getLastColumn() );

因为我看不到您的数据，所以我无法确认 senddata 的范围高度和宽度是否与 storedata 的尺寸相同，如果它们不相同，则可能会导致问题。