google-apps-script - 从某一行到最后一行的循环函数

Question

我在 A 列中有一个 url 列表，我正在从包含公式（模板行）的行中复制和粘贴公式，以提取数据。然后代码执行 SpreadsheetApp.flush(); 然后复制该特定行的结果并将其作为值粘贴到同一行。

我为此编写的代码如下。

function scraper(){
  copypasteFormulas();
  copypasteResultValues();
}

function copypasteFormulas() {

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");


 var sourceRange = sheet.getRange('B2:T2'); //Copy formulas from template row
 var sourceFormulas = sourceRange.getFormulasR1C1();

 var targetRange = sheet.getRange('B5:T5'); //Paste formulas 
  targetRange.setFormulasR1C1(sourceFormulas);

}

function copypasteResultValues() {

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

 SpreadsheetApp.flush(); //Delay between paste of formula and copypaste of values onto it's self

 var copyFromRange = 'Sheet1!B5:T5'; // Copies result range
 var copyToRangeStart = 'Sheet1!B5'; // Pastes results into itself as values
 var source = sheet.getRange(copyFromRange);
  source.copyTo(sheet.getRange(copyToRangeStart), {contentsOnly: true});

}

但是我希望这个循环遍历每一行直到最后一行，然后从 B5:T5 重新开始到行的末尾。

例如，以下内容保持不变，因为这是复制公式的地方。

 var sourceRange = sheet.getRange('B2:T2'); //Copy formulas from template row
 var sourceFormulas = sourceRange.getFormulasR1C1();

其余的需要随着每一行的填写而改变

 var targetRange = sheet.getRange('B5:T5'); //Paste formulas 
  targetRange.setFormulasR1C1(sourceFormulas);

 var copyFromRange = 'Scraper!B5:T5'; // Copies result range
 var copyToRangeStart = 'Scraper!B5'; // Pastes results into itself as values

我不知道该怎么做。

******更新******

我设法将以下代码放在一起，从某一行循环到最后一行。

function iterativeCopyPaste() {
  // Get array of values in the search Range
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scraper");
 var rangeData = sheet.getDataRange();
 var lastColumn = rangeData.getLastColumn();
 var lastRow = rangeData.getLastRow();
 var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);
 var rangeValues = searchRange.getValues(); // Loop through array and if condition met, add relevant
  
 var sourceRange = sheet.getRange(2,2,1,19); //Copy formulas from template row
 var sourceFormulas = sourceRange.getFormulasR1C1();
 
// Copy paste formulas and copy paste result to values iteration
 for ( j = 5 ; j < lastRow - 1; j++){
      var i = 2;
      if(rangeValues[j][i] === ""){
        var targetRange = sheet.getRange(j, i, 1, 19); //row range to which the formulas are pasted
        targetRange.setFormulasR1C1(sourceFormulas);
        SpreadsheetApp.flush();
    };
      var source = sheet.getRange(j, i, 1, 19); // result range to copy
       SpreadsheetApp.flush();
        source.copyTo(sheet.getRange(j, i, 1, 19), {contentsOnly: true}); // Pastes results into itself as values
       SpreadsheetApp.flush();
  };

};

但是它在最后一行之前停止了两行，所以我不得不在 A 列的列表末尾添加两个虚拟行，这样 url 就会循环通过。

复制到行中的公式使用 importxml 来提取数据，有时它们会卡在“加载”上，这是我使用的公式之一，如下所示。

=if(iserror(IMPORTXML($A2,"//h1[contains(@class,'ch-title')]")),"Loading",IMPORTXML($A2,"//h1[contains(@class,'ch-title')]"))

sheet.getRange(j, i, 1, 19)有没有什么办法只有在没有单元格显示为加载时才进行迭代。

编辑：看来，如果我使用

SpreadsheetApp.flush();
Utilities.sleep(100000);

在公式粘贴到的//行范围下，它似乎缓慢地通过所有 URL，加载所有数据，然后将其作为值复制并粘贴到自身上。到目前为止，它似乎运作良好。

标签： google-apps-scriptgoogle-sheets

Answer 1

这是我整理的最终代码，可以满足我的需求。并将时间触发器设置为每 30 分钟运行一次，以防超时。

   function iterativeCopyPaste() {
  // Get array of values in the search Range
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scraper");
  var rangeData = sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();
  var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);
 var rangeValues = searchRange.getValues(); // Loop through array and if condition met, add relevant
 
 var sourceRange = sheet.getRange(2,2,1,10); //Copy formulas from template row
 var sourceFormulas = sourceRange.getFormulasR1C1();
 var counter = 0;
 

// Copy paste formulas and copy paste result to values iteration
for ( j = 5 ; j < lastRow - 1; j++){
        var i = 2;
    counter++;
    var targetRange = sheet.getRange(j, i, 1, 10); //row range to which the formulas are pasted
        if(targetRange.isBlank()){
        //var targetRange = sheet.getRange(j, i, 1, 10); //row range to which the formulas are pasted
        targetRange.setFormulasR1C1(sourceFormulas);
        
        SpreadsheetApp.flush();
        Utilities.sleep(100000); // Delay before the copy and paste of result values
    };
      var source = sheet.getRange(j, i, 1, 19); // result range to copy
      SpreadsheetApp.flush();
        source.copyTo(sheet.getRange(j, i, 1, 19), {contentsOnly: true}); // Pastes results into itself as values
        SpreadsheetApp.flush();
        if (counter === 4) {    // Delay after every 5 rows
            Utilities.sleep(240000);
          SpreadsheetApp.flush();
          counter = 0;
        };
};

};