i have following script inserting formulas via script run calculations data entered in spreadsheet. last line doesn't work wondering if there anyway include typical roundup function in formula net height calculation one. advice.
function mh484020() { //grab active spreadsheet var ss = spreadsheetapp.getactivespreadsheet(); var sheet = ss.getactivesheet(); //find first last row data in var lastrow = sheet.getlastrow(); //use last row data in place forumulas below var range = sheet.getrange(lastrow+1,2); range.setvalue('drainage str. 48-4020') var range = sheet.getrange(lastrow+1,3) range.setvalue('484020') //set data validation steps or not var range = sheet.getrange(lastrow+1,4); var rule = spreadsheetapp.newdatavalidation().requirevalueinlist(['yes','no']) .build(); range.setdatavalidation(rule); //covers calculation sheet.getrange(lastrow+1,7).setformula("=f" + (lastrow+1)); //net height calculation sheet.getrange("h" + (lastrow+1)).setformula("=e" + (lastrow+1) + "-(f" + (lastrow+1) + "*1.5)"); //mastic calculation sheet.getrange(lastrow+1,9).setformula("=roundup"( + "h" + (lastrow+1))); }
you have quotes in wrong place
sheet.getrange(lastrow+1,9).setformula("=roundup(h" + (lastrow+1) + ")"); if have problems kind of thing first put formula in cell , make sure calculates correctly.then copy formula , paste between paranthesis setformula( )
.setformula(=e5-(f5*1.5)); next put quotes @ beginning , end of formula
.setformula("=e5-(f5*1.5)"); next replace each row number with: (including quotes)
" + (lastrow+1) + " as in:
.setformula("=e" + (lastrow+1) + "-(f" + (lastrow+1) + "*1.5)"); if row number ends formula should leave out ending plus sign , quote.
=e5-f5 .setformula("=e" + (lastrow+1) + "-f" + (lastrow+1)); 