Setting values in a spreadsheet

Question

I have put together the following script to automate a process I have to perform at my work. The script solves some basic math functions and then inputs text values in certain cells. I have several of these scripts tied to different items that we produce, activated by a button on the sheet, so that if button A is pressed this script runs, if button B is pressed a similar script runs but with different text values for the other product line.

The script runs as is and performs the function I need it to. That being said, I am new to scripting and most certainly I am not performing all of the functions in the most efficient way. I am trying to work with the little I know to innovate a data entry that I have to perform. I know the people on this forum are some of the most knowledgeable people around when it comes to writing scripts, so I thought someone might have some advice on where to improve the script.

function R36(){
RD36();
deleterows();
BuildUI9();
}
function RD36() {
//Grab the active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//Find The Last Row With Data In It
var lastrow = sheet.getLastRow();
//CALCULATE 8' BARRELS
//Set Text In Column I
sheet.getRange(lastrow,9).setValue('R036');
//Set Formula In Column J for Gasketed or Highway Pipe
 sheet.getRange(lastrow,10).setValue('=if(and($B2ドル="Gasketed",$A2ドル=301),"E",if(and($B2ドル="Gasketed",$A2ドル=304),"E",if(and($B2ドル="Gasketed",$A2ドル=307),"S",if(and($B2ドル="Highway",$A2ドル=301),"M",if(and($B2ドル="Highway",$A2ドル=304),"D",if(and($B2ドル="Highway",$A2ドル=307),"M",0))))))');
//Set Text In Column K
sheet.getRange(lastrow,11).setValue('-');
//Set Text In Column L
sheet.getRange(lastrow,12).setValue('=if($E2ドル="CL 3","CL3",if($E2ドル="CL 4","CL4",if($E2ドル="CL 5","CL5")))');
//Set Formula In Column M for B or C Wall Designation
sheet.getRange(lastrow,13).setValue('=if($J' + (lastrow) + '="E","C","B")'); 
//Set Formula In Column N for Section Length Determination
sheet.getRange(lastrow,14).setValue('=if(A' + (lastrow) + '>=7,8,6)'); 
//Set Formula In Column O for Tie Determination
sheet.getRange(lastrow,15).setValue('=if($C2ドル="Tied","",if($C2ドル="No Ties","NT",0))'); 
//Set Formula In Column C to Calculate Part Number
sheet.getRange(lastrow,3).setValue('=CONCATENATE(I' + (lastrow) + ':O' + (lastrow) + ')'); 
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow,5).setValue('=if(AND($B2ドル="Gasketed",A' + (lastrow) + '/8=trunc(A' + (lastrow) + '/8)),(A' + (lastrow) + '/8)-2,if(AND($B2ドル="Gasketed",(A' + (lastrow) + '-6)/8=trunc((A' + (lastrow) + '-6)/8)),((A' + (lastrow) + '-6)/8)-2,if(AND($B2ドル="Gasketed",(A' + (lastrow) + '-12)/8=trunc((A' + (lastrow) + '-12)/8)),((A' + (lastrow) + '-12)/8)-2,if(AND($B2ドル="Gasketed",(A' + (lastrow) + '-18)/8=trunc((A' + (lastrow) + '-18)/8)),((A' + (lastrow) + '-18)/8)-2,if(AND($B2ドル="Highway",A' + (lastrow) + '/8=trunc(A' + (lastrow) + '/8)),A' + (lastrow) + '/8,if(AND($B2ドル="Highway",(A' + (lastrow) + '-6)/8=trunc((A' + (lastrow) + '-6)/8)),(A' + (lastrow) + '-6)/8,if(AND($B2ドル="Highway",(A' + (lastrow) + '-12)/8=trunc((A' + (lastrow) + '-12)/8)),(A' + (lastrow) + '-12)/8,if(AND($B2ドル="Highway",(A' + (lastrow) + '-18)/8=trunc((A' + (lastrow) + '-18)/8)),(A' + (lastrow) + '-18)/8))))))))');
//CALCULATE 6' BARRELS 
//Set Text In Column I
sheet.getRange(lastrow+1,9).setValue('R036');
//Set Formula In Column J for Gasketed or Highway Pipe
sheet.getRange(lastrow+1,10).setValue('=if(and($B2ドル="Gasketed",$A2ドル=301),"E",if(and($B2ドル="Gasketed",$A2ドル=304),"E",if(and($B2ドル="Gasketed",$A2ドル=307),"S",if(and($B2ドル="Highway",$A2ドル=301),"M",if(and($B2ドル="Highway",$A2ドル=304),"D",if(and($B2ドル="Highway",$A2ドル=307),"M",0))))))');
//Set Text In Column K
sheet.getRange(lastrow+1,11).setValue('-');
//Set Text In Column L
sheet.getRange(lastrow+1,12).setValue('=if($E2ドル="CL 3","CL3",if($E2ドル="CL 4","CL4",if($E2ドル="CL 5","CL5")))');
//Set Formula In Column M for B or C Wall Designation
sheet.getRange(lastrow+1,13).setValue('=if($J' + (lastrow) + '="E","C","B")'); 
//Set Formula In Column N for Section Length Determination
sheet.getRange(lastrow+1,14).setValue('6'); 
//Set Formula In Column O for Tie Determination
sheet.getRange(lastrow+1,15).setValue('=if($C2ドル="Tied","",if($C2ドル="No Ties","NT",0))'); 
//Set Formula In Column C to Calculate Part Number
sheet.getRange(lastrow+1,3).setValue('=CONCATENATE(I' + (lastrow+1) + ':O' + (lastrow+1) + ')'); 
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+1,5).setValue('=if(AND($B2ドル="Gasketed",A' + (lastrow) + '>6),(A' + (lastrow) + '-(((E' + (lastrow) + ')+2)*8))/6,IF(AND($B2ドル="Highway",A' + (lastrow) + '>6),((A' + (lastrow) + '-(E' + (lastrow) + '*8))/6)))'); 
//CALCULATE MALE TRANSITION SECTIONS 
//Set Text In Column I
sheet.getRange(lastrow+2,9).setValue('R036');
//Set Formula In Column J for Gasketed or Highway Pipe
sheet.getRange(lastrow+2,10).setValue('T');
//Set Text In Column K
sheet.getRange(lastrow+2,11).setValue('-');
//Set Text In Column L
sheet.getRange(lastrow+2,12).setValue('=if($E2ドル="CL 3","CL3",if($E2ドル="CL 4","CL4",if($E2ドル="CL 5","CL5")))');
//Set Formula In Column M for B or C Wall Designation
sheet.getRange(lastrow+2,13).setValue('C'); 
//Set Formula In Column N for Section Length Determination
sheet.getRange(lastrow+2,14).setValue('8'); 
//Set Formula In Column O for Tie Determination
sheet.getRange(lastrow+2,15).setValue('EM'); 
//Set Formula In Column C to Calculate Part Number
sheet.getRange(lastrow+2,3).setValue('=CONCATENATE(I' + (lastrow+2) + ':O' + (lastrow+2) + ')'); 
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+2,5).setValue('=if($B2ドル="Gasketed",1,0)'); 
//CALCULATE FEMALE TRANSITION SECTIONS 
//Set Text In Column I
sheet.getRange(lastrow+3,9).setValue('R036');
//Set Formula In Column J for Gasketed or Highway Pipe
sheet.getRange(lastrow+3,10).setValue('T');
//Set Text In Column K
sheet.getRange(lastrow+3,11).setValue('-');
//Set Text In Column L
sheet.getRange(lastrow+3,12).setValue('=if($E2ドル="CL 3","CL3",if($E2ドル="CL 4","CL4",if($E2ドル="CL 5","CL5")))');
//Set Formula In Column M for B or C Wall Designation
sheet.getRange(lastrow+3,13).setValue('C'); 
//Set Formula In Column N for Section Length Determination
sheet.getRange(lastrow+3,14).setValue('8'); 
//Set Formula In Column O for Tie Determination
sheet.getRange(lastrow+3,15).setValue('ME'); 
//Set Formula In Column C to Calculate Part Number
sheet.getRange(lastrow+3,3).setValue('=CONCATENATE(I' + (lastrow+3) + ':O' + (lastrow+3) + ')'); 
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+3,5).setValue('=if($B2ドル="Gasketed",1,0)');
//CALCULATE MALE APRONS 
//Set Text In Column I
sheet.getRange(lastrow+4,9).setValue('R036');
//Set Formula In Column J for Gasketed or Highway Pipe
sheet.getRange(lastrow+4,10).setValue('M');
//Set Text In Column K
sheet.getRange(lastrow+4,11).setValue('-');
//Set Text In Column L
sheet.getRange(lastrow+4,12).setValue('BAM'); 
//Set Formula In Column C to Calculate Part Number
sheet.getRange(lastrow+4,3).setValue('=CONCATENATE(I' + (lastrow+4) + ':O' + (lastrow+4) + ')'); 
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+4,5).setValue("1");
//CALCULATE FEMALE APRONS 
//Set Text In Column I
sheet.getRange(lastrow+5,9).setValue('R036');
//Set Formula In Column J for Gasketed or Highway Pipe
sheet.getRange(lastrow+5,10).setValue('M');
//Set Text In Column K
sheet.getRange(lastrow+5,11).setValue('-');
//Set Text In Column L
sheet.getRange(lastrow+5,12).setValue('BAF'); 
//Set Formula In Column C to Calculate Part Number
sheet.getRange(lastrow+5,3).setValue('=CONCATENATE(I' + (lastrow+5) + ':O' + (lastrow+5) + ')'); 
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+5,5).setValue("1");
//CALCULATE TIES 
//Set Formula In Column C
sheet.getRange(lastrow+6,3).setValue('=if(and($B2ドル="Gasketed",$C2ドル="Tied",$D2ドル="U-Tie"),"85-00059",if(and($B2ドル="Highway",$C2ドル="Tied",$D2ドル="U-Tie"),"85-00058",if(and($B2ドル="Gasketed",$C2ドル="Tied",$D2ドル="Adjustable"),"85-00048",if(and($B2ドル="Highway",$C2ドル="Tied",$D2ドル="Adjustable"),"85-00042",if($C2ドル="No Ties","Not Required")))))');
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+6,5).setValue('=if($C2ドル="Tied",(sum(E' + (lastrow) + ':E' + (lastrow+5) + ')-1)*2,"0")');
//CALCULATE GASKETS 
//Set Formula In Column C
sheet.getRange(lastrow+7,3).setValue('=if($B2ドル="Gasketed","84-00344","Not Required")');
//Set Formula In Column E to Calculate Quantity Required
sheet.getRange(lastrow+7,5).setValue('=if($B2ドル="Gasketed",sum(E' + (lastrow) + ':E' + (lastrow+5) + ')-3,"0")');
}
//DELETE THE ROWS THAT HAVE A ZERO QUANTITY 
function deleterows(){
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var rowsDeleted = 0;
for (var i = 12; i <= numRows - 1; i++) {
var row = values[i];
if (row[4] == 0 || row[4] == '') {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
} 
}
 //ACTIVATE THE UI TO ASK FOR TRASH GRATES OR SAFETY APRONS 
 function BuildUI9() {
 //create the application itself
 var app = UiApp.createApplication().setHeight(360).setWidth(250);
 app.setTitle("Options");
 //create panels and add them to the UI
 var panel = app.createVerticalPanel();
 //create a text box
 var check1 = app.createCheckBox("4:1 Safety Aprons");
 check1.setName('CheckBox1');
 var check2 = app.createCheckBox("6:1 Safety Aprons");
 check2.setName('CheckBox2');
 var check3 = app.createCheckBox("4:1 Safety Aprons (ND)");
 check3.setName('CheckBox3');
 var check4 = app.createCheckBox("Pipe Style Trash Racks (1)");
 check4.setName('CheckBox4');
 var check5 = app.createCheckBox("Pipe Style Trash Racks (2)");
 check5.setName('CheckBox5');
 var check6 = app.createCheckBox("HD Pipe Style Trash Racks (1)");
 check6.setName('CheckBox6');
 var check7 = app.createCheckBox("HD Pipe Style Trash Racks (2)");
 check7.setName('CheckBox7');
 var check8 = app.createCheckBox("Bull Nose Style Trash Racks (1)");
 check8.setName('CheckBox8');
 var check9 = app.createCheckBox("Bull Nose Style Trash Racks (2)");
 check9.setName('CheckBox9');
 var check10 = app.createCheckBox("Flared End Safety Grates (1)");
 check10.setName('CheckBox10');
 var check11 = app.createCheckBox("Flared End Safety Grates (2)");
 check11.setName('CheckBox11');
 var check12 = app.createCheckBox("4:1 Pipe Style Safety Grates (1)");
 check12.setName('CheckBox12');
 var check13 = app.createCheckBox("4:1 Pipe Style Safety Grates (2)");
 check13.setName('CheckBox13');
 var check14 = app.createCheckBox("4:1 Ladder Style Safety Grates (1)");
 check14.setName('CheckBox14');
 var check15 = app.createCheckBox("4:1 Ladder Style Safety Grates (2)");
 check15.setName('CheckBox15');
 var check16 = app.createCheckBox("6:1 Ladder Style Safety Grates (1)");
 check16.setName('CheckBox16');
 var check17 = app.createCheckBox("6:1 Ladder Style Safety Grates (2)");
 check17.setName('CheckBox17');
 //create a submit button
 var button = app.createButton('Done');
 //add the text box and the button to the panel
 panel.add(check1);
 panel.add(check2);
 panel.add(check3);
 panel.add(check4);
 panel.add(check5); 
 panel.add(check6);
 panel.add(check7);
 panel.add(check8);
 panel.add(check9);
 panel.add(check10);
 panel.add(check11); 
 panel.add(check12);
 panel.add(check13);
 panel.add(check14);
 panel.add(check15);
 panel.add(check16);
 panel.add(check17);
 panel.add(button);
 var handler = app.createServerHandler("submitButton9");
 button.addClickHandler(handler);
 handler.addCallbackElement(panel); 
 //add the panel to the application
 app.add(panel);
 var doc = SpreadsheetApp.getActive();
 doc.show(app);
 }
 function submitButton9(e){
 var app = UiApp.getActiveApplication();
 var sheet = SpreadsheetApp.getActiveSheet();
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox1 == 'true') {
 var rows = sheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();
 var rowsDeleted = 0;
 for (var i = lastrow-4; i <= numRows - 1; i++) {
 var row = values[i];
 if (row[2] == 'R036M-BAM' || row[2] == 'R036M-BAF') {
 sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
 rowsDeleted++;}
 } 
 var lastrow = sheet.getLastRow();
 sheet.getRange(lastrow+1,3).setValue("R036M-BSAF4");
 sheet.getRange(lastrow+1,5).setValue("1");
 sheet.getRange(lastrow+2,3).setValue("R036M-BSAM4");
 sheet.getRange(lastrow+2,5).setValue("1");
 }
 if(e.parameter.CheckBox2 == 'true') {
 var rows = sheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();
 var rowsDeleted = 0;
 for (var i = lastrow-4; i <= numRows - 1; i++) {
 var row = values[i];
 if (row[2] == 'R036M-BAM' || row[2] == 'R036M-BAF') {
 sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
 rowsDeleted++;}
 } 
 var lastrow = sheet.getLastRow();
 sheet.getRange(lastrow+1,3).setValue("R036M-BSAF61");
 sheet.getRange(lastrow+1,5).setValue("1");
 sheet.getRange(lastrow+2,3).setValue("R036M-BSAM61");
 sheet.getRange(lastrow+2,5).setValue("1");
 sheet.getRange(lastrow+3,3).setValue("R036M-BSA62");
 sheet.getRange(lastrow+3,5).setValue("2")
 }
 if(e.parameter.CheckBox3 == 'true') {
 var rows = sheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();
 var rowsDeleted = 0;
 for (var i = lastrow-4; i <= numRows - 1; i++) {
 var row = values[i];
 if (row[2] == 'R036M-BAM' || row[2] == 'R036M-BAF') {
 sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
 rowsDeleted++;}
 } 
 var lastrow = sheet.getLastRow();
 sheet.getRange(lastrow+1,3).setValue("R036M-BSAF4ND");
 sheet.getRange(lastrow+1,5).setValue("1");
 sheet.getRange(lastrow+2,3).setValue("R036M-BSAM4ND");
 sheet.getRange(lastrow+2,5).setValue("1");
 }
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox4 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00112");
 sheet.getRange(lastrow+1,5).setValue("1");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox5 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00112");
 sheet.getRange(lastrow+1,5).setValue("2");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox6 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00380");
 sheet.getRange(lastrow+1,5).setValue("1");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox7 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00380");
 sheet.getRange(lastrow+1,5).setValue("2");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox8 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00430");
 sheet.getRange(lastrow+1,5).setValue("1");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox9 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00430");
 sheet.getRange(lastrow+1,5).setValue("2");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox10 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00191");
 sheet.getRange(lastrow+1,5).setValue("1");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox11 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00191");
 sheet.getRange(lastrow+1,5).setValue("2");
 } 
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox12 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00461");
 sheet.getRange(lastrow+1,5).setValue("1");
 }
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox13 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00461");
 sheet.getRange(lastrow+1,5).setValue("2");
 }
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox14 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00013");
 sheet.getRange(lastrow+1,5).setValue("1");
 }
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox15 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00013");
 sheet.getRange(lastrow+1,5).setValue("2");
 }
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox16 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00023");
 sheet.getRange(lastrow+1,5).setValue("1");
 }
 var lastrow = sheet.getLastRow();
 if(e.parameter.CheckBox17 == 'true') 
 {sheet.getRange(lastrow+1,3).setValue("85-00023");
 sheet.getRange(lastrow+1,5).setValue("2");
 }
 return app.close();
 }
