Can I use Insert_Row as trigger for cell.setFormula?

Question

Novice Google Apps Scripter here,

I have an IFTTT applet which adds a row to this spreadsheet via email: Data Test

Thanks to the amazing StackOverflow community, I'm now able to manually add a row which sets up the needed formulas with the correct reference, I'd like the script to automatically set those same formulas into corresponding cells of any new row that is inserted.

For example, my IFTTT.com automation will populate cells A6 and B6 with text (i.e., creating next blank row in linked spreadsheet) -- I need all of the formulas currently entered to then apply to B6 (as opposed to B2)

var ss = SpreadsheetApp.getActiveSpreadsheet();
function onOpen() {
  var items = [
    {name: 'Add Row', functionName: 'addrow'},
  ];
    ss.addMenu('Add Row', items);
    }

function addrow() {
  var ui = SpreadsheetApp.getUi(); 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var result = ui.prompt(
      'Enter number of Row',
      ui.ButtonSet.OK_CANCEL);

    // Process the user's response.
  var button = result.getSelectedButton();
  var rownum = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
  sheet.insertRowAfter(rownum);
  var cell = sheet.getRange("C" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("details",$B' + rownum +')+7,SEARCH(",",$B' + rownum +')-SEARCH("details",$B' + rownum +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("D" + rownum);
  cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + rownum +',FIND("$",B' + rownum +'),LEN(B' + rownum +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("E" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("exceed",$B' + rownum +')+7,SEARCH("%",$B' + rownum +')-SEARCH("exceed",$B' + rownum +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("F" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("due",$B' + rownum +')+3,SEARCH(";",$B' + rownum +')-SEARCH("due",$B' + rownum +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("G" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("held on",$B' + rownum +')+7,SEARCH(". Lottery",$B' + rownum +')-SEARCH("held on",$B' + rownum +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("H" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("posted by",$B' + rownum +')+9,SEARCH(". ",$B' + rownum +')-SEARCH("",$B' + rownum +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".

  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.

  }
}

To be clear, what I'd like to do (instead of adding a menu to manually insert a row) is have a script that detects whenever a new row is inserted, triggering the following:

  var cell = sheet.getRange("C" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("details",$B' + rownum +')+7,SEARCH(",",$B' + rownum +')-SEARCH("details",$B' + rownum +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("D" + rownum);
  cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + rownum +',FIND("$",B' + rownum +'),LEN(B' + rownum +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("E" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("exceed",$B' + rownum +')+7,SEARCH("%",$B' + rownum +')-SEARCH("exceed",$B' + rownum +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("F" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("due",$B' + rownum +')+3,SEARCH(";",$B' + rownum +')-SEARCH("due",$B' + rownum +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("G" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("held on",$B' + rownum +')+7,SEARCH(". Lottery",$B' + rownum +')-SEARCH("held on",$B' + rownum +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("H" + rownum);
  cell.setFormula('=IFERROR(MID($B' + rownum +',SEARCH("posted by",$B' + rownum +')+9,SEARCH(". ",$B' + rownum +')-SEARCH("",$B' + rownum +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');

Any assistance will be greatly appreciated!

score 1 · Accepted Answer · answered Apr 10 '17 at 18:18

This works anytime the spreadsheet is edited. You may have to test it as "ANYTIME" it is edited it adds the functions. Does not seem the best way to do it, however it is somewhat what you were looking for.

function onEdit(e){
    var ui = SpreadsheetApp.getUi(); 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var range = e.range;
    var range2 = range.getRow();
    var destrange = range2 + 1;
  
    // inserts blank row after last edit.
    sheet.insertRowAfter(range2);
  
    // adds the formulas
    var cell = sheet.getRange("C" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("details",$B' + destrange +')+7,SEARCH(",",$B' + destrange +')-SEARCH("details",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("D" + destrange);
    cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + destrange +',FIND("$",B' + destrange +'),LEN(B' + destrange +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("E" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("exceed",$B' + destrange +')+7,SEARCH("%",$B' + destrange +')-SEARCH("exceed",$B' + destrange +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("F" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("due",$B' + destrange +')+3,SEARCH(";",$B' + destrange +')-SEARCH("due",$B' + destrange +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("G" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("held on",$B' + destrange +')+7,SEARCH(". Lottery",$B' + destrange +')-SEARCH("held on",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("H" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("posted by",$B' + destrange +')+9,SEARCH(". ",$B' + destrange +')-SEARCH("",$B' + destrange +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');

}

Getting error: TypeError: Cannot read property "range" from undefined. (line 5, file "Code") — minky_boodle, Apr 10 '17 at 18:20
Ok cancel that, it works in spreadsheet itself. What about if I only want it to check for changes in Column 2? — minky_boodle, Apr 10 '17 at 19:46

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

As suggested in this related SO post - Google Script: function that insert new row copying functions/formulas from last row, try using getFormula() from previous row and setFormula() to the new row.

getFomula() code :

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This assumes we have a function in B5 that sums up
 // B2:B4
 var range = sheet.getRange("B5");

 // Logs the calculated value and the formula
 Logger.log("Calculated value: %s Formula: %s",
            range.getValue(),
            range.getFormula());

setFormula() code:

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("B5");
 cell.setFormula("=SUM(B3:B4)");

Hope this helps.

edited May 23 '17 at 11:46

Community

1
1

answered Apr 09 '17 at 14:54

Mr.Rebot

6,703
2
16
91

ok but i don't need to insert a new row, that's already being done for me automatically. Instead, I need a script to detect that a new row has been inserted, and subsequently either populate cells with formulas or copy formulas from a prev. row. – minky_boodle Apr 09 '17 at 19:03
Try checking this related SO [post](http://stackoverflow.com/a/15234631/5995040), there are difficulties on detect user inserting row or column in a google spreadsheet and reacting in a script. – Mr.Rebot Apr 09 '17 at 19:25
what about using an onEdit trigger to fill the last non-blank row with the formulas? what would that look like? – minky_boodle Apr 09 '17 at 19:40
If you'll check the link of the post above, there is someone who used onEdit() and onChange() to track this changes. – Mr.Rebot Apr 09 '17 at 19:47

score 0 · Answer 3 · answered Apr 11 '17 at 14:50

A different way to do this- Now if the last row column B is not blank it will fill in formulas. Works on each edit.

function onEdit(){
    var ui = SpreadsheetApp.getUi(); 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var lastRow = ss.getLastRow();
    var lastRow2 = lastRow+1;
    var s = ss.getSheetByName('sheet1');
    var lRow = s.getRange(lastRow, 2).getValue();
    var newRow = s.getRange(lastRow2, 2).getValue();
     if(lRow != ""){

    // adds the formulas
    var cell = sheet.getRange("C" + lastRow2);
    cell.setFormula('=IFERROR(MID($B' + lastRow2 +',SEARCH("details",$B' + lastRow2 +')+7,SEARCH(",",$B' + lastRow2 +')-SEARCH("details",$B' + lastRow2 +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("D" + lastRow2);
    cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + lastRow2 +',FIND("$",B' + lastRow2 +'),LEN(B' + lastRow2 +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("E" + lastRow2);
    cell.setFormula('=IFERROR(MID($B' + lastRow2 +',SEARCH("exceed",$B' + lastRow2 +')+7,SEARCH("%",$B' + lastRow2 +')-SEARCH("exceed",$B' + lastRow2 +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("F" + lastRow2);
    cell.setFormula('=IFERROR(MID($B' + lastRow2 +',SEARCH("due",$B' + lastRow2 +')+3,SEARCH(";",$B' + lastRow2 +')-SEARCH("due",$B' + lastRow2 +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("G" + lastRow2);
    cell.setFormula('=IFERROR(MID($B' + lastRow2 +',SEARCH("held on",$B' + lastRow2 +')+7,SEARCH(". Lottery",$B' + lastRow2 +')-SEARCH("held on",$B' + lastRow2 +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("H" + lastRow2);
    cell.setFormula('=IFERROR(MID($B' + lastRow2 +',SEARCH("posted by",$B' + lastRow2 +')+9,SEARCH(". ",$B' + lastRow2 +')-SEARCH("",$B' + lastRow2 +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     }
}

Can I use Insert_Row as trigger for cell.setFormula?

3 Answers3