如何使用 Google App Script 设置范围直到工作表中列的最后一行 [英] How to set the range until last row of a column in sheet using Google App Script

问题描述

我正在创建一个公式，它在 AppScript 中特定列的最后一行(例如 B 列的最后一行)之前一直有效.我在这里附上了公式.谁能帮帮我

I am creating a formula where it will be effective until the last row of a particular column (for example last row of column B) in AppScript. I have attached the formula here. Can anyone help me with it

sheet.getRange("A2:A11").setFormula('=IF(ISBLANK(B2:B),"",(B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")))');

推荐答案

试试这个

var lastR = getLastDataRow(sheet,"A")
sheet.getRange("A2:A" + lastR).setFormula('=IF(ISBLANK(B2:B),"",(B2:B &"."&substitute(substitute(lower(C2:C),"jalan","jln")," ","")))');

并添加此功能

function getLastDataRow(sheet,col) {
// col in letter
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

这篇关于如何使用 Google App Script 设置范围直到工作表中列的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！