用于“标记"的Google脚本非空白行 [英] Google Script For "Marking" Non Blank Rows

问题描述

我正在尝试创建一个Google脚本，该脚本将查看我的工作表，获取数据长度，然后将已处理"标记为在P列中.我无法使用array/if公式来执行此操作，因为我需要知道何时出现新行并且尚未对其进行处理.我当前的脚本只是将永无止境的数据写入P列.我希望它仅写入基于A列的最后一个非空白行.

I'm trying to create a google script that will look at my sheet, get the length of data and then mark "processed" in Column P. I can't do this with an array/if formula because I need to know when a new line comes in and has not been processed. My current script just writes never ending data to column P. I want it to only write to the last non blank row based on Column A.

这是我的示例工作表和脚本不起作用.

Here is my example sheet and script that isn't working.

https://docs.google.com/spreadsheets /d/13Q4I91O4vMDSCHwAMG0DOLLkbzLCxWiQzbugJ-6nPjs/edit?usp = sharing

 function MarkrowsProcessed() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spr.getSheetByName("Sheet1");
  var column = spr.getRange('A:A').getValues();

  for (var i = 0; i < column.length; ++i) 
  {
    var rowData = column[i];
    var emailSent = rowData[16];
    if (emailSent != 'Processed') 
    {       
   sheet.getRange(i+1, 16, column.length, 1).setValue('Processed');// Make sure the cell is 
updated right away in case the script is interrupted
 }

}
}

预先感谢您的帮助.

推荐答案

我不确定我是否正确理解任务.您是否需要在"P"列的每个单元格中写入已处理"，还是仅在其末尾写入?

I'm not sure if I understand the task right. Either you need write 'Processed' in every cell in column 'P' or only at the end of it?

这是经过纠正的函数，该函数获取列'A'的长度，遍历列'P'并写入'已处理':

Here is corrected function that gets the length of column 'A', iterates trough column 'P' and writes 'Processed':

function MarkrowsProcessed() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spr.getSheetByName("Sheet1");
  
  // get data of column 'A'
  var column_A = spr.getRange('A:A').getValues();

  // get a len of column 'A'
  var len = column_A.filter(String).length; // it works if the column has no empty cells inside  
  
  // get data of column 'P'
  var column_P = spr.getRange('P:P').getValues();  
  
  // loop trough data of column 'P' (until last row in column 'A') and write 'Processed'
  for (var i=0; i<len; ++i) 
  {
    if (column_P[i] != 'Processed') sheet.getRange('P' + (i+1)).setValue('Processed');
  }
}

如果您需要图纸上的最后一行(而不是列"A"中的最后一行)，它将更加容易.您需要在函数中更改一行:

If you need the very last row on the sheet (not the last row in column 'A') it will be even easier. You need to change one line in the function:

  var len = sheet.getLastRow();

这篇关于用于“标记"的Google脚本非空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！