Apps Script - How could this code be streamlined?-CodePudding

I've recently started working with Apps Script to improve the scope of what my google sheets can do, and I wanted to ask more experienced people how I might make my script more efficient. I used a mixture of tutorials, documentation, and trial & error to make it. I find that although it usually completes the task it's meant for, sometimes it takes an unreasonably long time or exceeds its runtime and simply stops.

I would like to know which best practices I could implement to make it run more quickly overall, and which things I might be able to include in future scripts to avoid any pitfalls I'd landed in here.

Scope: The script is meant to take each day's new data and apply it to a new sheet called 'TODAY.' It works as follows.

Rename the tab labeled 'TODAY' to the previous workday's date (if today is 2.3, it renames the sheet to 2.2.)
Hide this renamed tab.
Duplicate the 'TEMPLATE' tab, and rename it to 'TODAY.'
Pull data from the 'RAW DATA' tab, and paste it into the new 'TODAY' tab.
Paste a formula into the new 'TODAY' tab and drag it down to the bottom of the table so that the correct values populate and the conditional formatting occurs.

Any help would be greatly appreciated, I really just need some direction for how to improve my work.

Here is a link to an example sheet with editing permissions enabled: https://docs.google.com/spreadsheets/d/1F7bAd2DjKgk53e-haPgjWfFphMfu5YBn8iRQ3qwC3n0/edit?usp=sharing

CodePudding user response：

In my humble opinion, a good Google Sheet App Script doesn't need to use activate to control the source or destination of data. The sheet and script developer should know what and where they want the data to come from and go. Activate is like using the mouse to click on something.

I've taken your script and rewritten to minimize the use of variables. I have only one sheet variable and reuse it throughout. In fact for the majority of the time it is the copy of the TEMPLATE called TODAY.

Also unless I have to use a sheet last row many times, I avoid using a variable and instead just use sheet.getLastRow(). Same for columns.

I always wrap my code in a try catch block as a matter of habit.

As a last note, unless you change the notation in column C and N you could have used your script to fill in column B.

function myDailyUpdate() {
  try {

    let spread = SpreadsheetApp.getActiveSpreadsheet();

    // Step 1
    let sheet = spread.getSheetByName("TODAY");
    let oldDate = sheet.getRange("Q4").getValue();
    let prevDate = Utilities.formatDate(oldDate,"GMT-5","M.d");
    // Renames old 'TODAY' sheet to previous workday's date.
    sheet.setName(prevDate);
    // Sets the color to red.
    sheet.setTabColor("990000");
    // Hides the old 'TODAY' sheet
    sheet.hideSheet();

    // Step 2
    sheet = spread.getSheetByName("TEMPLATE");
    // Copies the contents of the 'TEMPLATE' sheet to a new sheet called 'TODAY.'
    sheet = sheet.copyTo(spread);
    sheet.setName("TODAY");
    sheet.activate();  // required to move to 1st position
    // Move TEMPLATE to first position
    spread.moveActiveSheet(1);

    // Step 3
    // Colors the 'TODAY' tab green to signify it being active.
    sheet.setTabColor("6aa85f")

    // Identifies the 'RAWDATA' sheet for later use.
    let source = spread.getSheetByName("RAWDATA");
    // Identifies ranges and values for later use.
    let values = source.getDataRange().getValues();

    // sheet is still the "TODAY" sheet
    // Identifies 'TODAY' sheet as the recipient of 'RAWDATA' data, and identifies the range.
    // Sets the values from 'RAWDATA' into 'TODAY.'
    sheet.getRange(12,2,values.length,values[0].length).setValues(values);

    // Step 4
    // sheet is still the "TODAY" sheet
    let range = sheet.getRange("C12");
    range.setFormula(
      '=IFERROR(IFERROR(IFS(VLOOKUP($B12,INDIRECT'
       '('
       '"'
       '\''
       '"'
       '&$Q$4&'
       '"'
       '\''
       '!"&"!"&"A1:O2000")'
       ',15,false)="D","D",$N12="Quote","Q",$N12="Important","I",$N12="On Hold","H",$N12="IN TRANSIT","T",$N12="REQUEST","R",$N12="INCOMPLETE","N",$N12="COMMENT","C"),VLOOKUP($N12,$B$3:$C$9,2,FALSE)),"")');
    // Pastes the above formula into cell C12.

    let fillRange = sheet.getRange(12,3,values.length,1);
    range.copyTo(fillRange);
    sheet.activate();

  }
  catch(err) {
    console.log(err);
  }
}