Spreadsheetでラクしたい Google Apps Script ソースコードテンプレート
[ひとことで言うと、こんな記事]
SpreadSheetでの情報の【取得】【更新】の基本的なメソッドをあらかじめGASのソースとして、準備しました！
[こんな人におすすめ]
- 表計算ソフトでのデータ処理の作業がつらいと感じている方
- データを編集するような作業を自動化したいと感じている方
このテンプレートで少しでも楽になる方が増えればと思います。
[目次]
- 定常作業がめんどくさいので、Google Apps Scriptで自動化したい
- 準備したSpreadsheet編集のサンプルコードの概要
- 実際のサンプルコード
定常作業がめんどくさいので、Google Apps Scriptで自動化したい
皆さんも、データ入力や編集の作業って普段から行ってたりしないでしょうか？ 特になにかのデータのマスター管理とかをなさっていたりする方は多いと思います。
__本当にめんどくさい__です。
実は、Googleに準備されているサービスのいくつかは、Google Apps Scriptというものでなにかしらの処理をしてもらうことが可能です。
Apps Script | Google Developersが公式サイトとなります。
TOPページをみただけでも、様々なサービスに対して利用できますね。
例
- Spreadsheet
- Gmail
- Google Drive
- Google Form
- etc...
今回はSpreadSheetのデータ処理を行う際に、最初に準備しておくと作業が楽になりそうなソースコードのテンプレートを準備させていただきました。
準備したスプレッドシート編集のサンプルコード概要
GASの実行はV8ランタイムが前提
V8 Runtime Overview | Apps Script | Google Developers
少し前にアップデートされたそうです。
スプレッドシートの1行目はテーブルヘッダ
| Column1 | Column2 | Column3 | Column4 | … |
|---|---|---|---|---|
| data1-1 | data1-2 | data1-3 | data1-4 | … |
| … | … | … | … | … |
各カラムにどんな情報が入っているのかの説明行としておいてある想定です。
1行のデータをモデルとして捉える
1行分のデータをモデルとして定義しています。今回は column1~4 + SpreadSheet用のデータをメンバとして定義しています。
必要最低限の情報取得、書込の機能だけを記述
以下のような機能のみを記述していたりします。
情報の取得
- resolveAll … スプレッドシート内にすでにある情報をすべて取得し、 Modelの配列 として返却
- resolveByRowNumber … スプレッドシート内で○○番目にある情報を取得し、 Model として返却
情報の書込
- storeAll … Modelの配列 をすべてシートに書き込む
- storeByRowNumber … Model をスプレッドシート内で○○番目に書き込む
実際のサンプルコード
以下に実際のサンプルコードを記述していきます。
main.gs
// Get Endpoint
function doGet(e) {
}
// Post Endpoint
function doPost(e) {
}
// Main
function main() {
// TODO:処理の記述
}model.gs
/**
* SpreadSheetの1行を表したModelの準備
*/
class _Model {
// TODO:必要なカラムに応じて準備
constructor(
column1,
column2,
column3,
column4
) {
this.column1 = column1
this.column2 = column2
this.column3 = column3
this.column4 = column4
this.sheetRow = [
column1,
column2,
column3,
column4,
]
}
}modelRepository.gs
/**
* SpreadSheetの情報を取得/編集する処理
* （※ 必要に応じて準備してください）
*/
/**
* 情報をすべてModelとして取得する
*/
function _modelRepository_resolveAll(sheet) {
var startRow = 2 // 1行目がテーブルヘッダ
var startCol = 1
var lastRow = sheet.getLastRow()
var numRows = lastRow - 1 // 1行目がテーブルヘッダ
var lastCol = sheet.getLastColumn()
// まだ情報がない場合
if (numRows === 0) return []
// TODO:必要なカラムに応じて準備
var sheetDatas = sheet.getSheetValues(startRow, startCol, numRows, lastCol)
return sheetDatas.map(sheetRow => new _Model(sheetRow[0], sheetRow[1], sheetRow[2], sheetRow[3]))
}
/**
* 上から○○番目のデータをModelとして取得
*/
function _modelRepository_resolveByRowNumber(sheet, rowNumber) {
var startRow = rowNumber + 1 // 1行目がテーブルヘッダ
var startCol = 1
var numRows = 1
var lastCol = sheet.getLastColumn()
// TODO:必要なカラムに応じて準備
var sheetDatas = sheet.getSheetValues(startRow, startCol, numRows, lastCol);
var models = sheetDatas.map(sheetRow => new _Model(sheetRow[0], sheetRow[1], sheetRow[2], sheetRow[3]))
return models[0]
}
/**
* すべてのModelをSpreadSheetに保存する
*/
function _modelRepository_storeAll(sheet, models) {
// 既存データをすべて消去
_truncateData(sheet)
var startRow = 2 // 1行目がテーブルヘッダ
var startCol = 1
var numRows = models.length
var lastCol = sheet.getLastColumn()
// 更新情報がない場合
if (numRows !== 0) {
var updateSheetRange = sheet.getRange(startRow, startCol, numRows, lastCol)
var sheetRows = models.map(model => { return model.sheetRow })
updateSheetRange.setValues(sheetRows)
}
}
/**
* 上から○○番目にModelの情報を保存する
*/
function _modelRepository_storeByRowNumber(sheet, rowNumber, model) {
var startRow = rowNumber + 1 // 1行目がテーブルヘッダ
var startCol = 1
var numRows = 1
var lastCol = sheet.getLastColumn()
var updateSheetRange = sheet.getRange(startRow, startCol, numRows, lastCol)
var sheetRows = [model.sheetRow]
updateSheetRange.setValues(sheetRows)
}
/**
* シート情報をすべてクリアする
*/
function _truncateData(sheet) {
var startRow = 2 // 1行目がテーブルヘッダ
var startCol = 1
var lastRow = sheet.getLastRow()
var numRows = lastRow - 1 // 1行目がテーブルヘッダ
var lastCol = sheet.getLastColumn()
// まだ情報がない場合
if (numRows !== 0) {
var clearSheetRange = sheet.getRange(startRow, startCol, numRows, lastCol)
clearSheetRange.clearContent()
}
}sheet.gs
/**
* 編集対象のSpreadSheetの情報を取得する
*/
function _getSheet() {
// TODO:利用するSpreadSheetに応じて準備
var SHEET_URL = '[SpreadsheetのURL]';
var spreadSheetPage = SpreadsheetApp.openByUrl(SHEET_URL);
var sheet = spreadSheetPage.getSheetByName('[対象シートの名前]');
return sheet
}「もっとソースコードをこうしたらきれいに書けます」や「もっとこういう機能入れましょう」などございましたら、以下プルリクエスト等をよろしくお願いいたします。
__一緒に様々なことを学んでいく仲間を募集__しています。
このサイトのお問い合わせなどからご連絡いただけると幸いです。