0
\$\begingroup\$

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($B$2="Gasketed",$A$2=301),"E",if(and($B$2="Gasketed",$A$2=304),"E",if(and($B$2="Gasketed",$A$2=307),"S",if(and($B$2="Highway",$A$2=301),"M",if(and($B$2="Highway",$A$2=304),"D",if(and($B$2="Highway",$A$2=307),"M",0))))))');

//Set Text In Column K

sheet.getRange(lastrow,11).setValue('-');

//Set Text In Column L

sheet.getRange(lastrow,12).setValue('=if($E$2="CL 3","CL3",if($E$2="CL 4","CL4",if($E$2="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($C$2="Tied","",if($C$2="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($B$2="Gasketed",A' + (lastrow) + '/8=trunc(A' + (lastrow) + '/8)),(A' + (lastrow) + '/8)-2,if(AND($B$2="Gasketed",(A' + (lastrow) + '-6)/8=trunc((A' + (lastrow) + '-6)/8)),((A' + (lastrow) + '-6)/8)-2,if(AND($B$2="Gasketed",(A' + (lastrow) + '-12)/8=trunc((A' + (lastrow) + '-12)/8)),((A' + (lastrow) + '-12)/8)-2,if(AND($B$2="Gasketed",(A' + (lastrow) + '-18)/8=trunc((A' + (lastrow) + '-18)/8)),((A' + (lastrow) + '-18)/8)-2,if(AND($B$2="Highway",A' + (lastrow) + '/8=trunc(A' + (lastrow) + '/8)),A' + (lastrow) + '/8,if(AND($B$2="Highway",(A' + (lastrow) + '-6)/8=trunc((A' + (lastrow) + '-6)/8)),(A' + (lastrow) + '-6)/8,if(AND($B$2="Highway",(A' + (lastrow) + '-12)/8=trunc((A' + (lastrow) + '-12)/8)),(A' + (lastrow) + '-12)/8,if(AND($B$2="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($B$2="Gasketed",$A$2=301),"E",if(and($B$2="Gasketed",$A$2=304),"E",if(and($B$2="Gasketed",$A$2=307),"S",if(and($B$2="Highway",$A$2=301),"M",if(and($B$2="Highway",$A$2=304),"D",if(and($B$2="Highway",$A$2=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($E$2="CL 3","CL3",if($E$2="CL 4","CL4",if($E$2="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($C$2="Tied","",if($C$2="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($B$2="Gasketed",A' + (lastrow) + '>6),(A' + (lastrow) + '-(((E' + (lastrow) + ')+2)*8))/6,IF(AND($B$2="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($E$2="CL 3","CL3",if($E$2="CL 4","CL4",if($E$2="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($B$2="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($E$2="CL 3","CL3",if($E$2="CL 4","CL4",if($E$2="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($B$2="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($B$2="Gasketed",$C$2="Tied",$D$2="U-Tie"),"85-00059",if(and($B$2="Highway",$C$2="Tied",$D$2="U-Tie"),"85-00058",if(and($B$2="Gasketed",$C$2="Tied",$D$2="Adjustable"),"85-00048",if(and($B$2="Highway",$C$2="Tied",$D$2="Adjustable"),"85-00042",if($C$2="No Ties","Not Required")))))');

//Set Formula In Column E to Calculate Quantity Required

sheet.getRange(lastrow+6,5).setValue('=if($C$2="Tied",(sum(E' + (lastrow) + ':E' + (lastrow+5) + ')-1)*2,"0")');


//CALCULATE GASKETS  

//Set Formula In Column C

sheet.getRange(lastrow+7,3).setValue('=if($B$2="Gasketed","84-00344","Not Required")');

//Set Formula In Column E to Calculate Quantity Required

sheet.getRange(lastrow+7,5).setValue('=if($B$2="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();
}
\$\endgroup\$
1
  • \$\begingroup\$ 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.) \$\endgroup\$ – 200_success Mar 16 '17 at 23:02
4
\$\begingroup\$

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($B$2="Gasketed",$A$2=301),"E",if(and($B$2="Gasketed",$A$2=304),"E",if(and($B$2="Gasketed",$A$2=307),"S",if(and($B$2="Highway",$A$2=301),"M",if(and($B$2="Highway",$A$2=304),"D",if(and($B$2="Highway",$A$2=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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.