Each month, populate spreadsheet data to next column [script needed]

Question

I am developing a "Dashboard" [Sample Sheet Here] in which a number of data points are automatically calculated using formulas in Column C (NOTE: formulas not included in sample sheet).

I would like to create a monthly log of Column C data, in which cell values are copied to the next blank column of the corresponding Row

I have previously used the following script to log changes vertically, and could use a hand with repurposing for my desired outcome.

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = event.source.getActiveSheet();

  var r = event.source.getActiveRange();

  if(s.getName() == "Action Items" && r.getColumn() == 2 && r.getValue()) {

    var row = r.getRow();

    var numColumns = s.getLastColumn();

    var targetSheet = ss.getSheetByName("Portion Tracking");

    if(targetSheet.getLastRow() == targetSheet.getMaxRows()) {
      targetSheet.insertRowAfter(targetSheet.getLastRow());
    }

    //Changes Start Here
    var myRow = targetSheet.getLastRow()+1;

    s.getRange(row, 1).copyTo(targetSheet.getRange(myRow,1));
    s.getRange(row, 2).copyTo(targetSheet.getRange(myRow,2));
    s.getRange(row, 3).copyTo(targetSheet.getRange(myRow,3));
  }
}

Cooper · Answer 1 · 2017-08-31T20:12:42.657

AppendColumn()

I played around with this idea once and using a function I took from here and some of my own carving. I've comeup with an appendColumn() function.

Anyway it sounded like something you might want to have. You can take a look at it and in the meantime I'll take a look at your code.

function appendColumn(columnA) 
{
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getActiveSheet();
    var rg=sh.getDataRange();
    var vA=rg.getValues();
    var vB=transpose(vA);
    sh.clear();
    sh.getRange(1,1,vB.length,vB[0].length).setValues(vB);
    sh.appendRow(columnA);
    vD=sh.getDataRange().getValues();
    sh.clear();
    vE=transpose(vD);
    sh.getRange(1,1,vE.length,vE[0].length).setValues(vE);;
}

function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

To test it I just called it like this.

function testAppendColumn()
{
  appendColumn(['',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]); 
}

I'd like to see the source data. But assuming it has not changed then I'm guessing that this technique will work for you.

function onEdit(event) 
{
  var ss = event.source;
  var s = event.source.getActiveSheet();
  var r = event.range;
  if(s.getName() == "Action Items" && r.getColumn() == 2 && r.getValue()) 
  {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Portion Tracking");
    if(targetSheet.getLastRow() == targetSheet.getMaxRows()) 
    {
      targetSheet.insertRowAfter(targetSheet.getLastRow());
    }
    //Changes Start Here
    var myCol = targetSheet.getLastColumn()+1;
    s.getRange(row, 1).copyTo(targetSheet.getRange(1,myCol));
    s.getRange(row, 2).copyTo(targetSheet.getRange(2,myCol));
    s.getRange(row, 3).copyTo(targetSheet.getRange(3,myCol));
  }
}

I don't understand the logic of connecting this to onEdit trigger but I'll assume that you do.

Each month, populate spreadsheet data to next column [script needed]

1 Answers1

AppendColumn()