GASでよく使うことメモ

指定した範囲のクリア

定期的なデータ更新ジョブなどを走らせる際、関数とかはスプレッドシートに直接書いちゃったほうが工数的に楽なので、そこは残しつつ、関数で参照するシートデータだけ随時更新する時とかに便利。

// A:Dの範囲だけクリア
const range = sheet.getRange("A:D")
range.clearContent()

シートデータの取得

いちいち範囲指定しても、元シートにメンテが入るとコードもメンテ必要なので、有効なデータ範囲のデータを一括取得できる。

const sheetData = sheet.getDataRange().getValues()

ファイルのディレクトリ構成を可視化

プラスボタン押して tests/main などと入力すると階層になる。反映される時とされない時があるので、その時は画面リロードすればOK

列名からindex取得

よくシートを勝手に列変更されたりするので、列名で何列目なのかを探る。ただ、列名だけ変えないでねという啓蒙は必須。

const headers = masterSheetData[0]
const engagementFeeColumnIndex = headers.indexOf("成約金額")

必要なデータだけに絞り込みして整形

mapでif文かますとelse部分は有効でないものが入ってきてしまうので、filterで適当に排除しておく。

const targetRows = masterSheetData.map((data, idx) => {
  if(data[engagementFeeColumnIndex] >= 500000) {
    return {
      data: data,
      rowNum: Number(idx)+1
    }
  }
}).filter(a => a)

Slack通知メソッド

ライブラリ化してもいいんだけども、GASにそこまで時間かけたくないのでコピペ用。BlockKit用いたものはjsonDataの部分でblocksプロパティを設定するが、そんなに豪華にやらなくていい。

const postSlack = (message, room) => {
  const postUrl = `${設定したwebhookURL}`
  const roomName = room ? room : "#development_notifications"
  const jsonData =
  {
     "text": message,
     "channel": roomName,
  }
  const payload = JSON.stringify(jsonData)
  const options =
  {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : payload
  }
  UrlFetchApp.fetch(postUrl, options)
}

処理実行速度改善の工夫

GASはメソッド最大処理時刻が180sなので、遅い処理は途中で落ちてめんどくさい。それを解決するためによくやっていること。

setValueではなくsetValuesを使う

// ちなみにこのgetRangeの第3,4引数は割と便利だよ
sheet.getRange(2,1,outputData.length, outputData[0].length).setValues(outputData)

// これをあえて遅く書くと、
outputData.forEach(data => {
  sheet.getRange(2,1,1,data.length).setValue(data)
})
=> outputDataの要素分だけ、API（sheet.getRangeやrange.setValue）が呼ばれるので遅くなる

変数をあまり使わない

そこまで気にしなくてもいいが、変数定義時にメモリ的には若干の負荷がかかるので不要なものは回避

const data = sheet.getDataRange().getValues()
data.forEach(...)

// data変数をここでしか使用しないのであれば、以下にしたほうが負荷的には削減できる
// リーダブルか否かはGASで気にしなくていい。処理の流れのコメントは書こう
sheet.getDataRange().getValues().forEach(...)

日付フォーマット

UTCとかめんどくさいのでJSTにしてフォーマット化する。

const response = UrlFetchApp.fetch(url)
const contractedAt = new Date(response.results.contracted_at) // 2022-01-01 12:00:00Tとか
const parsedContractedAt = contractedAt ? Utilities.formatDate(contractedAt, 'JST', 'yyyy/MM/dd') : ""

API

コピペ用

const getAllDeals = () => {
  const url = `${API_BASE_URL}?XXXX=test`
  try {
    return UrlFetchApp.fetch(url, options)
  } catch(e) {
    return false
  }
}

罫線とか消さないで、値だけクリアする

罫線とか折角設定して見栄え良くしてるのにclearで全部消えてしまうのが虚しいので、formatOnly: falseにより回避する。

sheet.getDataRange().clear({ formatOnly: false, contentsOnly: true })

スクレイピングで使うライブラリ

Parser

取得してきたHTMLテキストデータをよしなに分割するのに使用

const html = getHtmlJsonViaPhantom(targetUrl)
const targetArticles = Parser.data(html).from('<tr').to("</tr>").iterate()
targetArticles.forEach(article => {
  const linkBlock = Parser.data(article).from("<a").to("</a>").build()
  const [dummy, link] = linkBlock.match(/.+href="(.+)".+/)
})

PhantomJsCloud

レンダリングが必要なサイトなどは、すべて読み込まれるまで待機する必要がある。待機して結果のHTMLテキストを返してくれる。

const execHtmlJsonViaPhantom = (url, key) => {
  let payload = {
    url: url,
    renderType: 'HTML',
    outputAsJson: true,
    requestSettings: {
      maxWait: 300000
    }
  }
  payload = JSON.stringify(payload)
  payload = encodeURIComponent(payload)
  const phantomjscloud = `https://phantomjscloud.com/api/browser/v2/${key}/?request=${payload}`
  const response = UrlFetchApp.fetch(phantomjscloud)
  const json = JSON.parse(response.getContentText())
  const html = json["content"]["data"]

  return html
}

その他気をつけていること

トリガーは社内共通アカウントで登録すべし
- 個人で設定してその人が退社とかしちゃったらメンテできないyo！
やりたいこと単位でファイル作成するのではなく、ちゃんと/Apiとか/Configとか直感的にわかりやすい階層を作成
ファイルの冒頭でauthorを書いたほうがいい
- clasp導入してもいいけど、GASで永遠にメンテし続けるのであればMVPとしての役割は終わりで、管理画面などに移行するように起票すべき
使わなくなったものは消す
- 当たり前だけど、他の人が見た時にそれっぽいメソッド名なのに使われてないんかーい！ってなるとあれ
コード管理したいのであればclaspかGASから直接gitリポジトリにpush/pullできる拡張機能もあるので、それで管理すると楽です
- https://chrome.google.com/webstore/detail/google-apps-script-github/lfjcgcmkmjjlieihflfhjopckgpelofo?hl=en
GASは共同作業しない
- 共同作業すると互いにPRDにアクセスしてvimで書き合っているようなもの。また、GASを共同作業するくらい規模デカ目なのであれば、管理画面などに組み込むように起票したほうがいい

GASでよく使うこと、意識していること