🌟

GASでSpreadsheetを操作する時に役立つ知識

2021/12/28に公開約3,700字

よく使うメソッド

Spreadsheetを取得

const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); //スクリプトに紐づいたSpreadsheetを取得(基本これを使う)
const spreadSheet = SpreadsheetApp.openById(SHEET_ID); //IDから取得

シート(Spreadsheet内のシート)を取得

const sheet = spreadSheet.getSheetByName('シート1'); //シート名をもとに取得(こっちの方が可読性高い)
const sheet = spreadSheet.getSheets()[0]; //並び順をもとに取得

最終行や最終列を取得

const last_row = sheet.getLastRow(); //数値が返る
const last_col = sheet.getLastColumn(); //数値が返る

セルの範囲を指定

sheet.getRange('B2'); // セルB2を取得
sheet.getRange(1, 1); // セルB2を取得
sheet.getRange(1, 1, 3); // セルA1:A3を取得
sheet.getRange(1, 1, 3, 2); // セルA1:B3を取得

指定したセルの値を取得

const val = sheet.getRange('B2').getValue(); // セルB2の値を取得
const vals = sheet.getRange(1, 1, 3, 2).getValues() // セルA1:B3の値を取得

指定したセルに値を入力

sheet.getRange('B2').setValue('hogehoge'); // セルB2にhogehogeと入力
sheet.getRange(1, 1, 3, 2).setValues([
  ['1','hoge'],
  ['2','fuga'],
  ['3','bar']
])

最終行にデータを追加

sheet.appendRow([
    '1列目のデータ',
    '2列目のデータ',
    '3列目のデータ',
]);

日付を取得してフォーマット

Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm:ss");
Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd"); // GAのAPIを叩くときはこのフォーマット

APIを叩く

const response = UrlFetchApp.fetch('https://hogehoge.com/api/foo/bar');

ログを出力

Logger.log("ログhogehoge")

name = "Bob"
Logger.log("ユーザーの名前は%sです", name)

アラートを表示

Browser.msgBox('hogehoge')

オプションが色々ある。ボタンを複数設置して、クリックされたボタンに応じて次の処理を走らせることもできる。

https://www.yukibnb.com/entry/2019/01/22/164434

イベントハンドラ

onOpen

function onOpen(event) {
  // スプレッドシートが開かれるたびに、この名前の関数が発火する
}

onEdit

function onEdit(event) {
  // スプレッドシートに変更が加わるたびに、この名前の関数が発火する  
}

コードスニペット

x日前の日付を取得するメソッド

function calcDaysBefore(num) {
  let date = new Date();
  const day = date.getDate();
  date.setDate(day - num);
  return Utilities.formatDate(date, 'JST', 'yyyy-MM-dd');
}

RedashのAPIを叩いて、レスポンスからデータを取り出すメソッド

function fetchDataFromRedash(endpoint) {
  const response = UrlFetchApp.fetch(endpoint);
  return JSON.parse(response.getContentText()).query_result.data.rows;
}

2つの値の変化を%で出力するメソッド

function calcTransitionRate(before, after) {
  a = Math.round((after/before)* 1000)
  return (a/10 + '%')
}

その他知っておきたいこと

ファイルを分割しても、全てグローバルな名前空間になる

スタンドアロンスクリプトコンテナバインドスクリプト

Google Apps Scriptには大きく分けて2種類あります。「スタンドアロンスクリプト」と「コンテナバインドスクリプト」です。

「スタンドアロンスクリプト」はその名の通り、スタンドアロンで実行される単体のスクリプトプロジェクトとなります。Google ドライブからGoogle Apps Scriptのプロジェクトファイルを新規作成していきます。

「コンテナバインドスクリプト」はスプレッドシート、ドキュメント、フォームといった親ファイルに紐づく形で保存される形式のスクリプトプロジェクトです。スプレッドシート、ドキュメント、フォームなどから新規作成します。

https://tadaken3.hatenablog.jp/entry/apps-script-dashboard

ローカルで開発したり、ソースをGitHubで管理することもできる

参考

https://developers.google.com/apps-script/reference/
https://caymezon.com/gas-ss-methods-list/
https://qiita.com/tanabee/items/2c51681396fe12b6a0e4
https://qiita.com/ikemai/items/2900521062a11796738f
https://www.iehohs.com/gas-cheatsheet/
https://zenn.dev/creationup2u/articles/609fd50c2eb426
https://www.bugbugnow.net/2018/12/GoogleAppsScript.html
http://www.googleappsscript.info/2017-07-23/setting_onopen_onedit.html

Discussion

ログインするとコメントできます