スプレッドシートで簡易なデータ連携・検証に使う際のGAS集
はじめに
仕事をしていると、何でもかんでもサーバーで動かす、ソフトウェアライセンス費用がかかる、費用対効果もわからないので、簡易なデータ連携の基盤を作るが色々と面倒だったりする。そんなときに様々なツールと連携しているスプレッドシートを使うと色々と便利な場面がありそうなことがわかった。
しかし、GAS(Javascript)は書いたことがない・・・なので、これは基礎からまとめる勉強用の備忘録。
- エラーないし文字補完が機能しないときは大概、1行上でメソッドに
()をつけ忘れている。 Active系の関数は指定せず、ちゃんと指定するほうがよい
GASスクリプトの種類
Google SpreadSheet
スプレッドシートは4つのクラス階層(SpreadsheetApp→Spreadsheet→Sheet→Range)で成り立っている。
スクリプトの実行イメージ。
- SpreadsheetAppクラスのgetActiveSpreadsheetメソッドはSpreadsheetクラスのオブジェクトを取得し、戻り値として返す。
- Spreadsheetオブジェクト(クラス)を取得すると、それに属するメソッドを実行できるので、getActiveSheetメソッドを実行、getActiveSheetメソッドは現在の選択中のシートを表すSheetクラスのオブジェクトを取得し、戻り値として返す。
- Sheetオブジェクト(クラス)を取得し、getRangeメソッドを実行。getRangeメソッドは、指定されたセルの値を取得する。
function myfunc(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var cell = sheet.getRange("A1")
}
アクティブなセルの値を取得・書込
function myfunc() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
// アクティブなセルから値を取得
console.log(ss.getActiveRange().getValue())
// アクティブなセルに値を入力
ss.getActiveRange().setValue("set value")
// 指定したセルに値を入力
ss.getRange("A10").setValue("A10にset")
// 指定したセル範囲に値を入力。5行5列目を起点に2行3列の範囲に値をセット
ss.getRange(5,5,2,3).setValue("指定した範囲にset")
}
アクティブなシートの名前を取得
function get_sheet_name() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var sheet_name = spread_sheet.getName();
Logger.log(sheet_name)
}
複数シートが存在する場合に目的のシートを取得
function get_specific_sheet_name() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet()
// シート名を指定
var specific_sheet_name = spread_sheet.getSheetByName("sheetB").getName();
Logger.log(specific_sheet_name)
}
下記の例では、とりあえず選択中のシートのシート名をすべて取得して配列に収め、配列の要素を指定して取り出す方法
function get_specific_sheet_name() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet()
// シートを配列で取得
var specific_sheet_name = spread_sheet.getSheets();
Logger.log(specific_sheet_name)
// 左端のシートが1なので、インデックスを指定してシート名を取得
Logger.log(specific_sheet_name[0].getName())
Logger.log(specific_sheet_name[1].getName())
Logger.log(specific_sheet_name[2].getName())
}
その他のスプレッドシートを取得する方法
コンテナバインドスクリプトでも、関係のないスプレッドシートのURLやIDを指定することでスプレッドシートの名前を取得できる。
function get_spread_sheet_name() {
// URLで指定する方法
// SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/<SpreadSheetID>/edit#gid=0')
var spread_sheet = SpreadsheetApp.openById('<SpreadSheetID>')
var spread_sheet_name = spread_sheet.getName();
Logger.log(spread_sheet_name)
}
シートをコピーする方法
function do_copy_sheet() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet()
// アクティブなシートを取得
var sheet = spread_sheet.getActiveSheet();
// シートのコピーを実行
// コピー先のスプレッドシートはspread_sheet
var new_sheet = sheet.copyTo(spread_sheet);
// シート名を設定
new_sheet.setName("Copied sheet")
}
コンテナバインドスクリプトでも、関係のないスプレッドシートのURLやIDを指定することでスプレッドシートのシートをコピーすることができる。
function do_copy_sheet() {
// var spread_sheet = SpreadsheetApp.openById("<SpreadSheetID>")
// コピー元のスプレッドシートをIDで指定する方法でもよい
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet()
// アクティブなシートを取得
var sheet = spread_sheet.getActiveSheet();
// シートのコピー先を設定
// 別のスプレッドシートのIDを取得
var to_spread_sheet = SpreadsheetApp.openById("<SpreadSheetID>")
var new_sheet = sheet.copyTo(to_spread_sheet);
// シート名を設定
new_sheet.setName("Copied sheet2")
}
シートのクリーンアップ
function do_cleanup_sheet() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
// クリーンアップ対象はアクティブなシートを設定
var sheet = spread_sheet.getActiveSheet()
sheet.clear();
// クリーンアップ対象のシートを指定
var specific_sheet = spread_sheet.getSheetByName("sheetB");
specific_sheet.clear()
}
指定した範囲のセルの値を取得する
function get_cell_values() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
// A1:E5で5行5列を取得
var range = spread_sheet.getRange("A1:E5")
// 指定した範囲の確認
Logger.log(range.getA1Notation());
// 指定した範囲の値を配列で取得
Logger.log(range.getValues());
}
指定した行と列の値を取得する
function get_rowcol_cells() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread_sheet.getActiveSheet();
// A:Aで1列を取得
var col = sheet.getRange("A:A")
Logger.log(col.getA1Notation());
Logger.log(col.getValues());
// 1:1で1行目を取得
var row = sheet.getRange("1:1")
Logger.log(row.getA1Notation());
Logger.log(row.getValues());
}
データの範囲を自動で判定して値を取得する
function get_dataframe() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread_sheet.getActiveSheet();
// データの範囲を自動で判別して取得
// 空白行も範囲とみなされるので注意
var df = sheet.getDataRange();
Logger.log(df.getA1Notation());
Logger.log(df.getValues());
}
最終列や最終行を判定する方法
function get_rowcol_index() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread_sheet.getActiveSheet();
// データの範囲の末尾を検索して取得
var last_col = sheet.getLastColumn();
var last_row = sheet.getLastRow();
Logger.log(last_col);
Logger.log(last_row);
}
データの範囲を指定して値を取得する
function get_dataframe2() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread_sheet.getActiveSheet();
// データの範囲の最終行を検索して取得
var last_row = sheet.getLastRow();
// データの範囲をN行☓3列に設定
var df = sheet.getRange(1,1,last_row,3)
Logger.log(df.getValues());
}
スプレッドシートのシートを指定してデータを取得する方法
function get_specific_dataframe() {
var spread_sheet = SpreadsheetApp.openById("<SpreadSheetID>");
var sheetA = spread_sheet.getSheetByName("sheetA");
var sheetB = spread_sheet.getSheetByName("sheetB");
// シートAのデータを検索して取得
var df_A = sheetA.getDataRange();
Logger.log(df_A.getValues());
// シートBのデータを検索して取得
var df_B = sheetB.getDataRange();
Logger.log(df_B.getValues());
}
取得したデータからヘッダ行を削除
function get_specific_dataframe_without_header() {
var spread_sheet = SpreadsheetApp.openById("<SpreadSheetID>");
var sheet = spread_sheet.getSheetByName("SheetB");
// シートのデータを検索して取得
var df = sheet.getDataRange();
var df_vals = df.getValues();
Logger.log(df_vals);
// shiftは要素を取り除くので、返り値はheaderが返ってくるので代入はしない
df_vals.shift()
Logger.log(df_vals);
}
トリガを利用して、データの行数を自動的に増やす方法
function insert() {
var spread_sheet = SpreadsheetApp.openById("<SpreadSheetID>");
var sheet = spread_sheet.getSheetByName("logger");
//Date型でオブジェクト生成
var date = new Date();
var time = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
// 最終行を取得
var last_row = sheet.getLastRow()
// 最終行+1が追記したい行数
sheet.getRange(last_row + 1, 1).setValue(time);
}
行や列を追加する方法
function insert_row_col() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread_sheet.getActiveSheet();
// 指定した行の1つ上に空白行を1行挿入
sheet.insertRows(1, 1)
// 指定した列の1つ右に空白列を1列挿入
sheet.insertColumns(1, 1)
}
1行おきに空白行を挿入する方法
function insert_even_row() {
var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread_sheet.getActiveSheet();
var last_row = sheet.getLastRow();
for (var i = 1; i < last_row; i++) {
if(i % 2 != 0){
// 奇数行の1つ下に空白行を1行挿入
sheet.insertRowsAfter(i, 1);
last_row++;
}
}
}
Google Drive
ドライブのフォルダ名を取得する方法
function drive() {
var folder = DriveApp.getFileById("<Drive ID>")
var folder_name = folder.getName();
Logger.log(folder_name)
}
ドライブのファイル名を取得する方法
function drive() {
var file = DriveApp.getFileById("<File ID>");
var file_name = file.getName();
// ファイルのタイプ
var file_mimetype = file.getMimeType();
Logger.log(file_name)
Logger.log(file_mimetype)
}
フォルダ内のファイル名を取得する方法
function drive() {
var folder = DriveApp.getFolderById("<Folder ID>");
var files = folder.getFiles();
// 未処理のファイルがある間はTrueが返る
while(files.hasNext()){
var file = files.next();
var name = file.getName();
Logger.log(name)
}
}
フォルダ内のフォルダ名を取得する方法
var folder = DriveApp.getFolderById("<Folder ID>");
var folders = folder.getFolders();
// 未処理のフォルダがある間はTrueが返る
while(folders.hasNext()){
var folder = folders.next();
var name = folder.getName();
Logger.log(name)
}
メールを送信する方法
function send_email() {
const to = 'aaaaa.aaaaa@gmail.com'
const subject = 'テストの件名'
const boby = 'スプレッドシートに変更がありました'
GmailApp.sendEmail(to, subject, boby)
}
const INPUT_SPREAD_SHEET_ID = "<SpreadSheet ID>"
const OUTPUT_DRIVE_FOLDER_ID = "<DriveID>"
const mian = () => {
// CSVに変換したデータを保存するためのGドライブフォルダを指定
const folder = DriveApp.getFolderById(OUTPUT_DRIVE_FOLDER_ID);
// 指定したスプレッドシートの個々のシート情報が配列で返る
const sheets = SpreadsheetApp.openById(INPUT_SPREAD_SHEET_ID).getSheets();
// 個々のシートをループで1つずつCSVに変換
for (const sheet of sheets) {
var sheet_name = sheet.getName() + '.csv';
// ファイルが既に存在しているかを判定
// 存在していない場合、NULLが返る / 存在している場合、ファイル名が返る
var is_exist = is_file_exist(folder, sheet_name)
Logger.log("[is_file_exist] returns " + is_exist)
if (is_exist !== null) {
Logger.log("[" + sheet_name + "] is alrady exist. So delete " + sheet_name)
delete_target = folder.getFilesByName(sheet_name).next();
delete_target.setTrashed(true)
}
var blob = write_csv(sheet, sheet_name);
folder.createFile(blob);
}
}
const is_file_exist = (folder, sheet_name) => {
const files = folder.getFilesByName(sheet_name)
while (files.hasNext()) {
const file = files.next();
if(sheet_name === file.getName()) {
return file
}
}
return null
}
const write_csv = (sheet, sheet_name) => {
// データのテーブルを配列として保存
var data = sheet.getDataRange().getValues();
// 2次元配列をカンマ区切りの文字列に変換
// これと同じ。let csv = ""; for(let i = 0; i < data.length; i++) {csv += data[i].join(',') + "\r\n";}
var csv = data.join('\n');
//Blobオブジェクトの作成
var blob = Utilities.newBlob(csv, MimeType.CSV, sheet_name);
return(blob)
}