//DELETE OUT THE FORMULAS AND PASTE BACK THE VALUES 
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheets()[0]; 
var destination = ss.getSheets()[0];
var range = source.getRange("A13:E500");
range.copyValuesToRange(destination,1,5,13,500);
}
 //CLEAR COLUMNS I THROUGH N 
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; 
var range = sheet.getRange("I13:O500");
range.clearContent();
}

• 200_success: Every question on Code Review asks for code optimization, so the title is not helpful. Please tell us what the code is calculating, and also make that the title of the question. (See How to Ask.)

Answer 1

You can put all the value content to be set into an array, and set all the values at once.

function RD36() { 
 var arrayOfContent,lastrow,outerArray,sheet,ss,startColumn;//Define variables
 ss = SpreadsheetApp.getActiveSpreadsheet();
 sheet = ss.getActiveSheet();
 lastrow = sheet.getLastRow();//Find The Last Row With Data In It
 //Put all the cells content into an array separated by commas
 //Each cell in the spreadsheet will be one element in the array
 arrayOfContent = [
 'R036',
 '=if(and($B2ドル="Gasketed",$A2ドル=301),"E",if(and($B2ドル="Gasketed",$A2ドル=304),"E",if(and($B2ドル="Gasketed",$A2ドル=307),"S",if(and($B2ドル="Highway",$A2ドル=301),"M",if(and($B2ドル="Highway",$A2ドル=304),"D",if(and($B2ドル="Highway",$A2ドル=307),"M",0))))))'
 'etc',
 'etc'
 ];
 startColumn = 9; 
 outerArray = [];
 outerArray.push(arrayOfContent);//Put inner array into outer array
 //in order to create a 2D array
 sheet.getRange(lastrow,startColumn,1,arrayOfContent.length).setValues(outerArray);
}

Because the top section of code is only setting values in one row, it makes it easier. The setValues(values) method must have the values in a 2D array. The outer array will only contain 1 inner array, because the inner array represents one row, and you are only writing to one row. If you were setting multiple rows, then you would need multiple inner arrays, but that's not the case for the section of code that I'm referencing.