Search code examples
google-sheetsself-reference

How to keep track of changes over time of a value in a cell. Self referencing challenge

I've a spreadsheet where I've to track daily changes to a cell containing an integer value and calculate the difference between today's newly fetched data and yesterday's data. I do not need to track difference for more than the last 1 day.

Definition:

I've a google spreadsheet with 2 sheets:

  1. "SheetA" display updates
  2. "SheetB" today's data automatically imported with =IMPORTDATA(url)

The data structure for daily imported data in "SheetB" looks like this:

| key | value |
| ABC | 100 |
| DEF | 123 |
| ... | ... |

Data is updated every day at 00:00 CET - This means that every morning opening the spreadsheet it automatically updates values in "SheetB" with fresh data and i lose the data from the day before.

"SheetA" structure

| key | yesterday value | today value | difference |
| ABC | 100 | 107 | (+or-) 7 |
| ... | .... | .... | ... |

"SheetA" has to display the daily change in respect to the previous day. If newly imported data from "SheetB" show an increase of 7, it should display +7 in column "difference". The difference can be negative or positive

How do i calculate the difference in "SheetA" without losing data from the day before? I do not need to keep the whole history. I only need the difference from today's value and yesterday's value. I can modify the sheet however i like. I cannot change the source data, which is a csv imported with =IMPORTDATA

The problem is that i should update yesterday's data after calculating the difference. But i cannot calculate the difference without yesterday's data.

Thank you in advance :)

Solution

  • You could use another sheet where you will store yesterday's data.

    So - in order to do this, you can make use of Apps Script's time-driven triggers.

    Code

    // Copyright 2020 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function importYesterday() {
  let spreadsheet = SpreadsheetApp.openById("SPREADSHEET_ID");
  let yesterdaySheet = spreadsheet.getSheetByName("YESTERDAY");
  let todaySheet = spreadsheet.getSheetByName("TODAY");
  let data = todaySheet.getRange(ROW, COL, NUM_ROWS, NUM_COLS).getValues();
  yesterdaySheet.getRange(ROW, COL, NUM_ROWS, NUM_COLS).setValues(data);
}


function createTrigger() {
  ScriptApp.newTrigger('importYesterday')
      .timeBased()
      .atHour(23)
      .create();
}

    Explanation

    The code above is composed of two functions:

    • importYesterday - which is used to import the data from TODAY sheet to YESTERDAY.

    Since the IMPORTDATA recalculates at midnight, the method here chosen to import the data makes use of the getRange and getValues to get the data and of the getRange and setValues to paste the data into the YESTERDAY sheet.

    Note that this method above is independent of the IMPORTDATA's recalculation - so once you have all the data in the YESTERDAY sheet, it will remain there, unless you specifically trigger its change.

    So, in order to always get the data from yesterday, the createTrigger function was created.

    • createTrigger - which is used to trigger the execution of importYesterday function.

    This is done by using the atHour(23) method which means that the trigger will run approximately at hour 23.

    So essentially, the data is imported into the YESTERDAY sheet right before the IMPORTDATA recalculates.

    Next Steps

    As for the other calculations in the main sheet, you will simply have to reference the cells from the TODAY and YESTERDAY sheet.

    If you also want to update yesterday's data depending on the other values, you can simply modify the importYesterday function to match your needs and/or even create another function specifically for updating the values.

    Note

    You will also have to adapt the getRange methods used in importYesterday in order to match the range of the data you have.

    Reference