Google Sheets – Automatically Record Daily Values in New Column

formulasgoogle sheetsgoogle-apps-script

So I have this spreadsheet I use to control Content funnel.
I've come up with a few formulas to use something else than a pivot table.
But in order to get the trends and alarms I'm looking for, I need to start recording this data daily.. automatically.
This is the spreadsheet:

enter image description here

I'm looking for a script that would add a column between columns B and C, setting current date in cell C1, and completing each cell below with the corresponding value to each formula in each cell.

Here's the list of forumlas I'm using:

C2: =counta(Check!$J:$J)
C3: =COUNTIF(Check!$L:$L,Validation!$A$6)
C4: =COUNTIF(Check!$L:$L,Validation!$A$7)
C5: =ARRAYFORMULA(countif(Check!$L:$L&Check!$O:$O,Validation!$A$5&"") )
C6: =countif(Check!$O:$O,Validation!$A$1)
C7: =ARRAYFORMULA(countif(Check!L:L&Check!O:O,Validation!A5&Validation!A2) )
C8: =ARRAYFORMULA(countif(Check!O:O&Check!Q:Q,Validation!A1&"") )
C9: =countif(Check!Q:Q,Validation!A1)
C10: =countif(Check!Q:Q,Validation!A2)
C11: =countif(Check!T:T,Validation!A1)

So far I've only achieved this much:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Approval Funnel");
  var source = sheet.getRange("C2:C11");
  var values = source.getValues();
  values[0][0] = new Date();
  sheet.insertColumns(3);

And that's the frontier of my coding knowledge.
Any ideas?

Best Answer

Your draft is pretty good, but there is a design flaw: if you insert the values between B and C, then the column with formulas will become D. So, next time the script will try to get data, it will be looking at a wrong place.

Simply put, the source of the data you are recording (i.e., the column with formulas) should stay in the same place. You can put the historical data to the right of it. Like this: 

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Approval Funnel");
  var source = sheet.getRange("C2:C11");
  var values = source.getValues();
  values = [[new Date()]].concat(values);    // prepending the date to values
  sheet.insertColumnAfter(3);                // inserting AFTER column C
  SpreadsheetApp.flush();         
  sheet.getRange("D1:D11").setValues(values);
}

I put SpreadsheetApp.flush(); to make sure that the previous changes (namely, inserting a column) is indeed made before the script puts the data in with setValues.

Also you had an error in values[0][0] = new Date(); -- this command would overwrite the 0th element of array (namely, the content of C2) with the date. You wanted to prepend the date, which is what I did by creating a new array with one element, [[new Date()]], and concatenating values to it.

Related Topic