How Do I Archive Data in a Cell Daily

Ask Anything
1

I have a me-made sheet in my Tiller template. I set it up to automatically copy over one number from my Budget sheet. Then every day, I copy that number down to another cell (one with a date) and then add it to another number that changes monthly. Is there a way to automate this?

@randy This is the example (the numbers are compete dummy numbers, by the way) I used - sheets stuff I can’t figure out. :slight_smile: :upside_down_face:

Screenshot 2020-07-24 at 3.38.28 PM
Screenshot 2020-07-24 at 3.38.28 PM1148×598 72.4 KB

4

Good question, @susandennis!

I don’t think what you want to do is possible without scripts. In-sheet formulas are great at moving and manipulating live data, but they aren’t good at pulling in live data and "freezing it "as values.

But, don’t be afraid, because scripts can be just as fun and easy as in-sheet formulas and they are more powerful.

Here is a sample script that should work in your application with some small modifications for cell references & sheet names (it looks longer and denser than it is because of all the comments which begin with //— there are only 8 lines of code doing work):

function moveTodaysValue() {
  // get the sheet resource by sheet name
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Susan');
  // get all values from the sheet resouce
  var values = sheet.getDataRange().getValues();
  // get the value in A2 ([1] is the second row, [0] is the first column A -- it is all zero based)
  var value = values[1][0];

  // get today's date but strip off the time component so it matches the in-cell dates
  var todayDate = new Date();
  todayDate.setHours(0,0,0,0);
  
  // start in row 11 (zero based) and loop until the last row in the values array
  for(var row = 10; row < values.length; row++) {
    // if the date in the current row ("row") in column [0]/A matches our "todayDate" then...
    if(new Date(values[row][0]).getTime() == todayDate.getTime()) {
      // set the range to write to to "row+1" (getRange() is NOT zero based) in column [3]/D and set the cell value to "value" from early in this program
      sheet.getRange(row + 1, 3).setValue(value);
      // stop running the for loop... we've done our work... no need to continue to the bottom
      break;
    }
  }
}

To get started with this function…

  • Open your sheet then go to Tools / Script Editor.
  • Paste the function above into the script file (you can overwrite the empty starter function).
  • Check that the referenced sheet names and cell locations in the script are correct.
  • Save the script file.
  • Select the function name (“moveTodaysValue”) in the function selector to the left of the lightbulb.
  • Press the play button. (You will be asked to authorize the script to run the first time.)
  • Check that the value moved from A2 into the row matching today’s date.

Once you make sense of this all, we can add a trigger so it runs nightly.

1 Like
5

Ok so this is going to take some study. What is clearly simple to you is quite strange to me but you have given me much to learn. I really thank you for all the comments this will help tremendously. I found some tutorials on scripts in Google sheets that will be my starter.

Thanks so much for doing this!

6

I know it probably seems overwhelming but I’d start by trying to run it using the steps above, see what happens, then just make small adjustments to sheet names and indices… baby steps. You’ll understand it quicker than you think.

Let me know if you have any questions.

7

I will do! Thanks so much.