google apps script - Need to Set_Formula on Insert_Row from IFTTT -


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, ifttt.com automation populate cells a6 , b6 text (i.e., next blank row in linked spreadsheet) -- need of formulas entered apply b6 (as opposed b2)

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!

try out:

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 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 title bar.      }  }


Comments

Post a Comment

Popular posts from this blog

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more