提高我的脚本性能Google表格脚本 [英] Increase my script performance Google Sheets Script

问题描述

我创建了一个函数，每当我在AY列上运行没有AppendRow脚本的每行中都没有点(.")时，就会自动包含我想要的每个信息/列的数组该工作表将被转移到我的主工作表中，该工作表具有 13000行 atm.

通常每天大约有20-40行被粘贴到第一张工作表中，并且此脚本会自动将列重新排列到我的主工作表中.

问题:问题是，附加到我的主表上的每一行大约需要 8-15秒才能被传输，有时我会遇到这种超出最大执行时间"的错误，这真是令人讨厌.>

有什么方法可以使我的代码运行更快，或者仅仅是一种使脚本运行5分钟以上的方法?

  function AppendRow(){var app = SpreadsheetApp;var Sheet = app.getActiveSpreadsheet().getSheetByName("Sheet1");var ss2017 = SpreadsheetApp.openById(");var sheet2017 = ss2017.getSheetByName("2017");var lastSourceRow = Sheet.getLastRow();var lastSourceCol = Sheet.getLastColumn();var sourceRange = Sheet.getRange(1，1，lastSourceRow，lastSourceCol);var sourceData = sourceRange.getValues();var lenght = sourceData.length;//Logger.log(lenght);var time = new Date();时间= Utilities.formatDate(时间，"GMT + 01:00"，"dd/MM/yy，HH:mm:ss");for(i = 5326; i< = lenght; i ++)//我i = 5326只是因为我已经有//在此脚本存在之前已转移的我工作表上的信息{var columnAY = Sheet.getRange(i，51).getValue();var checkReservation = Sheet.getRange(i，2).getValue();if(columnAY ==."){}别的{var B = Sheet.getRange(i，2).getValue();var F = Sheet.getRange(i，6).getValue();var E = Sheet.getRange(i，5).getValue();var K = Sheet.getRange(i，11).getValue();var L = Sheet.getRange(i，12).getValue();var V = Sheet.getRange(i，22).getValue();var O = Sheet.getRange(i，15).getValue();var P = Sheet.getRange(i，16).getValue();var Q = Sheet.getRange(i，17).getValue();var AF = Sheet.getRange(i，32).getValue();var AG = Sheet.getRange(i，33).getValue();var N = Sheet.getRange(i，14).getValue();var AI = Sheet.getRange(i，35).getValue();var AB = Sheet.getRange(i，28).getValue();var AC = Sheet.getRange(i，29).getValue();var array = ["，B，F，E，K，L，"，V，O，P，Q，AF，AG，"，"，N，N，AI，"，"，AB，AC，"，"，"，，时间];sheet2017.appendRow(array);Sheet.getRange(i，51).setValue(.");}}} 

p.s .:我的电子表格中也有一个菜单，每次我不想确定是否与问题有关时都运行该脚本:P

  function onOpen(){var ui = SpreadsheetApp.getUi();ui.createMenu('我的菜单').addItem('复制到2017'，'AppendRow').addToUi(); 

每个评论都值得赞赏！！:)

解决方案

