Go to Qiita Advent Calendar 2024 Top
LoginSignup
44
47

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@wapa5pow

GoogleスプレッドシートでKPIをSlackにも投稿できちゃうGoogle Apps Scriptの逆引きリファレンス

Last updated at Posted at 2016-04-23

image

逆引きで、Google Apps Scriptを使って、Spreadsheetを操作する方法を記します。

基本編

Apps ScriptをDriveから開く

  • Apps Scriptを開きたいときは、Spreadsheetのメニュー内の、Tool=>Script Editor...で開きます。

スクリプトが必要なスコープの確認

ファイル => プロジェクトのプロパティ

image

APIの公開

ただし、必要に応じてGoogle Cloud Platform側のAPIをEnableにする必要がある。

image

Driveフォルダ編

指定のフォルダ以下のファイルをすべて列挙する

var files = DriveApp.getFolderById(folderId).getFiles();
while (files.hasNext()) {
	var file = files.next();
	Logger.log(file.getName());
}

スプレッドシート(ワークシート)・シート編

スプレッドシートをコピーする

DriveAppsの方から行う。

var file = DriveApp.getFileById(fromId);
var folder = DriveApp.getFolderById(toFolderId);
file.makeCopy(name, folder);

IDからSpreadsheetを取得する

SpreadsheetのURLのhttps://docs.google.com/spreadsheets/d/abc1234567/にあるIDを使ってSpreadsheetを開けます。

var ss = SpreadsheetApp.openById("abc1234567");

シート名からSheetを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1');

シートをActive状態にする

sheet.activate()

Activeなシートを削除する

sheet.deleteActiveSheet()

新しいシートを足す

以下では新規シートがあるかどうか確認して、なければ作成してます

  var directionsSheet = spreadsheet.getSheetByName(sheetName);
  if (directionsSheet) {
    directionsSheet.clear();
    directionsSheet.activate();
  } else {
    directionsSheet =
        spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());
  }

シートを複製する

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.duplicateActiveSheet();

異なったスプレッドシートにシートをコピーする

var fromSheet = ss.getSheetByName('Sheet1');
var toSS = SpreadsheetApp.openById(fromId);
fromSheet.copyTo(toSS)

スプレッドシートの名前を変更する

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setName('TestName')

シートの名前を変更する

var sheet = ss.getSheetByName('Sheet1');
sheet.setName('TestSheetName')

スプレッドシート内のシート名をすべて取得する

ss.getSheets()

セル編

データの取得

以下のようにすればdataに配列でセルの内容が取得できます。

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();

特定のセルのデータを取得

var value = sheet.getRange("A4").getValue();

データの追加

シートのデータがある最後に追加したいときは以下のようにします。

var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);

データの更新

function setSellValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var cell = sheet.getRange("B2");
  cell.setValue(100);
}

セルに計算式を設定

function setFormulaToCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var cell = sheet.getRange("B13");
  cell.setFormula("=SUM(B2:B12)");
}

固定行・列を設定

# 行の設定: rowsは行数
setFrozenRows(rows)	

# 列の設定: columnsは列数
setFrozenColumns(columns)

セルのフォーマットを整える

以下な感じでセルのフォーマットをいろいろ整えれます

directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');
directionsSheet.getRange('A1:2').setFontWeight('bold');
directionsSheet.setColumnWidth(1, 500); directionsSheet.getRange('B2:C').setVerticalAlignment('top');
directionsSheet.getRange('C2:C').setNumberFormat('0.00');

列を追加する

以下だとB列とC列の間に1行足す

sheet.insertColumnAfter(2)

行を追加する

sheet.insertRowAfter(2)

行をコピーする

fromRowはコピー元の行数を、fromRowはコピー先の行数を表す。
Rangeは、例えば、getRangeの引数に、16:16とすると、16行すべてを選択することができる。

相対的な値のフォーミュラも相対的にコピーできるので便利。

sheet.getRange(fromRow + ':' + fromRow).copyTo(ss.getRange(toRow + ':' + toRow));

シート内のすべての値を2D配列で取得する

sheet.getDataRange().getValues();

シート内の特定の値を2D配列で取得する

sheet.getSheetValues(2, 1, 42, 2)

プロパティ編

Properties Serviceというのがあって、スクリプトで使う設定値を格納することができます。
設定はメニューの ファイル -> プロジェクトのプロパティ から出来ます。

image

  • Script Properties: スクリプト全体にかかわる設定
  • User Properties: ユーザに応じた設定
  • Document Properties: ソースURLなどのドキュメントの情報
PropertiesService.getScriptProperties().

UI編

スプレッドシートにボタンを設置する

