vendredi 14 juin 2019

google App script - replace formula with static values if condition is met

Trying to come up with a Google App Script that replaces formula outputs with static values for previous weeks.

Spreadsheet has 'Report' Sheet which is updated once a week (new data are overwritten) and cell B3 gets total number of cutomers from this report. 'Summary' Sheet, where row B29:BA29 are calendar weeks. Cell B5 - current CW number for report. Row B32:BA32 contains weekly data on number of customers that should be picked up from Report Sheet.

Since Report is always updating would lie to hardcode formula results for past weeks.

Tried with loop and w/o it. Tried getValues, Logger, setValues. Also tries copyValuesToRange. Either nothing is replaced or all weeks get static values even future ones.

function freeze() {

var sss = SpreadsheetApp.openById('XXX').getSheetByName('Summary');

var cw = sss.getRange('B5').getValue;//current week

var weeks = sss.getRange('B29:BA29').getValues(); // all weeks

    for (var i=0; i<weeks.length; i++) {

         if (weeks < cw) 

      {  var dered = sss.getRange(32,2,1,27);

         dered.copyValuesToRange(sss,2,27,32,32);

}}}

or another code

function freeze() {

var sss = SpreadsheetApp.openById('XXX');

var cw = sss.getSheetByName('Summary').getRange('B5').getValue;

var weeks = sss.getSheetByName('Summary').getRange('B29:BA29').getValues(); 

 var dered = sss.getSheetByName('Summary').getRange('B32:BA32');

 var deredvalues = dered.getValues();

      if (weeks < cw) {

  var deredsum = msred.setValues(deredvalues);

} }

or like this

 function freeze() {

  var sss = SpreadsheetApp.openById('XXX');

  var cw = sss.getSheetByName('Summary').getRange('B5').getValue;

  var weeks = sss.getSheetByName('Summary').getRange('B29:BA29').getValues(); 

  var dered = sss.getSheetByName('Summary').getRange('B32:BA32');

    if (weeks < cw) {

   var deredvalues = dered.getValues();

     Logger.log(deredvalues)

        dered.setValues(deredvalues);

  } }

Either nothing is replaced or all weeks get static values even future ones. Would be grateful for ideas.

Aucun commentaire:

Enregistrer un commentaire