google apps script

Google Apps Script

2023-03-18 04:37

2023-03-25 16:28

Q&A

Sheets API に関する Q&A

スプレッドシートの値を参照したい

基本的にはシートを取得後、Range を指定して getValues する流れ。下記のように getDataRange を用いるとデータが入っている範囲を雑に指定できる。

const SPREADSHEET_ID = '......'
const SHEET_NAME = '......'
const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME)
sheet!.getDataRange().getValues()

カスタム関数呼び出し時にシートを開く権限がないというエラーが出る

Apps Script のエディタからは正常に実行できるがカスタム関数としてシートから呼び出すと以下のようなエラーがでる場合、関数内部で SpreadsheetApp.openById() または SpreadsheetApp.openByUrl()を利用していないか確認する。

Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets
    at ...

下記ドキュメントに記載のとおりカスタム関数から呼び出せるサービスには制限がある

スプレッドシート読み取り専用（ほとんどの get*() メソッドは使用できますが、set*() は使用できません）他のスプレッドシート（SpreadsheetApp.openById() または SpreadsheetApp.openByUrl()）を開くことはできません。 Google スプレッドシートのカスタム関数 | Apps Script | Google Developers

YouTube API に関する Q&A

カスタム関数呼び出し時に有効なアクセストークンがないというエラーが出る

Apps Script のエディタからは正常に実行できるがカスタム関数としてシートから呼び出すと以下のようなエラーがでる場合、関数内部で YouTube API を利用していないか確認する。

GoogleJsonResponseException: API call to youtube.videos.list failed with error: The request is missing a valid API key.
    at ...

残念ながら YouTube API はカスタム関数呼び出し時に利用可能な API に含まれていない。

clasp に関する Q&A

ローカルで開発したい

Google が Clasp: google/clasp というツールを提供している。Clasp は Command Line Apps Script Projects の略。

下記コマンドで Clasp の導入、Google アカウントとの連携、およびリモートの App Script をローカル環境にクローンできる。

$ npm install -g @google/clasp
$ clasp login
$ SCRIPT_ID=
$ clasp clone $SCRIPT_ID

TypeScript で開発したい

まず yarn init && yarn add -D typescript @types/google-apps-script && yarn tsc --init コマンドを叩いて package.json の作成と必要なパッケージの導入、tsconfig.json の生成を行う。tsconfig.json は下記のような内容にする。

{
  "compilerOptions": {
    "target": "ES5",
    "lib": ["ESNext"],
    "module": "commonjs",
    "esModuleInterop": true,
    "outDir": "dist",
    "strict": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", ".vscode"]
}

続いて dist/appscript.json として以下のような内容を配置しあとは src 下に適当に TS ファイルを実装して clasp push すれば clasp によって自動的にトランスパイルされ App Script 上にコードがプッシュされる。

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

各種 Google API を TypeScript から呼び出したいが型定義は利用可能か

@types/google-apps-script を導入すれば基本的に OK。特段何の import もなしに以下のように Sheets API を呼び出せるはず。

const sheet = SpreadsheetApp.openById("...").getSheetByName("...")

ローカルで実行したい

clasp のドキュメントに従い GCP プロジェクトの作成、App Script の GCP プロジェクトへの紐付け、OAuth クライアントの作成、App Script のデプロイなどを行う

ローカル Sheets API などを用いたスクリプトの実行がエラーとなる

clasp run 時に以下のようなエラーが出た場合、OAuth スコープに必要なものが含まれていない可能性が高い、

Exception: Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets [ { function: 'getChannels', lineNumber: 9 } ]
ScriptError

appscript.json の oauthScopes に以下のような内容を追加後 clasp login --cred cred.json コマンドを叩き認可を与えればよい

  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ]

export const した定数が import できない

2023/03 現在、下記のとおり ES modules 自体がサポートされていない

Currently, Google Apps Script does not support ES modules. clasp/typescript.md at master · google/clasp

以下のいずれかのワークアラウンドで対応する形となる

The exports declaration workaround
The namespace statement workaround
The third party build-chain workaround

<!DOCTYPE html>
<html>
  <body>
    <h1>test</h1>
    <div>hello</div>
  </body>
</html>

つづいて app.ts を以下のように記述し、デプロイ

function doGet() {
  return HtmlService.createTemplateFromFile("index.html").evaluate()
}

API を公開したい

下記のようなコードでとりあえず hello を返す API を作れる

const doGet = () => {
  const output = ContentService.createTextOutput()
  output.setMimeType(ContentService.MimeType.JSON)
  output.setContent(JSON.stringify('hello'))
  return output
}