Google Sheets Script Needed: On Insert_Row, set multiple formulas

Question

Novice Google Apps Scripter here,

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

I seem to have the formulas set up correctly, but when a new row is added, the formulas obviously do not auto-populate into that new row. When a row is inserted, in which the corresponding cells in Columns A and B are not blank, I'd like set certain formulas in that row.

The script I have so far (see below) does give me the formulas I want, but only in Row1. I'd like the script to set those same formulas into corresponding cells of any new row that is inserted.

For example, if I were to insert a blank row after Row5 (i.e., create a new Row6), then the formulas will appear in C6:H6

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

  var cell = sheet.getRange("C1");
  cell.setFormula('=IFERROR(MID($B2,SEARCH("details",$B2)+7,SEARCH(",",$B2)-SEARCH("details",$B2)-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("D1");
  cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B2,FIND("$",B2),LEN(B2))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("E1");
  cell.setFormula('=IFERROR(MID($B2,SEARCH("exceed",$B2)+7,SEARCH("%",$B2)-SEARCH("exceed",$B2)-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("F1");
  cell.setFormula('=IFERROR(MID($B2,SEARCH("due",$B2)+3,SEARCH(";",$B2)-SEARCH("due",$B2)-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("G1");
  cell.setFormula('=IFERROR(MID($B2,SEARCH("held on",$B2)+7,SEARCH(". Lottery",$B2)-SEARCH("held on",$B2)-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
  var cell = sheet.getRange("H1");
  cell.setFormula('=IFERROR(MID($B2,SEARCH("posted by",$B2)+9,SEARCH(". ",$B2)-SEARCH("",$B2)-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');

}

Any assistance will be greatly appreciated!

I recommend that you read this documentation it's very well written and very well organized. When your finished reading then try to write the script yourself and if you have any trouble come back with a copy of what you've done and then we can help you. — Cooper
Looks well written and well organized. However, it's still like Greek to me. Can anyone get me started? — minky_boodle
Start by writing a script to set formulas, then add an onEdit Trigger. — Robin Gertenbach

OblongMedulla OblongMedulla · Accepted Answer · 2017-04-06T12:32:12

This is a start for your script. It is how you assign a formula to a cell.

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

  var cell = sheet.getRange("C1");
  cell.setFormula('=IFERROR(MID($B2,SEARCH("details",$B2)+7,SEARCH(",",$B2)-SEARCH("details",$B2)-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
}

Google Sheets Script Needed: On Insert_Row, set multiple formulas

1 Answers