以下ページの転載になります。ご了承ください。

Spreadsheetでラクしたい Google Apps Script ソースコードテンプレート - Yuto Hongo Portfolio

[ひとことで言うと、こんな記事]

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
}

「もっとソースコードをこうしたらきれいに書けます」や「もっとこういう機能入れましょう」などございましたら、以下プルリクエスト等をよろしくお願いいたします。

google-apps-script-sample

最後までお読みいただき、誠にありがとうございます。様々なことを学んでいきたいと思っていますので、アドバイス等いただけると幸いです。