Google Sheetsのカスタムメニューで特定セルを開くスクリプトを実装した

久々にGASを触って試行錯誤した。

[完成形]

このようなカスタムメニューを実装した。「今日を開く」をクリックすると、本日の日付が入力されているセルを選択して画面表示を切り替えてくれる。

実際の動作はこのようになる（キャプチャは2022/02/27に取得）。

このメニューを表示、実行するためのスクリプトはこちら。

function onOpen() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('デイリー', [
      {name: '今日を開く', functionName: 'openToday'}
    ]);
}

function openToday() {
  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  var today = toDate(new Date());
  const maxColumns = 365; // 最大365カラム先まで検索
  const dateRow = 2;　// 日付データが２行目にある
  const data = sheet.getRange(dateRow, 1, 1, maxColumns).getValues()[0];
  const index = data.findIndex(cellData => {
    const d = toDate(new Date(cellData));
    return d == today;
  }) + 1;
  if (0 < index) {
    var range = sheet.getRange(dateRow, index);
    SpreadsheetApp.setActiveRange(range);
  } else {
    SpreadsheetApp.getUi().alert(`${today}のセルが見つかりません`);
  }
}

function toDate(d) {
  return `${d.getFullYear()}/${toMonth(d)}/${d.getDate()}`
}

function toMonth(today) {
  const month = today.getMonth() + 1;
  return month < 10 ? `0${month}` : month;
}

別途、設定ファイル（appscript.json）でタイムゾーンを日本標準時（Asia/Tokyo）に設定している。

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

[解きたかった問題]

何のためにこのメニューを実装したかというと、日々やっているデイリースクラムもどきのためである。

開発チーム内での、メンバーが昨日やったこと、今日やること、障害になってることなどを、Google Sheetsに書き出して日々共有している。例えば以下のようなイメージ。

大体月替わりくらいでシートを作り直して運用していたのだが、月の後半になると、デイリースクラムの開始時に10セル以上右に移動して、今日の記入欄を探す羽目になっていた*1。

1人数秒とは言え、日々時間が無駄になっているなあと感じていたところ、次の記事を読んでGoogle Sheetsに拡張機能を実装できることを知った。

qiita.com

2日以上前は基本的にあまり参照しないので、毎日対象列を非表示にするスクリプトを実装してスケジュール実行することも考えた。

しかし、「先週何やってたっけ？」とか「あの件について話したっけ？」と過去の記入内容を参照することがまれによくあるので、過去列を非表示にしてしまうと不便そうと判断し、この方針はナシにした。

そうした経緯で出来上がったのが冒頭のスクリプトである。

自分を含め、初回利用時にはスクリプトに権限を許可する必要がある（詳細は上記Qiita記事参照）。

カーソルを特定のセルに移動させる方法については、こちらのStackoverflowを参考にした。

stackoverflow.com

[GASのタイムゾーン]

スクリプトの実装中に、タイムゾーン問題にハマった。

「2022/02/28」が入力されたセルの値を取得すると、JavaScriptのDateオブジェクトが取得できる。しかし、これをconsole.log()で出力してみると、デフォルトのタイムゾーンがアメリカ東部標準時になっているらしく、以下のように出力された。

Sun Feb 27 2022 10:00:00 GMT-0500 (アメリカ東部標準時)

とりあえず、日付の処理をする箇所で、以下のようにタイムゾーンのUTCとの差を取得し日本時間と合わせて時刻を進めることで暫定的に回避して実装を進めた。

const offset = (new Date().getTimezoneOffset() + (9 * 60)) * 60 * 1000;  
const today = toDate(new Date(Date.now() + offset));

最終的には、プロジェクトの設定からタイムゾーンを日本標準時に設定することで、上記コードを以下のようにシンプルにできた。

const today = toDate(new Date());

masa-enjoy.com

[まとめ]

• デイリースクラムで利用するGoogle Sheetsにカスタムメニューを実装した
• タイムゾーン問題にハマって時差計算で暫定回避したが、GASのプロジェクト設定からタイムゾーンを変更できた

*1:会社のアカウントのセッションが何時間かで切れるため、シートをブラウザで開きっぱなしにしていても、翌日には再度ログインが必要でA1セルからやり直しになってしまうのが原因。