时间戳

首页 > 解决方案 > 如何跟踪单元格中值随时间的变化。自引用挑战

google-sheets - 如何跟踪单元格中值随时间的变化。自引用挑战

问题描述

我有一个电子表格，我必须在其中跟踪包含整数值的单元格的每日更改，并计算今天新获取的数据与昨天数据之间的差异。我不需要跟踪超过过去 1 天的差异。

定义：

我有 2 张纸的谷歌电子表格：

  1. “SheetA”显示更新
  2. "SheetB" 使用 =IMPORTDATA(url) 自动导入今天的数据

“SheetB”中每日导入数据的数据结构如下：

| 关键| 价值 |
| 美国广播公司 | 100 |
| 防御工事 | 123 |
| ... | ... |

数据每天在欧洲中部时间 00:00 更新 - 这意味着每天早上打开电子表格时，它会使用新数据自动更新“SheetB”中的值，并且我会丢失前一天的数据。

“SheetA”结构

| 关键 | 昨天的值| 今日价值| 区别|
| 美国广播公司 | 100 | 107 | (+或-) 7 |
| ... | .... | .... | ... |

“SheetA”必须显示相对于前一天的每日变化。如果从“SheetB”中新导入的数据显示增加7，则在“差异”列中应显示+7。差异可以是负数或正数

如何在不丢失前一天数据的情况下计算“SheetA”中的差异？我不需要保留整个历史。我只需要今天价值和昨天价值的差异。我可以随意修改表格。我无法更改源数据，这是使用 =IMPORTDATA 导入的 csv

问题是我应该在计算差异后更新昨天的数据。但如果没有昨天的数据，我无法计算差异。

先感谢您 ：）

标签： google-sheetsself-reference

解决方案

您可以使用另一张表来存储昨天的数据。

所以 - 为了做到这一点，您可以利用 Apps Script 的时间驱动触发器。

代码

// 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();
}

解释

上面的代码由两个函数组成：

  • importYesterday-用于将数据从 TODAY 工作表导入到 YESTERDAY.

由于IMPORTDATA在午夜重新计算，因此这里选择的导入数据的方法使用getRange和getValues获取数据，使用和将数据粘贴到工作表中。getRangesetValuesYESTERDAY

请注意，上述方法独立于IMPORTDATA' 的重新计算 - 因此，一旦您在工作YESTERDAY表中拥有所有数据，它将保留在那里，除非您专门触发其更改。

因此，为了始终获取昨天的数据，createTrigger创建了该函数。

  • createTrigger-用于触发函数的执行 importYesterday 。

这是通过使用atHour(23)方法完成的，这意味着触发器将在大约 23 小时运行。

YESTERDAY所以本质上，数据在重新计算之前就被导入到工作表中IMPORTDATA。

下一步

至于主工作表中的其他计算，您只需引用TODAY和工作YESTERDAY表中的单元格。

如果您还想根据其他值更新昨天的数据，您可以简单地修改importYesterday函数以满足您的需要和/或甚至创建另一个专门用于更新值的函数。

笔记

您还必须调整getRange使用的方法importYesterday以匹配您拥有的数据范围。

参考

推荐阅读