Daily record log to different spreadsheet

Question

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

Answer 1

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 page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.