Google Apps Scriptでよく使うコードのメモ（スプレッドシート）

2021.11.30

Google Apps Scriptを書くとき、いつも同じようなことをググっている気がする。
・・・と思ったので、自分がよく使うものをまとめてみました。

スプレッドシート / シートを扱う

スクリプトが紐づいているスプレッドシートを取得

const ss = SpreadsheetApp.getActiveSpreadsheet();

IDを指定してスプレッドシートを取得

const ss = SpreadsheetApp.openById("スプレッドシートのID");

参考：スプレッドシートのIDがわからず、名前はわかる場合
＊ 同じフォルダ内に 同名のファイル / 同名のフォルダ が複数存在する場合はこの方法は使えない

// 例１：　□フォルダ(IDがわかる) > □スプレッドシート(名前だけわかる)
const folder = DriveApp.getFolderById("フォルダのID");
const file = folder.getFilesByName("スプレッドシートの名前").next();
const ss = SpreadsheetApp.openById(file.getId());

// 例２：　□親フォルダ(IDがわかる) > □子フォルダ(名前だけわかる) > □スプレッドシート(名前だけわかる)
const parentFolder = DriveApp.getFolderById("親フォルダのID");
const childFolder = parentFolder.getFoldersByName("子フォルダの名前").next();
const file = childFolder.getFilesByName("スプレッドシートの名前").next();
const ss = SpreadsheetApp.openById(file.getId());

シートを取得

const sheet = ss.getSheetByName("シート名")

シートを追加

// シートを追加
const newSheet = ss.insertSheet();
// シートの名前を変更
newSheet.setName("追加するシートの名前");

シートを削除

// シートを削除
ss.deleteSheet(sheet);

単一のセルを扱う

単一セルの値を取得する

// B3セル の値を取得する例１
const value = sheet.getRange("B3").getValue();

// B3セル の値を取得する例２
const value = sheet.getRange(3, 2).getValue();  // 3行目, 2列目

単一セルに値をセットする

// B3セル に値をセットする例１
sheet.getRange("B3").setValue("セットする値");

// B3セル に値をセットする例２
sheet.getRange(3, 2).setValue("セットする値");  // 3行目, 2列目

単一セルの値や書式などをクリアする

// B3セル をクリアする例（getRangeは、行番号, 列番号 の書き方でもOK）
sheet.getRange("B3").clear();         // セルをクリア（書式などもクリア）
sheet.getRange("B3").clearContent();  // セルの値をクリア
sheet.getRange("B3").clearFormat();   // セルの書式をクリア

複数のセル（範囲）を扱う

サンプルとして、このような表を用意しました

複数セルの値を取得する

// A1～C4セル の値を取得する例１
const values = sheet.getRange("A1:C4").getValues();

// A1～C4セル の値を取得する例２
const values = sheet.getRange(1, 1, 4, 3).getValues();  // 1行目, 1列目を起点に、4行, 3列分を取得
[[id, name, age], [A001, kenji, 21.0], [A002, taro, 18.0], [A003, yuko, 39.0]]

参考：シートがA1セルからはじまる綺麗な表の形の場合は getDataRange も使いやすい

// A1セルから、データが存在する最も右下のセルまでの範囲を取得
const records = sheet.getDataRange().getValues();

// １行目（項目名行とか）が不要な場合は slice で切り捨てる
const records = sheet.getDataRange().getValues().slice(1);

複数セルに値をセットする

// A1～C4セル に値をセットする例
const data = [
  ["id", "name", "age"], 
  ["A001", "kenji", 21], 
  ["A002", "taro", 18], 
  ["A003", "yuko", 39]
];

// シートに貼り付ける
sheet.getRange("A1:C4").setValues(data);  // A1～C4セルの範囲に貼り付け

参考：データの行数・列数が場合によって変動する場合

// シートに貼り付ける
// 1行目, 1列目を起点に、dataの要素数(=行数), data[0]の要素数(=列数)分の範囲に貼り付け
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

複数セルの値や書式などをクリアする