詳しくはここを見ていただければと思いますが、以下のようにAppsScriptを実行できるボタンを設置できます。

image

セルのデータの入力を特定の範囲にしぼりたいとき

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  var cell = SpreadsheetApp.getActive().getRange('D4');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

範囲外の入力が来たときは、警告がでます。

image

カスタムメニューを作成

ダイアログを出す

以下でダイアログが出せます。

function showMessageBox() {
  Browser.msgBox('ダイアログ!');
}

image

入力可能なダイアログを表示する

function showInputMessageBox() {
  var selectedRow = Browser.inputBox('数値を入れてください', Browser.Buttons.OK_CANCEL);
  if (selectedRow == 'cancel') {
    return;
  }
  Browser.msgBox(selectedRow);
}

カスタムメニューを表示する

こんな感じのができます。

image

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}
  ];
  spreadsheet.addMenu('Directions', menuItems);
}

グラフ編

グラフを作成する

Sheet1にのA1:B15にデータがあるとすると、以下のコードで棒グラフが作成できます。

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

image

イベント編

Spreadsheetオープン時に実行

onOpenはスプレッドシートが開いたときに実行されるので、何か初期化などしたいときは、実行するといいでしょう。

function onOpen() {
}

その他のトリガーとして以下があります。なお、SpreadsheetではdoGetとdoPostはありません。

  • onEdit: スプレッドシートの値を変えたとき
  • onInstall: アドオンがインストールされたとき
  • doGet: Web AppにHTTP GETリクエストをしたとき
  • doPost: Web AppにHTTP POSTリクエストをしたとき

定期ジョブ編

cron設定

まず、日時が設定できる関数を設定します。

function setDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('CurrentDate');
  var cell = sheet.getRange("A1");
  var date = new Date();
  cell.setValue(date);
}

つぎにその関数に対して、cronを設定します。
時計っぽいアイコンを選択します。

image

Add a new triggerを設定します。今回は毎分実行されるように設定しました。

image

結果、1分ごとに更新される時計ができました。

image

エクスポート編

CSVとしてエクスポートする

Convert Google Documents and Spreadsheets with Apps Scriptに書いてあります。

なおexport出来るタイプはここによると、以下のようです

image

PDFとしてエクスポートする

HTMLとしてエクスポートする

外部ライブラリを使うとできます。これでスプレッドシートをHTMLに変換してメールで送信ということができます。

外部連携

Slack連携

Google Apps Scriptを使って、スプレッドシートの内容をSlackに通知するを参考にするとできます。

Botのようにしたい場合は以下らへんを見るとよさそうです。

外部からのデータ取得

今回は、天気を取得し、それをslackになげてみます。【Google Apps Script】天気予報をWeb APIで取得する方法を参考にしました。

出力フォーマットは「東京の今日の天気は曇り。最高気温は23度でしょう」でいきます。

function weatherForecast() {
  var response = UrlFetchApp.fetch("http://weather.livedoor.com/forecast/webservice/json/v1?city=130010"); //URL+cityID
  var json = JSON.parse(response.getContentText());
  var message = json.location.city + "の" + json.forecasts[0].dateLabel + 'の天気は' + json.forecasts[0].telop + '。最高気温は' + json.forecasts[0].temperature.max.celsius + '度でしょう';
  slack(message);
}

できました。
image

メール送信

wapa5pow@example.comがtoになって、メールが送られます。

var email = 'wapa5pow@example.com';
GmailApp.sendEmail(email, 'subject', 'body');

Fromを変えたい場合は別のアドレスからメールを送信するにやり方がかいてあります。

デバッグ編

ログを出す

Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);

Script Editor内で、View(表示) => Logs(ログ) を選ぶとログが見えます。
また、View(表示) => 実行トランスクリプト を選ぶと実際にどの関数がよばれたかなどの実行ログが出ます。

image

ブレークポイントを設定してデバッグ実行する

関数ごとにデバッグができます。オブジェクトの値を見ることしかできないっぽいですが、それでも便利そうです。
ステップ実行などもできます。
コンソールみたいなっていて、ブレークポイント上で、実行できると便利そうですがいまはできません。

image

image

開発編

ローカルで開発する

IntelliJでコード補完する

設定の、Languages & Frameworks > JavaScript > Libraries から、Download... でgoogle-apps-scriptを入れればいい。

image

外部ライブラリ

2D Array Library

2D配列を扱いやすくしてくれます。転置も楽にしてくれるので便利です。

underscoreGS

mapやeachなどが使えるようになります。

BetterLog

BetterLogはスクリプト実行ログをスプレッドに出せるようになります。
これでRubyからApps Scriptを実行してもログがたまっていくようになります。

参考

44
47
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
44
47

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?