Это пример сценария для копирования объекта даты между таблицами Google с разными часовыми поясами с помощью сценария Google Apps.

Однажды у вас может возникнуть ситуация, когда потребуется скопировать объект даты между таблицами Google с разными часовыми поясами с помощью скрипта Google Apps. В этом посте я хотел бы представить примеры сценариев для достижения этой цели.

Пример ситуации

Здесь объявлен пример ситуации. Чтобы протестировать приведенные ниже сценарии, создайте 2 таблицы Google.

  1. Заголовок: «Таблица A», часовой пояс «Азия/Токио». В качестве образца объекта даты введите значение «2023/01/01 12:00:00» в ячейку «A1» на вкладке 1-го листа.
  2. Название: «Таблица B», часовой пояс «Америка/Лос-Анджелес» (тихоокеанское время)

Разница во времени между «Азией/Токио» и «Америкой/Лос-Анджелесом» составляет 17 часов.

В этом отчете предполагается ситуация, когда значение ячейки «A1» 2023/01/01 12:00:00 из «Таблицы A» в «Таблицу B».

Пример сценария 1

Прежде чем тестировать этот сценарий, установите идентификаторы электронных таблиц.

В этом сценарии объект даты 2023/01/01 12:00:00 копируется путем изменения часового пояса. Итак, 2023/01/01 12:00:00 копируется как 2022/12/31 19:00:00 с учетом разницы во времени.

function sample1() {
  const spreadsheetIdA = "### Spreadsheet ID of Spreadsheet A ###"; // Asia/Tokyo
  const spreadsheetIdB = "### Spreadsheet ID of Spreadsheet B ###"; // America/Los_Angeles

  const [ss1, ss2] = [spreadsheetIdA, spreadsheetIdB].map((id) =>
    SpreadsheetApp.openById(id)
  );
  const range = ss1.getSheets()[0].getRange("A1");
  const numberFormat = range.getNumberFormat();
  const dateObject = range.getValue();
  ss2
    .getSheets()[0]
    .getRange("!A1")
    .setNumberFormat(numberFormat)
    .setValue(dateObject);
}

При запуске этого сценария значение 2023/01/01 12:00:00 из «A1» 1-й вкладки в электронной таблице A копируется в «A1» 1-й вкладки в электронной таблице B. Когда значение 2023/01/01 12:00:00 копируется из таблицы A в таблицу B, значение в таблице B становится 2022/12/31 19:00:00 из-за разницы во времени в 17 часов между «Азией/Токио» и «Америкой/Лос-Анджелесом».

Если вы хотите скопировать объект даты между таблицами A и B с учетом разницы во времени, можно использовать этот сценарий. Но если вы хотите скопировать объект даты между таблицами A и B, игнорируя разницу во времени, например, когда вы хотите скопировать объект даты 2023/01/01 12:00:00 между таблицами A и B как одно и то же значение, этот сценарий может не работать. подходящий. В этом случае можно использовать следующие примеры сценариев.

Пример сценария 2

Прежде чем тестировать этот сценарий, установите идентификаторы электронных таблиц.

Если числовой формат объекта даты является числовым форматом, который может автоматически определять объект даты по строковому типу, можно использовать следующий сценарий.

В этом сценарии объект даты 2023/01/01 12:00:00 копируется без учета разницы во времени. Итак, 2023/01/01 12:00:00 копируется как 2023/01/01 12:00:00.

function sample2() {
  const spreadsheetIdA = "### Spreadsheet ID of Spreadsheet A ###"; // Asia/Tokyo
  const spreadsheetIdB = "### Spreadsheet ID of Spreadsheet B ###"; // America/Los_Angeles

  const [ss1, ss2] = [spreadsheetId1, spreadsheetId2].map((id) =>
    SpreadsheetApp.openById(id)
  );
  const range = ss1.getSheets()[0].getRange("A1");
  const numberFormat = range.getNumberFormat();
  const stringValue = range.getDisplayValue();
  ss2
    .getSheets()[0]
    .getRange("!A1")
    .setNumberFormat(numberFormat)
    .setValue(stringValue);
}

В этом сценарии значение ячейки 2023/01/01 12:00:00 извлекается как строковое значение с getDisplayValue(), а 2023/01/01 12:00:00 строкового значения помещается в «A1» 1-й вкладки в электронной таблице B. В случае 2023/01/01 12:00:00 строкового значения, когда оно помещается в ячейку, он автоматически преобразуется в объект даты.

Однако бывают случаи, когда используется числовой формат, который невозможно автоматически преобразовать в объект даты. В таком случае, к сожалению, этот метод использовать нельзя. Итак, я хотел бы представить следующие сценарии.

Пример сценария 3

Прежде чем тестировать этот сценарий, установите идентификаторы электронных таблиц.

В этом примере цель достигается путем изменения часового пояса электронной таблицы.

В этом сценарии объект даты 2023/01/01 12:00:00 копируется без учета разницы во времени. Итак, 2023/01/01 12:00:00 копируется как 2023/01/01 12:00:00.

function sample3() {
  const spreadsheetIdA = "### Spreadsheet ID of Spreadsheet A ###"; // Asia/Tokyo
  const spreadsheetIdB = "### Spreadsheet ID of Spreadsheet B ###"; // America/Los_Angeles

  const [ss1, ss2] = [spreadsheetId1, spreadsheetId2].map((id) =>
    SpreadsheetApp.openById(id)
  );
  const range = ss1.getSheets()[0].getRange("A1");
  const numberFormat = range.getNumberFormat();
  const dateObject = range.getValue();
  const orgTimezone = ss2.getSpreadsheetTimeZone();
  ss2.setSpreadsheetTimeZone(ss1.getSpreadsheetTimeZone());
  ss2
    .getSheets()[0]
    .getRange("!A1")
    .setNumberFormat(numberFormat)
    .setValue(dateObject);
  ss2.setSpreadsheetTimeZone(orgTimezone);
}

В этом сценарии значение ячейки 2023/01/01 12:00:00 извлекается как объект даты, часовой пояс изменяется на тот же, что и в электронной таблице A, а 2023/01/01 12:00:00 объекта даты помещается в «A1» 1-й вкладки в электронной таблице B. Затем часовой пояс таблицы B изменяется на исходный часовой пояс. В результате этого процесса 2023/01/01 12:00:00 помещается в «A1» первой вкладки таблицы B.

Пример сценария 4

Прежде чем тестировать этот сценарий, установите идентификаторы электронных таблиц.

В этом примере цель достигается путем преобразования объекта даты в серийный номер, отражающий разницу во времени.

В этом сценарии объект даты 2023/01/01 12:00:00 копируется без учета разницы во времени. Итак, 2023/01/01 12:00:00 копируется как 2023/01/01 12:00:00.

function sample4() {
  const spreadsheetIdA = "### Spreadsheet ID of Spreadsheet A ###"; // Asia/Tokyo
  const spreadsheetIdB = "### Spreadsheet ID of Spreadsheet B ###"; // America/Los_Angeles

  const [ss1, ss2] = [spreadsheetId1, spreadsheetId2].map((id) =>
    SpreadsheetApp.openById(id)
  );
  const range = ss1.getSheets()[0].getRange("A1");
  const numberFormat = range.getNumberFormat();
  const dateObject = range.getValue();
  const unixTimeWithOffset =
    dateObject.getTime() - 1000 * 60 * dateObject.getTimezoneOffset();
  const serialNumber = unixTimeWithOffset / 1000 / 86400 + 25569; // Ref: https://stackoverflow.com/a/6154953
  ss2
    .getSheets()[0]
    .getRange("!A1")
    .setNumberFormat(numberFormat)
    .setValue(serialNumber);
}

В этом сценарии значение ячейки 2023/01/01 12:00:00 извлекается как объект даты, а объект даты преобразуется в серийный номер с учетом разницы во времени. Затем серийный номер помещается в «A1» 1-й вкладки электронной таблицы B. Таким образом, 2023/01/01 12:00:00 помещается в «A1» 1-й вкладки электронной таблицы B.

Пример сценария 5

Прежде чем тестировать этот сценарий, установите идентификаторы электронных таблиц.

В этом примере цель достигается путем преобразования часового пояса объекта даты с помощью Utilities.parseDate.

В этом сценарии объект даты 2023/01/01 12:00:00 копируется без учета разницы во времени. Итак, 2023/01/01 12:00:00 копируется как 2023/01/01 12:00:00.

function sample5() {
  const spreadsheetIdA = "### Spreadsheet ID of Spreadsheet A ###"; // Asia/Tokyo
  const spreadsheetIdB = "### Spreadsheet ID of Spreadsheet B ###"; // America/Los_Angeles

  const [ss1, ss2] = [spreadsheetId1, spreadsheetId2].map((id) =>
    SpreadsheetApp.openById(id)
  );
  const range = ss1.getSheets()[0].getRange("A1");
  const numberFormat = range.getNumberFormat();
  const dateObject = Utilities.parseDate(
    range.getDisplayValue(),
    ss2.getSpreadsheetTimeZone(),
    range.getNumberFormat().replace(/"/g, "")
  );
  ss2
    .getSheets()[0]
    .getRange("!A1")
    .setNumberFormat(numberFormat)
    .setValue(dateObject);
}

В этом сценарии значение ячейки 2023/01/01 12:00:00 извлекается как объект даты, а объект даты преобразуется путем указания часового пояса электронной таблицы B. Затем преобразованный объект даты помещается в «A1» первой вкладки электронной таблицы B. В результате этого процесса 2023/01/01 12:00:00 помещается в «A1» первой вкладки таблицы B.

Рекомендации