novice google apps scripter here,
i have ifttt applet adds row spreadsheet via email: data test
i seem have formulas set correctly, when new row added, formulas not auto-populate new row. when row inserted, in corresponding cells in columns , b not blank, i'd set formulas in row.
the script have far (see below) give me formulas want, in row1. i'd script set same formulas corresponding cells of new row inserted.
for example, if insert blank row after row5 (i.e., create new row6), formulas 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 appreciated!
this start script. how assign formula 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"))'); }
Comments
Post a Comment