Getting data from a column and pasting it in a row-CodePudding

I'm trying to get the data from an original sheet named "Interface Boletagem" that is column C rows 6 to 28 and paste this data in a new sheet named "Boletador (Dados)" on the last blank row and in different columns. Basically transposing all the data from the original sheet.

I managed to make it work, but the current script is taking a long time to complete... Any tips on how to optimize it?

function submitDataInbound(){

  //declare a variable and set the reference of active google sheet

  var myGoogleSheet=SpreadsheetApp.getActiveSpreadsheet();

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

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

  //code to update the data on database sheet

  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;
  
  datasheet.getRange(blankRow,1).setValue(shUserForm.getRange("C26").getValue());//ID

  datasheet.getRange(blankRow,2).setValue(shUserForm.getRange("C27").getValue());//DF

  datasheet.getRange(blankRow,3).setValue(shUserForm.getRange("C11").getValue());//VMN

  datasheet.getRange(blankRow,4).setValue(shUserForm.getRange("C12").getValue());//VME

  datasheet.getRange(blankRow,5).setValue(shUserForm.getRange("C21").getValue());//Dea

  datasheet.getRange(blankRow,6).setValue(shUserForm.getRange("C22").getValue());//PS

  datasheet.getRange(blankRow,7).setValue(shUserForm.getRange("C23").getValue());//LSBD

  datasheet.getRange(blankRow,8).setValue(shUserForm.getRange("C24").getValue());//SS
    
  datasheet.getRange(blankRow,9).setValue(shUserForm.getRange("C20").getValue());//TID

  datasheet.getRange(blankRow,10).setValue(shUserForm.getRange("C6").getValue());//Cli

  datasheet.getRange(blankRow,11).setValue(shUserForm.getRange("C28").getValue());//CV

  datasheet.getRange(blankRow,12).setValue(shUserForm.getRange("C7").getValue());//Moe

  datasheet.getRange(blankRow,13).setValue(shUserForm.getRange("C16").getValue());//TFX

  datasheet.getRange(blankRow,14).setValue(shUserForm.getRange("C8").getValue());//QtME
  
  datasheet.getRange(blankRow,15).setValue(shUserForm.getRange("C9").getValue());//TxBco

  datasheet.getRange(blankRow,16).setValue(shUserForm.getRange("C10").getValue());//TxCli

  datasheet.getRange(blankRow,17).setValue(shUserForm.getRange("C14").getValue());//IOF

  datasheet.getRange(blankRow,18).setValue(shUserForm.getRange("C13").getValue());//Tar

  datasheet.getRange(blankRow,19).setValue(shUserForm.getRange("C17").getValue());//BPar

  datasheet.getRange(blankRow,20).setValue(shUserForm.getRange("C19").getValue());//Nat

 datasheet.getRange(blankRow,21).setValue(shUserForm.getRange("C18").getValue());//CBPar

  datasheet.getRange(blankRow,22).setValue(shUserForm.getRange("C15").getValue());//IR

  ui.alert('Boletado')

  shUserForm.getRange("C6").clear();
  shUserForm.getRange("C7").clear();
  shUserForm.getRange("C8").clear();
  shUserForm.getRange("C9").clear();
  shUserForm.getRange("C10").clear();
  shUserForm.getRange("C11").clear();
  shUserForm.getRange("C12").clear();
  shUserForm.getRange("C13").clear();
  shUserForm.getRange("C14").clear();
  shUserForm.getRange("C15").clear();
  shUserForm.getRange("C16").clear();
  shUserForm.getRange("C17").clear();
  shUserForm.getRange("C18").clear();
  shUserForm.getRange("C19").clear();   

  shUserForm.getRange("C6").setBackground('#BEBEBE');
  shUserForm.getRange("C7").setBackground('#BEBEBE');
  shUserForm.getRange("C8").setBackground('#BEBEBE');
  shUserForm.getRange("C9").setBackground('#BEBEBE');
  shUserForm.getRange("C10").setBackground('#BEBEBE');
  shUserForm.getRange("C11").setBackground('#BEBEBE');
  shUserForm.getRange("C12").setBackground('#BEBEBE');
  shUserForm.getRange("C13").setBackground('#BEBEBE');
  shUserForm.getRange("C14").setBackground('#BEBEBE');
  shUserForm.getRange("C15").setBackground('#BEBEBE');
  shUserForm.getRange("C16").setBackground('#BEBEBE');
  shUserForm.getRange("C17").setBackground('#BEBEBE');
  shUserForm.getRange("C18").setBackground('#BEBEBE');
  shUserForm.getRange("C19").setBackground('#BEBEBE');

  }

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In this case, how about the following modification? In order to retrieve the values from the scattered cells, I used Sheets API. And, in order to clear and set background to the cells "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", shUserForm.getRange(6, 3, 14).clear().setBackground('#BEBEBE') is used.

Modified script:

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

function submitDataInbound() {
  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 = ["C26", "C27", "C11", "C12", "C21", "C22", "C23", "C24", "C20", "C6", "C28", "C7", "C16", "C8", "C9", "C10", "C14", "C13", "C17", "C19", "C18", "C15"];
  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, 3, 14).clear().setBackground('#BEBEBE');
}

Reference:

Method: spreadsheets.values.batchGet