Getting data from a column and pasting it in a row

Question

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');

  }

score 0 · Accepted Answer · answered Mar 11 '22 at 02:15

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

Thanks! It did work... I edited my initial question to add more points that could be optimized as well. Any tips on how to use Sheets APIs for that? — user18433675, Mar 11 '22 at 12:03
@user18433675 About `I edited my initial question to add more points that could be optimized as well. Any tips on how to use Sheets APIs for that?`, please don't change your question. Because my proposed answer is your initial question. First, please resolve your initial question. When you have another question, please post it as a new question. Because when you change your question, it confuses other users. So I updated your question to your initial question. — Tanaike, Mar 11 '22 at 12:17
Sorry @Tanaike, I have created a new question. Thanks a lot!! — user18433675, Mar 11 '22 at 13:30
@user18433675 Thank you for your response. I would like to check it. — Tanaike, Mar 11 '22 at 13:32
this is the link for the new question: https://stackoverflow.com/questions/71439241/reducing-process-cost-of-a-script — user18433675, Mar 11 '22 at 13:38

Getting data from a column and pasting it in a row

1 Answers1

Modified script:

Reference:

Linked