How can I record the contents of a Google Sheets cell at the end of each day?

Question

In Google Sheets I'm collecting data dynamically using the IMPORTHTML function, and one element from that data set is currently copied in cell C1.

In another sheet I have a list of dates from now until 2017 as column A.

At the end of each day, I'd like to record the final C1 score in column B next to the relevant date.

How would I go about doing that in an automated way? That is, I want the spreadsheet to complete itself at the end of each day.

Niccolo Niccolo · Accepted Answer · 2014-08-13T17:07:47

You need a script that copies the entry from sheet1!C1 into sheet2!columnB row(where columnA=today()).

Replace sheet1, sheet2 with your sheet names.

Setup spreadsheet

Within your spreadsheet goto

File>spreadsheet settings

Set your local and time zone, then set the recalculation onChange and every hour.

Use inbuilt function to locate today's row

goto sheet2!

I am assuming your columnA has the dates running from A1 to A# in date format. We are going to need a cell to hold a value, I am choosing C1. in C1 put this

=MATCH(today(),A:A)

This will give you a number, it should be the row holding today's date.

Build the script

Then goto

tools>script editor

create a script for spreadsheets. This will give you some sample script, delete this.

code is

function scorekeeper(){
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet2");
  var score = sheet1.getRange("C1").getValue(); // we want to store this
  var row   = sheet2.getRange("C1").getValue(); // this is our row
sheet2.getRange(row, 2).setValue(score );
}

You can test this by going to Run and selecting the function name. You may have to save the script first and give the app permission.

Setting the trigger

Let's make this run at midnight

goto

Resources>Current project's triggers.

Add a trigger.

Select; scorekeeper() Time-driven Day timer Midnight to 1:00 a.m.

And you are good to go.