0

I would like to be able to log the last value with yesterdays timestamp to a completely different Google sheet, but am a bit stomped by the complexity of this.

I have a sheet that logs "daily_gains" (column U) values at random intervals. The time/date (column B) and daily gains column look something like this;

    
|date/time (B)      |daily_gains (U) |
|:------------------|---------------:|
|12 Aug 2021, 06:16 |                |
|12 Aug 2021, 06:05 |                |
|12 Aug 2021, 05:55 |                |
|12 Aug 2021, 05:50 |                |
|12 Aug 2021, 04:30 |          2.225 |
|12 Aug 2021, 04:25 |                |
|12 Aug 2021, 04:15 |                |
|12 Aug 2021, 04:05 |                |
|12 Aug 2021, 04:00 |                |
|12 Aug 2021, 03:55 |                |
|12 Aug 2021, 03:20 |         -1.129 |
|12 Aug 2021, 03:10 |                |
|12 Aug 2021, 03:00 |                |
|12 Aug 2021, 02:35 |                |
|12 Aug 2021, 02:30 |                |
|12 Aug 2021, 02:25 |                |
|11 Aug 2021, 20:30 |         -3.306 |
|11 Aug 2021, 20:25 |                |
|11 Aug 2021, 20:10 |                |
|11 Aug 2021, 19:30 |                |
|11 Aug 2021, 18:40 |                |
|11 Aug 2021, 18:30 |                |
|11 Aug 2021, 15:45 |         -2.819 |
|11 Aug 2021, 15:41 |                |

What I am trying to do is: Everyday, sometime after midnight, I would like to copy the last daily_gains value from the previous day to a different spreadsheet.

In this case, on 12 Aug 2021, 00:10, I would like to copy the value -3.306 to a new spreadsheet.

I have found this thread: Daily calculation and record insertion in Google Spreadsheet which helps some explaining how to use apps script, but it copies from a specific cell to a new sheet in the same document. And my coding knowledge doesn't quite let me extrapolate my specific use case from that.

edit: This spreadsheet shows the data I have and what I am trying to achieve: https://docs.google.com/spreadsheets/d/1fglFHDZOxOfiipMjB9sXWpdzCdgvhZ_PYIrdfPpfL98/edit?usp=sharing

1
0

It is unclear why you would need to use a script for this, as the report you are asking for can more easily be created with a plain vanilla spreadsheet formula, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      sequence( 
        max(RawData!C2:C) - min(RawData!C2:C), 
        1, 
        min(RawData!C2:C) 
      ), 
      filter( 
        { RawData!C2:C, RawData!G2:G }, 
        RawData!C2:C < today(), 
        not(isblank(RawData!G2:G)) 
      ), 
      { 1, 2 }, 
      false 
    ) 
  ) 
)

See the new 'Solution - arrayformula' sheet in your sample spreadsheet. The first column is formatted as Format > Number > Date.

If you absolutely need to use a script for some reason, you can Insert > New sheet, name the new sheet Gains yesterday and use this formula in cell A2:

=arrayformula( 
  iferror( 
    vlookup( 
      today() - 1, 
      filter( 
        { RawData!C2:C, RawData!G2:G }, 
        RawData!C2:C < today(), 
        not(isblank(RawData!G2:G)) 
      ), 
      { 1, 2 }, 
      false 
    ) 
  ) 
)

Then use the appendValuesToArchiveSheet script with these parameters:

// [START modifiable parameters]
var rangeToLog = 'Gains yesterday!A2:B2';
var sheetToLogTo = 'Archive';
// [END modifiable parameters]

The script will write the values to an 'Archive' sheet in the same spreadsheet file. To automatically copy the log to another spreadsheet, use importrange(), or insert a new line right before the var logSheet like like this:

  // ...
  ss = SpreadsheetApp.openById('...ID...');
  var logSheet = ss.getSheetByName(sheetToLogTo);
  // ...

Replace the ...ID... bit with the spreadsheet ID of the target spreadsheet. You can get it from the browser address bar when you have the spreadsheet open. It is the part between /d/ and /edit.

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets pagejavascript.infoMozilla Developer Network and Apps Script at Stack Overflow.

5
  • the last daily_gains entry of a particular day wont be in the same cell every day. the columns are updated in irregular intervals and it wont be possible to predict how many "empty" entries there are from the last daily_gains value to midnight. And also how many entries it already generated after midnight until the script runs. Your script is great though, but is there a way to pinpoint the last entry with a value before midnight?
    – Bravewart
    Aug 12 at 17:29
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Aug 12 at 19:09
  • Of course - could have thought of that. I have added a link to an example spreadsheet in the question
    – Bravewart
    Aug 13 at 6:56
  • Edited the answer to add an easier solution. Aug 13 at 10:41
  • wow. Still going through your formula - a lot in there I haven't seen before. But it does exactly what I need it to do. thank you
    – Bravewart
    Aug 13 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.