Google App脚本优化 [英] Google App Script Optimisation

问题描述

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

我的代码在20多周内无错误运行。现在突然间，脚本在执行190行上的.setvalues时挂起。这是归档信息的部分。

收到的错误是 服务超时：电子表格和例外：服务错误：电子表格。

脚本运行时间为凌晨2点至3点在星期天，当服务器应该不那么拥挤。手动运行时，脚本也从未超时。即使是工作数据的三倍或四倍，我也无法复制这个错误。

所以，我会开始。

我的脚本分为4个部分。

第1部分：

验证信息 - 删除过滤器，取消隐藏行/列并删除空白行。 第2部分：

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

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

第4部分： 这是失败的部分，TRY并粘贴将复制的值复制到存档电子表格中。

以前，没有循环可以重新尝试。如果失败，我会收到一封带有excel文档的电子邮件。

循环似乎没有帮助。除此之外，它在本周末将一半的信息粘贴到我的档案中。

如果有帮助，正在移动的数据大约有8000行和15列，所以大约有120,000个单元格。 （没有那么多）

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

完整代码

  // ********************菜单开始************************ // 
 
函数onOpen（）{
 var ui = SpreadsheetApp.getUi（ ）; 
 ui.createMenu（'Admin'）
 .addItem（'Update to Raw'，'moveData'）
 .addSeparator（）
 .addSubMenu（ui.createMenu（'Authorize '）
 .addItem（'Authorize Scripts'，'Auth'））
 .addToUi（）; 
} 
 
 // ********************菜单结束************* *********** // 
 
 // ********************授权开始***** ******************* // 
 
函数Auth（）{
 
 var email = Session.getActiveUser（） .getEmail（）; 
 var temp = new Date（）; 
 if（temp ==Blank）{
 //这些调用永远不会被访问
 onOpen（）; 
 moveData（）; 
 clearData（）; 
 RemoveFilter（）; 
 DeleteBlankRows（）; 
 UnhideAllRowsAndColumns（）; 
 UnhideAllRowsAndColumnsRaw（）; 
 clearDataRaw（）; 
 $ b} else {
 Browser.msgBox（备份脚本现在被授权使用+ email +，每个用户只需要做一次。）; 
} 
} 
 
 // ********************授权结束******** **************** // 
 
 // ********************清除来源纸张开始************************ // 
 
函数clearData（）{
 
 var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var source = Spreadsheet.getSheetByName（Data）; 
 
 source.deleteRows（2，source.getLastRow（） -  1）; 
 
} 
 
 // ********************清除源代码片尾******* ***************** // 
 
 // ********************复制数据开始************************ // 
 
函数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）; 
 
尝试{
 
 //信息质量检查
 
 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 = [[e-mail]，[e-mail]，
[e-mail]，Recipient4 @ gmail .co.uk， [e-mail]]; 
 
 // Google Sheets Extract Sheet Hack // 
 //创建一个新的Spreadsheet并将当前表单复制到// 
 
 var newSpreadsheet = SpreadsheetApp.create （呼叫日志脚本导出）; 
 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 }]}）; 
 
 
 
 
 // -------------------------移动数据 - ------------------------ // 
 
 var senddata = target.getRange（target.getLastRow（）+ 1，1 ，source.getLastRow（），source.getLastColumn（））; 
 
 // -------------------------清除数据呼叫------------ ------------- // 
 // -------------清除源表格------------- // 
 
 clearData（）; 
 
 var retryLimit = 4; 
 var retryDelay = 1000; 
 var retry; 
 for（retry = 0; retry< = retryLimit; retry ++）{
 try {
 //执行可能失败的电子表格操作
 senddata.setValues（storedata）; 
 
 //删除我们创建的浪费表，因此我们的云端硬盘保持整洁
 DriveApp.getFileById（ssID）.setTrashed（true）; 
 SpreadsheetApp.flush（）; 
休息; 
} 
 catch（e）{
 Logger.log（'尝试失败+重试+'，例外：'+ e）; 
 if（retry == retryLimit）{
 throw e; 
} 
 Utilities.sleep（retryDelay）; 
} 
} 
 
 // ------------------------- Copy Data Mid  - ----------------------- // 
 
} 
 
 // ------ -------------------捕获和发送错误启动------------------------- / / 
 
 
 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 = [[e-mail]，[e-mail]，
[e-mail]，Recipient4 @ gmail .co.uk， [e-mail]]; 
 var subject = sn +：存档脚本错误; 
 var body = URL + -   -   -  Date  -   -   - + date + -   -   -  Error Code  -   -   - + error 
 
 MailApp.sendEmail（emailadd，subject，body ）; 
 
} 
 
 // -------------------------捕获并发送错误结束 - ------------------------ // 
 
} 
 
 // ***** ***************复制数据结束************************ // 
 
 // ********************取消隐藏开始************************ // 
 
函数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）; 
} 
 
 // ********************取消隐藏结束************* *********** // 
 
 // ********************删除空白开始**** ******************** // 
 
函数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 ++; 
} 
} 
} 
 
 // ********************删除空白结束** ********************** // 
 
 // **************** ****删除过滤开始************************ // 
 
函数RemoveFilter（）{
 
 var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var source = Spreadsheet.getSheetByName（Data）; 
 
 var row = 1 //带过滤器的行
 var rowBefore =行
 
 source.insertRowBefore（row）; //在过滤器之前插入一行
 row ++; 
 
 var Line = source.getRange（row +：+ row）; //获取A1N 
中的过滤行line.moveTo（source.getRange（rowBefore +：+ rowBefore））; //在A1N 
中移动到新行source.deleteRow（row）; //删除过滤行
 
} 
 
 // ********************删除过滤结束** ********************** // 
  

错误发生在我今天的脚本上，经过几次尝试后，我能做的就是：

• 制作完整版本（包含脚本）并开始使用该表。

干杯，

Ok, I'm in need of some help optimising (where possible) and error checking my code.

My code has ran error free for 20+ weeks. Now all of a sudden, the script 'hangs' while executing the .setvalues on line 190. This is the section that archives the information.

Error received is "Service Timed Out : Spreadsheets" and "Exception: Service Error: Spreadsheets".

The Scripts runs between 2-3am on Sundays, when the servers should be less congested. The Script has also never timed out when running manually. I have not been able to replicate this error, even when tripling or quadrupling the working data.

So, I'll start.

My script runs in 4 sections.

Section 1 :

Validate information - Remove filters, Unhide Rows/columns and delete blank rows.

Section 2 :

Copy the selected sheet to a new spreadsheet and email this to selected users as an attachment in Excel format.

Section 3 :

Clear the data from the original sheet to prevent the possibility of duplication.

Section 4 :

This is the part that fails, TRY and paste the copied values into the archived spreadsheet.

Previously, there was no loop to re-attempt this. If it failed I would receive an email with the excel document.

The loop doesn't seem to be helping. Other than, it pasted half the information into my archive this weekend past.

If this helps, the data that is being moved is about 8000 rows and 15 columns, so about 120,000 cells. (Not that much)

If anyone can suggest any amendments or improvements, please feel free.

Full code below.

//******************** 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 = ["[e-mail]","[e-mail]",
                "[e-mail]","[e-mail]","[e-mail]"];

  // 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 = ["[e-mail]","[e-mail]",
                    "[e-mail]","[e-mail]","[e-mail]"];
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 ************************// 

解决方案

"Service Timed Out : Spreadsheets"

error started happening on my script today and after a few trials what I could do to get away with that was to:

• Make a full copy of the sheet (that contains script) and start using that sheet.

Cheers,

这篇关于Google App脚本优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！