自分のためのGASチートシート（10選）2年向き合って便利だなと思ったもの✨ #JavaScript

こんにちは。
株式会社HRBrainでフロントエンドエンジニアをしているみつです。

GAS(Google Apps Script)と2年間ほど向き合ってみて・・

ふと、
スプレッドシートの操作に必要な処理って意外と限られるのでは・・・？

ただ、
もちろんなんでもこれだけで・・・という感じではない・・・

みたいな気持ちがありつつ。

誰かの開発スピードもUPするといいなぁと思ってまとめてみました。

※基本的に、スプレッドシートに紐づくGASを操作するためのコード（コンテナバインド型）を想定しています。

スプレッドシートのなにか系
- ✨ 1. スプレッドシートをアクティブにする関数
- ✨ 2. スプレッドシートのIDだけ得る関数
取得した値を綺麗にする系
なくてもいいけど、あってもいい系
BigQuery系
- ✨ 10. BigQueryでSQLを実行する関数（リトライ付き）
最後に

スプレッドシートのなにか系

✨ 1. スプレッドシートをアクティブにする関数

SpreadsheetAppというGAS特有の書き方を覚えることに失敗した結果これができました。

愚直に書いてもいいんですけど、あると便利じゃない・・・？

const activateSpreadSheetBySsName = (sheetName) => {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetObject = spreadSheet.getSheetByName(sheetName);
  return sheetObject;
};

✨ 2. スプレッドシートのIDだけ得る関数

SpreadsheetAppというGAS特有の書き方を覚えることに失敗した結果これができました。

上と同じ気持ちです。

const getSpreadSheetID = () => {
  const thisSs = SpreadsheetApp.getActiveSpreadsheet();
  const thisSsID = thisSs.getId();
  return thisSsID;
};

取得した値を綺麗にする系

✨ 3. 全角と半角変換を整える関数

みんな思い思いの値を入力する・・・

とりあえず半角に！！

みたいな時に使えるかなみたいなフォーマット整えてくれる系の関数。

const upperToLower = (targetValue) => {
  return targetValue.toString().replace(/[Ａ-Ｚａ-ｚ０-９]/g, function (s) {
    return String.fromCharCode(s.charCodeAt(0) - 0xfee0);
  });
};

✨ 4. 日付のフォーマットをちょっと整える関数

みんな思い思いの日付を入力する・・・

とりあえずスラッシュ区切りの日付！！

みたいな時に使えるかなみたいな日付を整えてくれる関数。

これで他のフォーマットでも返せるね。

const normalizeDate = (targetValue) => {
  const regex = /(\d{4})[-\/\.](\d{1,2})[-\/\.](\d{1,2})/;

  if (!regex.test(targetValue)) {
    return targetValue;
  }

  const match = regex.exec(targetValue);

  const year = match[1];
  const month = match[2].padStart(2, "0");
  const day = match[3].padStart(2, "0");

  return year + "/" + month + "/" + day;
};

✨ 5. 配列の""をなくして、文字だけの配列にして返す関数

これが意外と便利な気がしてる！！

GASでgetRange()してからgetValues()とか、
getDataRange()をすると何も値の入っていないセルの値""も含まれてたりして・・・

出力画面が""でぐちゃーーーん！！！みたいなことよく起こしちゃうので・・・

forとかmapの中でこれ使うとか・・・

意外と使ってます。

他にやり方あったら恥ずかしい・・

const filterString = (prevArray) => {
  const newArray = prevArray.filter(String);
  return newArray;
};

なくてもいいけど、あってもいい系

✨ 6. UUIDだけもらう関数

なんかgenerateID()でID作れるの素敵だなって思った関数。

const generateID = () => {
  return Utilities.getUuid();
};

✨ 7. onOpen関数に大量に処理を記載しない

別にぐちゃぁぁってonOpen関数に書くのも良いと思うけど・・・

まとめとくとなんか気分良いかも？

const onOpen = () => {
  onOpenAddHeaderMenuButton();
};

const onOpenAddHeaderMenuButton = () => {
  let ui = SpreadsheetApp.getUi();
  let menu = ui.createMenu("実行ボタン");

  menu.addItem("【入力実行】", "main");
  menu.addToUi();
};

✨ 8. 処理の最後にモーダルを表示する関数

別になくてもいいけどあってもいいのかな、みたいな関数。

処理の最後に入れておいてあげるとなにやら処理が終わった感がシートに見える。

一応、ぺたっ。

completePage.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    <h1>Complete Page!</h1>
  </body>
</html>

const showModal = () => {
  const html = HtmlService.createHtmlOutputFromFile("completePage")
    .setWidth(400)
    .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(
    html,
    "処理が完了しました。"
  );
};

✨ 9. 共通で使う値はこんな形でまとめてみたり

まとめておいたらコレクター癖のある自分はちょっとHappyに

実際のコードにはもっともっとたくさん書きました・・・！

const getCommonValues = () => {
  let effectiveUser = Session.getEffectiveUser();

  let today = new Date();
  let formatToday = Utilities.formatDate(
    today,
    "Asia/Tokyo",
    "yyyy/MM/dd HH:MM:ss"
  );

  return {
    effectiveUser,
    today,
    formatToday
  };
};

BigQuery系

✨ 10. BigQueryでSQLを実行する関数（リトライ付き）

getCommonValues()のbigqueryProjectSettingsの中身はこんな感じ。

  const bigqueryProjectSettings = {
    projectID: "",
    datasetName: ""
  };

const jobQuery = (sql) => {
  const commonValues = getCommonValues();
  const bigquerySettings = commonValues.bigqueryProjectSettings;

  const jobDetails = {
    status: "",
    statusMessage: "",
    errorMessage: "",
    result: null,
  };

  try {
    jobDetails.result = BigQuery.Jobs.query(
      {
        useLegacySql: false,
        query: sql,
      },
      bigquerySettings.projectID
    );
    let numTries = 0;
    while (
      (jobDetails.result["jobComplete"] !== true ||
        jobDetails.result["numDmlAffectedRows"] !== 0) &&
      numTries < 4
    ) {
      Utilities.sleep(500);

      jobDetails.result = BigQuery.Jobs.query(
        {
          useLegacySql: false,
          query: sql,
        },
        bigquerySettings.projectID
      );

      numTries += 1;
    }

    if (jobDetails.result["jobComplete"] === true) {
      jobDetails.status = "success";
      jobDetails.statusMessage = "success";
    }
  } catch (e) {
    jobDetails.status = "error";
    jobDetails.statusMessage = "error";
    jobDetails.errorMessage = e.message;
  }

  return jobDetails;
};

最後に

とりあえず自分が使ってきた大好きな関数たちを並べました。

このカスタムした関数たちとfor文とか基本的なのを使って良い感じに処理を作ってきたかなぁみたいな感じです！

もちろんこれだけでできないこと、

API叩いて・・・
他のGoogle系サービスを使って・・・

みたいなこととかはあると思いますが・・・

スプレッドシートで何かをするぞ〜
系ならこれでスピードUpするのではぁ・・・？と思ってます。

誰かの開発スピードUPに貢献できてればいいなぁ。

おわり。

自分のためのGASチートシート（10選）2年向き合って便利だなと思ったもの✨

目次