CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Ben
Ben

Reputation: 5626

Google Sheets Prevent Formula Target Changing when AppsScript Runs

I have an AppsScript attached to a Google Sheet. The functionality is like basic data entry - when I enter a value into A1 (onEdit()), a new cell is added at A4 (the column cells shift down), and the value from A1 is moved to A4.

As new data is entered into A1, the column data continues to grow downward, with content starting at A4 and with the most recent entry on top.

function onEdit(e) {
  var range = e.range;
  if (range.getRow() == 1.0) {
    var targetRange = e.range.offset(3, 0);

    targetRange.insertCells(SpreadsheetApp.Dimension.ROWS);

    range.moveTo(targetRange);
  }
}

Also, in column B, I have a formula that should report the length of the adjacent string in column A. So the cell at B4 would report the length of the string at A4 with this formula:

=LEN($A$4)

The problem is that even with the $ in the formula, every time my AppsScript runs (when I enter data into A1) the formula shifts. So =LEN($A$4) becomes =LEN($A$5) becomes =LEN($A$6) etc... as I continue to enter data, and none of the new cells

How can I prevent this?

I do have a workaround, which is to also create the formula in the AppsScript also, but I would still like to know if the behavior is preventable. Workaround:

function onEdit(e) {
  var range = e.range;
  if (range.getRow() == 1.0 && ((range.getColumn() - 1) % 3 == 0)) {
    if (range.getValue() !== "") {
      var targetRange = e.range.offset(3, 0);
      var nextCol = targetRange.offset(0, 1);

      targetRange.insertCells(SpreadsheetApp.Dimension.ROWS);
      nextCol.insertCells(SpreadsheetApp.Dimension.ROWS);
      range.moveTo(targetRange);

      nextCol.setFormulaR1C1("=LEN(R[0]C[-1])");
    }
  }
}

Upvotes: 0

Views: 104

Answers (1)

JPV
JPV

Reputation: 27262

Maybe try

=LEN(INDIRECT("A4"))

Reference:

Upvotes: 1

Related Questions