メインコンテンツまでスキップ

Google Apps Script

Introduction

用語

  • SpreadSheetApp --- Google Spreadsheet アプリ
  • SpreadSheet --- 個々のファイル
  • Sheet --- タブ、ワークシート
  • Range --- 選択範囲

アクティブとは

  • active sheet --- 表示中のシート
  • active range --- 表示中のシートの中で、ハイライトされている箇所
  • active cell --- 表示中のシートの中で、フォーカスされている単一のセル

カスタムファンクション

/**
* ドルを日本円に変換する
*
* @param {number} dollars ドル
* @return {number} 日本円
* @customfunction
*/
function USDTOJPY(dollars) {
const rate = 0.99;
const swissFrancs = dollars * rate;
return 'JPY' + swissFrancs;
}

以上をスクリプトとして記述した後、セルに=USDTOJPY(123)とするだけで使える。

基本

SpreadSheet

// 取得
const spreadsheet =
SpreadsheetApp.getActiveSpreadsheet() || spreadsheet.getSheetByName('name');
// リネーム
spreadsheet.rename('2017 Avocado Prices in Portland, Seattle');
// シートの追加
spreadsheet.insertSheet(name);

Sheet

// 取得
const sheet = SpreadsheetApp.getActiveSpreadsheet();
// 複製
sheet.duplicateActiveSheet();
// リネーム
sheet.setName('MySheet');
// リサイズ
sheet.autoResizeColumns(1, 5);
// 固定行の設定
sheet.setFrozenRows(2);
// id取得
sheet.getSheetId();
// 最終行を取得
sheet.getLastRow();
// アクティブにする
sheet.activate();

Range

// F2より下にあるすべての値を、C2の位置に移動する
const range = sheet.getRange('F2:F');
range.moveTo(sheet.getRange('C2'));

// 指定した範囲の値について、3番目の列の値で並べ替える
const range = sheet.getRange('A3:D55');
range.sort(3);

// 範囲をずらしたり広げたりする
range.offset(
0, // 行方向のオフセット
0, // 列方向のオフセット
range.getHeight(), // 高さ
range.getWidth(), // 幅
);

// 列数、行数を取得する
range.getNumRows();
range.getNumColumns();

// - バンディング(配色の組み合わせ)を設定する
// - 例えば行ごとに交互に色をつけるなど
// - すでにバンディングがあるとエラーになるので、チェックしてから設定する
if (!range.getBandings()[0]) {
range.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false);
}

// スタイル
range
.setFontWeight('bold')
.setFontStyle('italic')
.setFontColor('#ffffff')
.setBackgroundColor('#007272')
.setNumberFormat('mmmm dd, yyyy (dddd)')
.setBorder(
true,
true,
true,
true,
null,
null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM,
);

メニューを作る

onOpen()という特殊な関数を使う。

function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('メニュー名')
.addItem('メニュー項目名', 'functionNameYouWantToExec')
.addSeparator()
.addItem('...', '...')
.addSeparator()
.addToUi();
}

他のスプレッドシートからデータを持ってくる

// コピー
var sourceSS = SpreadsheetApp.openById(
'1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo',
);
var sourceSheet = sourceSS.getSheetByName('target-sheet-name');
var sourceRange = sourceSheet.getDataRange();
var sourceValues = sourceRange.getValues();

// ペースト
var destSheet = SpreadsheetApp.getActiveSheet();
destSheet
.getRange(1, 1, sourceRange.getHeight(), sourceRange.getWidth())
.setValues(sourceValues);

範囲内の値に一括処理をする

const range = SpreadsheetApp.getActiveRange();

// 2次元配列として値を取得
const values = range.getValues();

const updatedValues = values.map((row) => {
const [col1, col2, ...rest] = row;
return [col1, 'updated col2', ...rest];
});
range.setValues(updatedValues);

API からのデータ取得

UrlFetchAppを使う

function fetchBookInfo(ISBN) {
const url =
'https://openlibrary.org/api/books?bibkeys=ISBN:' +
ISBN +
'&jscmd=details&format=json';
const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
const json = response.getContentText();
const bookInfos = JSON.parse(json);
const bookInfo = bookInfos['ISBN:' + ISBN];

return bookInfo;
}

Utils

列名を元に列番号を取得する

function columnIndexOf(findingColumnName) {
var sheet = SpreadsheetApp.getActiveSheet();
var columnHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var [columnNames] = columnHeaders.getValues();
const index =
columnNames.findIndex((columnName) => columnName === findingColumnName) + 1;
return index;
}

SpreadSheet と Sheet で共用されているメソッドの動作の違い

// 特定のシートにおいて、3つめの列を削除する
Sheet.deleteColumn(3);

// アクティブなシートにおいて、3つめの列を削除する
Spreadsheet.deleteColumn(3);
// これは下記と等価
Spreadsheet.getActiveSheet().deleteColumn(3);

モーダル

const htmlOutput = HtmlService.createHtmlOutput('<p>HTMLで記述できます</p>')
.setHeight(120)
.setWidth(350),
SpreadsheetApp.getUi().showModalDialog(
htmlOutput,
'Created a presentation!',
);

チャートの作成

const sheet = SpreadsheetApp.getActiveSheet();
const chartDataRange = sheet.getRange('A2:F102');
const lineChartBuilder = sheet.newChart().asLineChart();
const chart = lineChartBuilder
.addRange(chartDataRange)
.setPosition(5, 8, 0, 0)
.setTitle('USD Exchange rates')
.setNumHeaders(1)
.setLegendPosition(Charts.Position.RIGHT)
.setOption('hAxis', {
slantedText: true,
slantedTextAngle: 60,
gridlines: {
count: 12,
},
})
.build();

sheet.insertChart(chart);

チャートをスライドに貼り付け

const slides = SlidesApp.create('slide-title');
slides.getSlides()[0].remove(); // デフォルトページを削除
sheet.getCharts().forEach((chart) => {
var newSlide = slides.appendSlide();
newSlide.insertSheetsChart(chart);
});