Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I mask values using setValues in google apps scripts?

I'm using google apps script to construct a custom spreadsheet. In order to improve rendering speed of the script I first build an array of output values and then use setValues() to build the spreadsheet all at once.

When I try to add formulas to into the spreadsheet with setFormulas() it overwrites all my values as well. So now I have a case where setValues() overwrites my formulas and setFormulas() overwrites my values.

Is there a way I can specify a masking value for specific elements in my output array so that those elements won't overwrite data already in the spreadsheet?

Ideally I would initialize every element of the output array to this masking value, then only the non-masked elements would show up in the spreadsheet.

like image 605
Eclipzer Avatar asked Oct 21 '22 19:10

Eclipzer


1 Answers

For anyone that finds this through Google, using setValues() for formulas now seems to work in the new sheets.

More specifically, I'm using code like this to put formulas back in after doing work on the sheet:

function setValuesAndFormulas() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var formulas = range.getFormulas();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (formulas[i][j].charAt(0) == "=") {
        values[i][j] = formulas[i][j];
      }
    }
  }

  range.setValues(values);
}

Hope that helps!

like image 180
coagmano Avatar answered Oct 24 '22 12:10

coagmano