Snapshot data and append at a specific location in Google Sheets

Question

I have a Google Sheet I use to track data. I have a sheet that pulls data from multiple sheets in a single row. The row has the current date for Column B and then pulls in data for columns C through AC. I am trying to create a mechanism to snapshot that data and put it on the next line below it. I want the ability to continue doing this and keep pushing the data down and dropping the current on the next line. This allows me to select data in column A to use for graphing purposes. This is what I was using:

  function recordHistory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("History");
    var source = sheet.getRange("B2:AC2");
    var values = source.getValues();
    var now = new Date();
    values[0][0] = now;
    for (var col in values) {
      sheet.getRange(sheet.getLastRow(),2,1,28).setValues(values[col]);
    }

I used a combination of examples and I think I got my wires crossed with the translation from one to the other. Looking for help to clean this up or point me to a better option. I was originally using appendRow, but that limits me to using the first column. I want the ability to have the snapshot placed in the 2nd column and the corresponding columns after it. Hopefully, that makes sense.

In this sheet, you can see I am pulling data from the first 2 sheets into the last sheet. I am skipping the first column and using Row 2 as the exact values. The script above is supposed to take what is in Row 2, snapshot it as values only, and move the data to Row 3, moving the previous rows down. This provides me a history of the values. I will be using the triggers to run this function every night at midnight, so the data will be a daily capture of the values. Hopefully, this makes it a bit more clear.

EDIT 2: Let me try and simplify the explanation. I have a sheet that has data in cells B2 through AC2. I want to grab that data and copy it to cells B3 through AC3, moving the data down a row. So on the sheet, you should see cells B3:AC3 having yesterdays data. B4:AC4 has the day before. B5:AC5 has the day before that. Basically keeping a log of the data that is captured in B2:AC2 each day.

Is it clearer what I am trying to accomplish or should I explain it further? I really want to get this script corrected so I can schedule it to run over the weekend.

I have to apologize for my poor English skill. Unfortunately, I cannot understand about your goal. In order to correctly understand about your goal, can you provide the sample Spreadsheet including the input and output you expect? By this, I would like to correctly see the vision of your goal and think of the solution. Of course, please remove your personal information. — Tanaike
I am sorry, it is hard to understand your purpose. Maybe you can try to rephrase it or provide a picture of what your data looks like now and what is your expected outcome. — ziganotschka

Chris Moretti Chris Moretti · Accepted Answer · 2020-01-24T19:17:34

After a few hours of playing with syntax a bit and realizing where my mistake was, I noticed some issues with the way I was capturing the data and trying to apply it to a range. Here is the solution to my problem:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("B2:AC2");
  var values = source.getValues();
  sheet.insertRowBefore(3);
  sheet.getRange(3, 2, 1, 28).setValues([values[0]]);
};

As you can see in the solution, I realized how the data was being stored in the array and matched it to the setValues part of the script. It is a pretty basic issue I was having, but the use case was difficult to explain. The insertRowBefore was also a vital piece to establish the structure of the sheet.

Snapshot data and append at a specific location in Google Sheets

1 Answers