使用 Power Automate 动态更改 Excel 工作表日期的 Office 脚本

Question

我正在使用以下 Office 脚本在工作日结束时隐藏每日 Excel 工作表。当从 Excel 中的脚本运行时，它运行良好，但是，当在一天结束时使用 EST 23:00 触发 Power Automate 时，它​​会失败。我相信这是由于 Power Automate 在一天结束时使用的时区与我所在的时区存在差异，当通过 Power Automate 在一天结束时触发此操作时，是否有某种方法可以定义正确的时区？

function main(workbook: ExcelScript.Workbook) {
  //Assign the "Template" worksheet to the ws variable
  let date = new Date(Date.now());
  let ws = workbook.getWorksheet(`${date.toDateString()}`);

  //Set the visibility of the ws worksheet to hidden
  ws.setVisibility(ExcelScript.SheetVisibility.hidden);
}

Answer 1

对于云来说，时区是很棘手的。您无法 100% 确定您的代码最终将在哪个时区运行。这实际上取决于云计算的数据中心位于何处及其配置方式。

当您需要将日期时间对象转换为字符串时，区域设置（文化）会让事情变得更糟。转换后的输出可能因文化而异。

在处理日期和时间时，您必须始终明确。

如果您按照问题中提到的时区信息（23:00 EST）明确配置了 Power Automate 流程，我认为您的 Power Automate 流程可能会在正确的时刻触发。

因此，我猜您代码中的以下两个 API 调用可能是罪魁祸首：

• Date.now() 返回该地区的当前日期时间（可能与您居住的地区不同）运行脚本的数据中心的名称。

• date。 toDateString() 返回日期对象的日期部分的文本表示形式，采用运行脚本的数据中心区域的当前区域设置（可能与您居住的区域不同）。 p>

您想要做的是获取当前 EST 时间的日期部分，我想知道这是否有帮助：

// You can find the time zone names from https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
function currentDateStringInTimeZone(timeZone: string, locale: string = "en-US"): string {
  return new Date().toLocaleDateString(
    locale,
    {
      timeZone: timeZone
    });
}

function main(workbook: ExcelScript.Workbook) {
  //Assign the "Template" worksheet to the ws variable
  let ws = workbook.getWorksheet(`${currentDateStringInTimeZone("America/New_York")}`);

  //Set the visibility of the ws worksheet to hidden
  ws.setVisibility(ExcelScript.SheetVisibility.hidden);
}

您需要传入预期的时区（您可以从以下位置找到所有时区名称） <一href="https://en.wikipedia.org/wiki/List_of_tz_database_time_zones" rel="nofollow noreferrer">https://en.wikipedia.org/wiki/List_of_tz_database_time_zones）和预期的区域设置（默认情况下 <代码>en-US）。默认情况下，America/New_York 和 en-US 的日期字符串采用 2/25/2022 格式。如果它与您的工作表名称的格式不匹配，您可能需要调整 toLocaleDateString()。

Answer 2

如果是我，我会从 PowerAutomate 步骤确定日期并将其用作参数，而不是直接从 Office 脚本执行此操作。

注意： 这假设您始终从 PowerAutomate 中触发此操作，并且用户不会从直接 Excel Online。

作为一个非常基本的示例，这就是我的脚本的样子......

function main(workbook: ExcelScript.Workbook, localDateTime: string) {
  let worksheet = workbook.getActiveWorksheet();
  worksheet.getRange("A1").setValue(localDateTime);
}

您可以看到，我有一个 localDateTime 参数，然后可以填充该参数通过 PowerAutomate 流程。

在流程本身中，我让它看起来像这样...

Local Date Time 变量表达式如下所示...

formatDateTime(convertFromUtc(utcNow(), 'AUS Eastern Standard Time'), 'yyyy-MM-dd hh:mm')

该表达式为我提供了完全符合我想要的日期和时间它在我的时区。为了演示的目的，我已经精确到小时和分钟以显示其真正的准确性，正如我希望实现的那样，这就是结果......

忽略地址中的 AM酒吧，它正在处理字符串根据收到的内容将单元格作为日期/时间，但您不会这样做，您只会使用日期来显示/隐藏工作表。

如果需要将其存储在单元格中，那么我必须使字符串更严格一些，并包含 AM/PM 或使其为 24 小时之类的。

因此，如果您在 PowerAutomate 中的表达式是这样的......

formatDateTime(convertFromUtc(utcNow(), 'Eastern Standard Time'), 'yyyy-MM-dd')

或者您的工作表名称的任何 yyyy-MM-dd 格式，那么它应该按照您的需要工作。