// A1:C4セル をクリアする例（getRangeは、行番号, 列番号, 行数, 列数 の書き方でもOK）
sheet.getRange("A1:C4").clear();         // 範囲A1:C4のセルをクリア（書式などもクリア）
sheet.getRange("A1:C4").clearContent();  // 範囲A1:C4のセルの値をクリア
sheet.getRange("A1:C4").clearFormat();   // 範囲A1:C4のセルの書式をクリア

取得した表（範囲）を使って一括処理する

参考：取得した表（２次元配列）の各行に対して処理を行うサンプル

const records = sheet.getDataRange().getValues().slice(1);

for (const record of records){
  // ID_名前 形式のフォルダをマイドライブに作成
  const folder = DriveApp.createFolder(`${record[0]}_${record[1]}`);
  // ログ出力
  Logger.log(`Folder created! ID:${folder.getId()} NANE:${folder.getName()}`);
}
情報	Folder created! ID:1Bxvtsa2FRbaUcWIdZvMbICmixbULliT3 NANE:A001_kenji
情報	Folder created! ID:1R_WCJoDzPeyxxIvFZBnHcaTNxOeOPNF3 NANE:A002_taro
情報	Folder created! ID:170eXyHb9uE5R95yFdVeyojUcbSyI9Hcw NANE:A003_yuko

日付・時刻を扱う

dateオブジェクトの作成

// 現在日時をセット
const d = new Date();

// 以下はすべて JST 2021/12/24 12:30:45 を指定する例
const d = new Date(2021, 12-1, 24, 12, 30, 45);  // 月は 0 = 1月, 1 = 2月...のため-1するとわかりやすい

// ISO8601形式
const d = new Date("2021-12-24T12:30:45+09:00");

// Unix Time
const d = new Date(1640316645 * 1000);

dateオブジェクトから情報を取得

const d = new Date(2021, 12-1, 24, 12, 30, 45);

const year = d.getFullYear();     // 年
const month = d.getMonth() + 1;   // 月（0始まりのため+1する）
const date = d.getDate();         // 日
const hours = d.getHours();       // 時
const minutes = d.getMinutes();   // 分
const seconds = d.getSeconds();   // 秒

dateオブジェクトの日付・時刻を更新

const d = new Date(2021, 12-1, 24, 12, 30, 45);

// "+" を "-" にすれば引き算も可能
d.setFullYear(d.getFullYear() + 5); // 5年後
d.setMonth(d.getMonth() + 5);       // 5か月後
d.setDate(d.getDate() + 5);         // 5日後
d.setHours(d.getHours() + 5);       // 5時間後
d.setMinutes(d.getMinutes() + 5);   // 5分後
d.setSeconds(d.getSeconds() + 5);   // 5秒後

日付・時刻を文字列化

const d = new Date(2021, 12-1, 24, 12, 30, 45);

// ISO8601形式
const isoString = d.toISOString();
Logger.log(isoString);  // 	2021-12-24T03:30:45.000Z  ← 環境を問わずUTCになる

// 形式を指定して文字列化する
const formattedDate = Utilities.formatDate(d, "JST", "yyyy年MM月dd日 HH時mm分ss秒");
Logger.log(formattedDate);  // 2021年12月24日 12時30分45秒

formatDateの詳細は下記参照

Class Utilities  |  Apps Script  |  Google for Developers
developers.google.com

JSONをスプレッドシートに展開する

JSONをスプレッドシートに展開するサンプル

// サンプルとして取り扱うJSONデータの内容
// 変数 json に格納されているものとする
{
    status: "1",
    message: "OK",
    result: [
      { id: "A001", name: "kenji", age: "21" },
      { id: "A002", name: "taro", age: "18" },
      { id: "A003", name: "yuko", age: "39" }
    ]
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("貼り付け先シートのID");  
const data = [];
 
// JSONをオブジェクトに変換
const obj = JSON.parse(json);
 
// key取得
data.push(Object.keys(obj.result[0]));

// value取得
for (const item of obj.result){
  data.push(Object.values(item));
}

// シートに貼り付け
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
スプレッドシートに展開した結果

コメント