CODEWITHSUNDEEP
for-loopif-statementgoogle-apps-script
Maryna
Maryna

Reputation: 5

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.

Here is Sample Google Sheet https://docs.google.com/spreadsheets/d/1GaqGBG4YcSihlP10weIr7tnGGgNSvf9XcftEoYeS4DY/edit#gid=0

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 like to hardcode formula results for past weeks.

Tried with loop and w/o it. Tried getValues, Logger, setValues. Also tried 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++) {
    // alternatively tried this loop 
    //  for (var i=0; i<weeks[0].length; i=i+1)

         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.

Upvotes: 0

Views: 1105

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

The procedure you should follow to implement your desired functionality:

  1. Check in sheet Summary, cell B5 which calendar week it is
  2. Find the column assigned to the respective calendar week
  3. Get the values from the cells B2 and B3 from sheets Us Category1 and Category2
  4. Paste the values in the corresponding rows in sheet Summary - in the column that corresponds to the current calendar week

This is what it could look like, based on the script file bound to the spreadsheet you added in the comments:

function logUS() {

  var ss = SpreadsheetApp.openById('1Vy1k_ejwxA4DJPqo_Pa0b5kXSkvE44MHRPR3lNF3wCw');
  var country = "US";
  var summary=ss.getSheetByName('Summary');
  var cw = summary.getRange('B5').getValue();
  var cat1 = ss.getSheetByName('US Category1').getRange('B2').getValue();
  var cat1cancel = ss.getSheetByName('US Category1').getRange('B3').getValue();
  var cat2 = ss.getSheetByName('US Category2').getRange('B2').getValue();
  var cat2cancel = ss.getSheetByName('US Category2').getRange('B3').getValue();

  var ts = ss.getSheetByName('CopyValues').appendRow([country+cw,cw,cat1,cat2,cat1cancel,cat2cancel]); 
  var columnNumbers=summary.getLastColumn()-1; 
  var weekRange=summary.getRange(31,2, 1, columnNumbers);
  for(i=0;i<columnNumbers; i++)
   {
    if(summary.getRange(31,(2+i)).getValue()==cw)
       {
       summary.getRange(33,(2+i)).setValue(cat1);
       summary.getRange(34,(2+i)).setValue(cat2);
       summary.getRange(36,(2+i)).setValue(cat1cancel);
       summary.getRange(37,(2+i)).setValue(cat2cancel);
      }   
   } 
}

Upvotes: 0

Related Questions