0
votes

I've been handling a big set of Data with automated scripts in google drive. These script constantly update some cells in a column through different sheets.

Now here is the problem: Reading and writing in google scripts is super long. So I tried to get the column via getValues, dump it in a an array, do my researches and my mods there and write the whole array back to the sheet.

However, this erases the formulas that might have been in the column (getValues would then return the result of the formula). Using getFormulas wouldnt give back the values. I could write and read formulas and values, but that would add operations when I'm trying to save time.

How come google doesn't provide a way to directly dump a String content of each cell? Any work around?

A.

1
Well, I could find any google specified answer, but I developed a merge function and it's actually fast enough, if anyone needs it : function dumpColumn(sheet, index){ var range = sheet.getRange(1, index, sheet.getLastRow(), 1); var arrV = range.getValues(); var arrF = range.getFormulas(); for(i = 0; i<arrF.length; i++){ if(arrF[i][0] != "") arrV[i][0] =arrF[i][0]; } return arrV; }A. Laurent

1 Answers

0
votes

Well, I could find any google specified answer, but I developed a merge function and it's actually fast enough, if anyone needs it :

function dumpColumn(sheet, index){
  var range  = sheet.getRange(1, index, sheet.getLastRow(), 1);
  var arrV =  range.getValues();
  var arrF = range.getFormulas();
  for(i = 0; i<arrF.length; i++){
    if(arrF[i][0] != "")
      arrV[i][0]  =arrF[i][0];   
  }
  return arrV;
}