CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1205901 - Слава Україні
user1205901 - Слава Україні

Reputation: 939

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

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.

Upvotes: 2

Views: 9528

Answers (2)

Chad Pittman
Chad Pittman

Reputation: 1

If you're not comfortable with Google Apps Script, you can use the Append feature of the Google Sheet add-on Sheetgo (https://www.sheetgo.com/append-google-sheets/). To configure:

  1. Install Sheetgo
  2. Open the add-on (Add-ons -> Start -> Sheetgo)
  3. Mousover the + button and down arrow for an Import Connection
  4. Select the connection type 'Append'
  5. Select the source spreadsheet and source tab from that spreadsheet
  6. Select the frequency (in your case would be daily)
  7. Click 'Connect'

The add on will create a new sheet with the data from the source sheet and automatically append a new record daily.

*disclaimer - I'm a co-founder of Sheetgo

Upvotes: 0

Niccolo
Niccolo

Reputation: 815

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.

Upvotes: 6

Related Questions