我必须承认我在与您的协调方面存在一些问题，但我认为我的专栏是正确的.因此，请检查一下，但这应该会运行得更快.

 函数AppendRow(){var ss0 = SpreadsheetApp.getActive();var sh0 = ss.getSheetByName('Sheet1');var rg0 = sh0.getDataRange();var vA0 = rg0.getValues();var ss1 = SpreadsheetApp.openById(id);var sh1 = ss1.getSheetByName('2017');var TimeStamp = Utilities.formatDate(new Date()，Session.getScriptTimeZone()，"dd/MM/yy HH:mm:ss");for(i = 5326; i  

I created a function that whenever I run the AppendRow script every row that does not have a dot (".") at the AY column, an array with every information/column that I want from that sheet will be transfered to my main sheet that has around 13k rows atm.

Usually about 20-40 rows get pasted into the first sheet everyday and this script automatically re-arranges the columns to my main sheet.

PROBLEM: The problem is that each row that gets appended to my main sheet takes around 8-15sec to get transfered and sometimes I get this "exceeded-maximum-execution-time" error thats its really annoying.

Is there any way to make my code run faster or maybe just a way to make the script run more than 5 minutes?

function AppendRow() {

  var app = SpreadsheetApp;
  var Sheet = app.getActiveSpreadsheet().getSheetByName("Sheet1");

  var ss2017 = SpreadsheetApp.openById("");
  var sheet2017 = ss2017.getSheetByName("2017");

  var lastSourceRow = Sheet.getLastRow();
  var lastSourceCol = Sheet.getLastColumn();

  var sourceRange = Sheet.getRange(1, 1, lastSourceRow, lastSourceCol);
  var sourceData = sourceRange.getValues();
  var lenght = sourceData.length;
  //Logger.log(lenght);

  var time = new Date();
  time = Utilities.formatDate(time, "GMT+01:00", "dd/MM/yy, HH:mm:ss");


      for(i=5326;i<=lenght;i++) 
//my i=5326 is just cuz I had already
// information on my sheet that was transfered before this script existed
      {

        var columnAY = Sheet.getRange(i,51).getValue();
        var checkReservation = Sheet.getRange(i,2).getValue();

           if(columnAY == ".")
           {
       }
       else
       {
      var B = Sheet.getRange(i,2).getValue();
      var F = Sheet.getRange(i,6).getValue();
      var E = Sheet.getRange(i,5).getValue();
      var K = Sheet.getRange(i,11).getValue();
      var L = Sheet.getRange(i,12).getValue();
      var V = Sheet.getRange(i,22).getValue();
      var O = Sheet.getRange(i,15).getValue();
      var P = Sheet.getRange(i,16).getValue();
      var Q = Sheet.getRange(i,17).getValue();
      var AF = Sheet.getRange(i,32).getValue();
      var AG = Sheet.getRange(i,33).getValue();
      var N = Sheet.getRange(i,14).getValue();
      var AI = Sheet.getRange(i,35).getValue();
      var AB = Sheet.getRange(i,28).getValue();
      var AC = Sheet.getRange(i,29).getValue();   



      var array = ["",B,F,E,K,L,"",V,O,P,Q,AF,AG,"","",N,N,AI,"","",AB,AC,"","","","",time];

      sheet2017.appendRow(array);


       Sheet.getRange(i,51).setValue(".");


       }
  }
}

p.s.: I also have a menu in my spreadsheet to run the script every time i want to not sure if that is any relevant to the question :P

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Menu')
      .addItem('Copy to 2017', 'AppendRow')
      .addToUi();

Every comment is appreciated !! :)

解决方案

I must admit to having a bit of a problem figure out how my i's coordinated with your's but I think my columns are correct. So check it out but this should run a bit faster.

function AppendRow() 
{
  var ss0=SpreadsheetApp.getActive();
  var sh0=ss.getSheetByName('Sheet1');
  var rg0=sh0.getDataRange();
  var vA0=rg0.getValues();
  var ss1=SpreadsheetApp.openById(id);
  var sh1=ss1.getSheetByName('2017');
  var TimeStamp=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yy HH:mm:ss");
  for(i=5326;i<vA0.length;i++)
  {
    var columnAY=vA0[i][50];
    var checkReservation=vA0[i][1];
    if(vA0[i][50]!='.')
    {
      var B = vA0[i][1];//Sheet.getRange(i,2).getValue();
      var F = vA0[i][5];//Sheet.getRange(i,6).getValue();
      var E = vA0[i][4];//Sheet.getRange(i,5).getValue();
      var K = vA0[i][10];//Sheet.getRange(i,11).getValue();
      var L = vA0[i][11];//Sheet.getRange(i,12).getValue();
      var V = vA0[i][21];//Sheet.getRange(i,22).getValue();
      var O = vA0[i][14];//Sheet.getRange(i,15).getValue();
      var P = vA0[i][15];//Sheet.getRange(i,16).getValue();
      var Q = vA0[i][16];//Sheet.getRange(i,17).getValue();
      var AF = vA0[i][31];//Sheet.getRange(i,32).getValue();
      var AG = vA0[i][32];//Sheet.getRange(i,33).getValue();
      var N = vA0[i][13];//Sheet.getRange(i,14).getValue();
      var AI = vA0[i][34];//Sheet.getRange(i,35).getValue();
      var AB = vA0[i][27];//Sheet.getRange(i,28).getValue();
      var AC = vA0[i][28];//Sheet.getRange(i,29).getValue();   
      var array = ["",B,F,E,K,L,"",V,O,P,Q,AF,AG,"","",N,N,AI,"","",AB,AC,"","","","",TimeStamp];
      sh1.appendRow(array);
      sh0.getRange(i+1,51).setValue(".");
    }
  }
}

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