0

I'm using this simple function to quickly autofill the respective column with a formula (in the 2nd row). However, I found that if I happen to want to modify smth in a cell (say C4) at one point, then add more rows and need to re-run it will do it from C2 again and will overwrite any modifications I've made with the formula again. This is why I'd like something more dynamic (set the formula in the last row, referencing other columns/cells from that respective row). The quick fixes I thought of don't take it as a correct range. I've tried to avoid using loops or other complex solutions and just referencing the last row, but it doesn't seem to work. Is there an easy fix I could make to this code. You can see spreadsheet example here here. I hope it makes sense.

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();

var FillC = ss.getRange(2,3,lr-1);
ss.getRange("C2").copyTo(FillC);
//ss.getRange("C"&lr).setFormula('A'&lr&'B'&lr)
//ss.getRange("C2").setFormula (='A'& lastrow(in this case 5) & 'B'& lastrow(5)
}
Debs
  • 133
  • 7
  • Hi, I'm not sure I understand what you want to accomplish here. You want to populate column `C` with a formula that concatenates the values from `A` and `B`, but at the same time you want to be able to manually modify the values of `C` without any change to `A` and `B`? – Iamblichus Jul 08 '22 at 08:43
  • 1
    Your question can be greatly improved if you add a table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Jul 08 '22 at 11:10

1 Answers1

0

I believe your goal is as follows.

  • When the cells "D2:D4" have values, you want a script for putting a formula =A5&B5 to the cell "D5.

In this case, how about using R1C1? When your script is modified, how about the following modification?

Modified script:

function myFunction() {
  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = ss.get1stNonEmptyRowFromBottom(4);
  ss.getRange(row + 1, 4).setFormulaR1C1("=R[0]C[-3]&R[0]C[-2]");
}

  • For example, for the above situation, when you want to put the formulas to the cells from "D5" to the last row, please modify them as follows.

    • From

        ss.getRange(row + 1, 4).setFormulaR1C1("=R[0]C[-3]&R[0]C[-2]");

    • To

        ss.getRange(row + 1, 4, ss.getLastRow() - row - 1).setFormulaR1C1("=R[0]C[-3]&R[0]C[-2]");
Tanaike
  • 181,128
  • 11
  • 97
  • 165