CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Giuseppe Baldi
Giuseppe Baldi

Reputation: 43

How can I increment a cell referenced in a script each time I run it?

I have this script, which works perfectly (although I'm sure there's a better way to do this).

function dataTransfer() {
   var value1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BACKLOG').getRange('G18').getValue();
   var value2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BACKLOG').getRange('AF8').getValue();
   var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AM COMP');
   var cell1 = sheet2.getRange('D8');
   var cell2 = sheet2.getRange('D18');
   cell1.setValue(value1);
   cell2.setValue(value2);

}

This code reads cells for data and copies that data to another sheet when I press a button I created. What I need is for the cells that I'm getting data from and putting data into to change (some by an increment of 2 rows over, some by 1) each time I run the script.

For example: in January I press the button and the value in G18 (BACKLOG) is copied to D8 (AM COMP), the value in AF8 (BACKLOG) gets copied to D18 (AM UPSELL). But in February I would like to push the button again but this time have the value from I18 (BACKLOG) copied to E8 (AM COMP), AF8 (BACKLOG) stays the same but gets copied to E18 (AM COMP). For March they would need to move again, etc.

What is the best way to accomplish this?

Upvotes: 2

Views: 81

Answers (2)

Cooper
Cooper

Reputation: 64032

Monthly Data Transfers

All of your settings for each month are neatly stashed away in the sheet named 'DATA'.

function dataTransfer() {
  var ss=SpreadsheetApp.getActive();
  var dataSh=ss.getSheetByName('DATA');
  var m=new Date().getMonth()+1;//The + 1 is to get past the header row
  var values=dataSh.getRange(1,1,dataSh.getLastRow(),dataSh.getLastColumn()).getValues();
  var hdrA=values[0];
  var valA=values[m];
  var vObj={};
  for(var i=0;i<valA.length;i++){
    vObj[hdrA[i]]=valA[i];//the hdrA is the key and the valA is the value
  }
  ss.getSheetByName(vObj.SH).getRange(vObj.RG1).setValue(ss.getSheetByName(vObj.valSH1).getRange(vObj.valRG1).getValue());
  ss.getSheetByName(vObj.SH).getRange(vObj.RG2).setValue(ss.getSheetByName(vObj.valSH2).getRange(vObj.valRG2).getValue());
  Logger.log('SH: %s,RG1: %s,valSH1: %s,valRG1: %s,RG2:%s,valSH2: %s,valRG2: %s,MONTH: %s',vObj.SH,vObj.RG1,vObj.valSH1,vObj.valRG1,vObj.RG2,vObj.valSH2,vObj.valRG2,vObj.MONTH);
}

This is what the 'DATA' sheet looks like:

enter image description here

Of course I just filled the table in with the data that you provided for one month and I added the month column. You can add more columns and then give them a name in the header row and that will become the key for extracting in out of vObj.

Upvotes: 1

Alan Wells
Alan Wells

Reputation: 31300

This might not be a complete solution, but it should give you an idea of what needs to be done. You need to fill in the values for the rest of the months in the object literal.

function dataTransfer() {
  var addr_2_ToGet,cellAddressToGet,cellAddressToSet,
      currentMonthNumber,d,innerObj,objectMap,sourceSheet,ss;

  d = new Date();//Get the current date

  currentMonthNumber = d.getMonth();//Get a number which represents 
    //the current month - 0 = January
  Logger.log('currentMonthNumber: ' + currentMonthNumber)

  objectMap = {
    0:{cellToGet:'G18',cellTwoToGet:'',cellToSet:'D8',cellTwoToSet:''},
    1:{cellToGet:'I18',cellToSet:'E8'},
    2:{cellToGet:'',cellToSet:''},
    3:{cellToGet:'',cellToSet:''},
    4:{cellToGet:'',cellToSet:''},
    5:{cellToGet:'',cellToSet:''},
    6:{cellToGet:'',cellToSet:''},
    7:{cellToGet:'',cellToSet:''},
    8:{cellToGet:'',cellToSet:''},
    9:{cellToGet:'',cellToSet:''},
    10:{cellToGet:'',cellToSet:''},
    11:{cellToGet:'',cellToSet:''}    
  }

  innerObj = objectMap[currentMonthNumber]
  Logger.log('innerObj: ' + innerObj)

  cellAddressToGet = innerObj.cellToGet;
  Logger.log('cellAddressToGet: ' + cellAddressToGet)
  addr_2_ToGet = innerObj.cellTwoToGet;

  cellAddressToSet = innerObj.cellToSet;
  Logger.log('cellAddressToSet: ' + cellAddressToSet)

  addr_2_To_Set = innerObj.cellTwoToSet;
  Logger.log('addr_2_To_Set: ' + addr_2_To_Set)

  ss = SpreadsheetApp.getActiveSpreadsheet();
  sourceSheet = ss.getSheetByName('BACKLOG');

  var value1 = sourceSheet.getRange(cellAddressToGet).getValue();
  var value2 = sourceSheet.getRange(addr_2_ToGet).getValue();

  var sheet2 = ss.getSheetByName('AM COMP');

  var cell1 = sheet2.getRange(cellAddressToSet);
  var cell2 = sheet2.getRange(addr_2_To_Set );

  cell1.setValue(value1);
  cell2.setValue(value2);

}

Upvotes: 1

Related Questions