Reducing process cost of a script-CodePudding

I'm building a Google Sheet that has two main sheets to it: (i) the user interface where they include the data (Name: "Interface Boletagem" - let's call Sheet A) and (ii) the database where the data gets stored (Name: "Boletador (Dados)" - let's call Sheet B).

I already have a script that got optimzed thanks to the contributors of this forum that transfers the data from Sheet A to the last blank row on Sheet B.

function submitDataOutbound(){

  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGoogleSheet.getSheetByName("Interface Boletagem");
  var datasheet = myGoogleSheet.getSheetByName("Boletador (Dados)");
  var ui = SpreadsheetApp.getUi();
  var columnToCheck = datasheet.getRange("A:A").getValues();

  function getLastRowSpecial(range) {
    var rowNum = 0;
    var blank = false;
    for (var row = 0; row < range.length; row  )
      if (range[row][0] === "" && !blank) {
        rowNum = row;
        blank = true;
      } else if (range[row][0] !== "") {
        blank = false;
      };
    return rowNum;
  };

  var blankRow = getLastRowSpecial(columnToCheck)   1;
  var ranges1 = ["G26", "G27", "G11", "G12", "G21", "G22", "G23", "G24", "G20", "G6", "G28", "G7", "G16", "G8", "G9", "G10", "G14", "G13", "G17", "G19", "G18", "G15"];
  var values = Sheets.Spreadsheets.Values.batchGet(myGoogleSheet.getId(), { ranges: ranges1.map(e => `'Interface Boletagem'!${e}`) }).valueRanges.map(({ values }) => values[0][0]);
  datasheet.getRange(blankRow, 1, 1, values.length).setValues([values]);
  ui.alert('Boletado');
  shUserForm.getRange(6, 7, 14).clear().setBackground('#BEBEBE');

  }

After the data is stored on Sheet B, some calculations are made and I need to populate two summary tables that live under Sheet A with the data from the newly created row in Sheet B. Right now I have a functional script (below), but it takes a long time to run. Any tips on how to optimize it?

function buildTables(){

var myGoogleSheet=SpreadsheetApp.getActiveSpreadsheet();

var datasheet=myGoogleSheet.getSheetByName("Boletador (Dados)");

var sheetdest=myGoogleSheet.getSheetByName("Interface Boletagem");

var columnToCheck = datasheet.getRange("A:A").getValues();

  function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row  )

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
    return rowNum; 
  };

var blankRow=getLastRowSpecial(columnToCheck);

sheetdest.getRange("C36").setValue(datasheet.getRange(blankRow,10).getValue());//ClienteBco
sheetdest.getRange("C53").setValue(datasheet.getRange(blankRow,10).getValue());//ClienteCli

sheetdest.getRange("C37").setValue(datasheet.getRange(blankRow,9).getValue());//CNPJBco
sheetdest.getRange("C54").setValue(datasheet.getRange(blankRow,9).getValue());//CNPJCli

sheetdest.getRange("C38").setValue(datasheet.getRange(blankRow,11).getValue());//compravendaBco
sheetdest.getRange("C55").setValue(datasheet.getRange(blankRow,11).getValue());//compravendaCli

sheetdest.getRange("C39").setValue(datasheet.getRange(blankRow,12).getValue());//moedaBco
sheetdest.getRange("C56").setValue(datasheet.getRange(blankRow,12).getValue());//moedaCli

sheetdest.getRange("C40").setValue(datasheet.getRange(blankRow,14).getValue());//ValorMEBco
sheetdest.getRange("C57").setValue(datasheet.getRange(blankRow,14).getValue());//ValorMECli

sheetdest.getRange("C41").setValue(datasheet.getRange(blankRow,15).getValue());//TxBcoBco

sheetdest.getRange("C42").setValue(datasheet.getRange(blankRow,16).getValue());//TxCliBco
sheetdest.getRange("C58").setValue(datasheet.getRange(blankRow,16).getValue());//TxCambioCli

sheetdest.getRange("C43").setValue(datasheet.getRange(blankRow,17).getValue());//IOFBco
sheetdest.getRange("C59").setValue(datasheet.getRange(blankRow,17).getValue());//IOFCli

sheetdest.getRange("C44").setValue(datasheet.getRange(blankRow,26).getValue());//IOFValorBco
sheetdest.getRange("C60").setValue(datasheet.getRange(blankRow,26).getValue());//IOFValorCli

sheetdest.getRange("C45").setValue(datasheet.getRange(blankRow,18).getValue());//TarifaBco
sheetdest.getRange("C61").setValue(datasheet.getRange(blankRow,18).getValue());//TarifaCli

sheetdest.getRange("C46").setValue(datasheet.getRange(blankRow,27).getValue());//IRValorBco
sheetdest.getRange("C62").setValue(datasheet.getRange(blankRow,27).getValue());//IRValorCli

sheetdest.getRange("C47").setValue(datasheet.getRange(blankRow,28).getValue());//ValorBRLBco
sheetdest.getRange("C63").setValue(datasheet.getRange(blankRow,28).getValue());//ValorBRLCli

sheetdest.getRange("C48").setValue(datasheet.getRange(blankRow,3).getValue());//ValutaBRLBco
sheetdest.getRange("C64").setValue(datasheet.getRange(blankRow,3).getValue());//ValutaBRLCli

sheetdest.getRange("C49").setValue(datasheet.getRange(blankRow,4).getValue());//ValutaMEBco
sheetdest.getRange("C65").setValue(datasheet.getRange(blankRow,4).getValue());//ValutaMECli

}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In this case, I would like to propose to use Sheets API. When Sheets API is used to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function buildTables2() {
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var datasheet = myGoogleSheet.getSheetByName("Boletador (Dados)");
  var columnToCheck = datasheet.getRange("A:A").getValues();
  function getLastRowSpecial(range) {
    var rowNum = 0;
    var blank = false;
    for (var row = 0; row < range.length; row  )

      if (range[row][0] === "" && !blank) {
        rowNum = row;
        blank = true;
      } else if (range[row][0] !== "") {
        blank = false;
      };
    return rowNum;
  };
  var blankRow = getLastRowSpecial(columnToCheck);
  var src = ["J", "J", "I", "I", "K", "K", "L", "L", "N", "N", "O", "P", "P", "Q", "Q", "Z", "Z", "R", "R", "AA", "AA", "AB", "AB", "C", "C", "D", "D"].map(e => `'Boletador (Dados)'!${e   blankRow}`);
  var dst = ["C36", "C53", "C37", "C54", "C38", "C55", "C39", "C56", "C40", "C57", "C41", "C42", "C58", "C43", "C59", "C44", "C60", "C45", "C61", "C46", "C62", "C47", "C63", "C48", "C64", "C49", "C65"].map(e => `'Interface Boletagem'!${e}`);
  var ssId = myGoogleSheet.getId();
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: src }).valueRanges.map(({ values }) => values ? values[0][0] : "");
  var data = values.map((e, i) => ({ values: [[e]], range: dst[i] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ssId);
}

Modified script:

References: