【GAS】Google スプレッドシート Tips

導入

今回スプレッドシート関連の処理を実装した際に使用した関数をまとめます。

Tips

前提

コード内で出てくるsheetは以下の手順でsheetオブジェクトを取得している。

  // スプレッドシート取得(Idがnullの場合は、Activeなスプレッドシートを設定)
  const ss = spreadsheetId
    ? SpreadsheetApp.openById(spreadsheetId)
    : SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート名');

１行をまるごと取得する

１行をRangeで取得したい場合は、getRange(row, column, numRows) を使用します。

使用例

// 最終行(列数)を取得
let lastCol = sheet.getLastColumn();
// セル(1,1)を基準に1行 `lastCol`列 をRangeで取得
let headerRange = sheet.getRange(1, 1, 1, lastCol);
// 取得したRangeを配列に格納
let headers = headerRange.getValues()[0] as string[];
// 指定行(5行目)を取得
let valueRange = sheet.getRange(5, 1, 1, lastCol);
// 取得したRangeを配列に格納
let values = valueRange.getValues()[0] as string[];

マルチセレクトの編集を検知する

onEdit関数を使用します。
以下の例は、編集を検知して編集内容をログに出すだけの最小サンプルです。

type OnEditEvent = GoogleAppsScript.Events.SheetsOnEdit;

export function onEdit(e: OnEditEvent) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  const a1 = e.range.getA1Notation();
  const newValue = (e as any).value ?? null;
  const oldValue = (e as any).oldValue ?? null;

  console.log(`シート「${sheet.getName()}」の ${a1} が編集されました`, {
    newValue,
    oldValue,
  });
}

下記のような環境の場合、onEdit をグローバルに公開する必要があるため、main.tsに以下の内容を追加します。

/src/main.ts

import { App } from "./app";
import { onEdit } from "./utils/sheet-utils";

interface Global {
  App: typeof App;
  // Apps Script のグローバル関数として公開するハンドラ
  onEdit?: (e: GoogleAppsScript.Events.SheetsOnEdit) => void;
}
declare const global: Global;
// entryPoints
global.App = App;
// onEdit をグローバルに公開してトリガーとして動作させる
global.onEdit = onEdit;

詰まったところ

onEditが機能しない

Standaloneのプロジェクトとして作成していたため、スプレッドシートのバインドされたプロジェクトにpushする必要がありました。
GUIから操作するのが簡単だったので、以下の方法で解決しました。

手順

1. 対象のスプレッドシートを開く
2. 上部にある「拡張機能」 > 「App Script」を選択
3. バインドプロジェクトが作成される
4. 作成されたプロジェクトIDをコピー
5. .clasp.json の scriptId に設定する

onEditのログの確認場所

「対象プロジェクト」 -> 「実行数」
https://script.google.com/home/projects/[プロジェクトI]/executions

参考