Edited at

GASでよく使うコード集: 逆引き辞典風

タイトルどおり、コードのメモ集です。

随時追加していく予定です。

間違っている点や不足点ありましたら、お気軽にご指摘お願いいたします!


イベントハンドラ

くわしい引数の中身は→イベントオブジェクト

/**

* 閲覧権限のあるユーザが、このスクリプトが紐付けられたファイルを開いた時
*/

function onOpen(e) { }

/**
* スプレッドシートで任意のセルの値を変更した時
*/

function onEdit(e) { }

// その他、あまり使わないの↓
// ユーザがアドオンをインストールした時
function onInstall(e) { }
// APIとして公開されているGASに対してhttp/GETリクエストを行った時
function doGet(e) { }
// APIとして公開されているGASに対してhttp/POSTリクエストを行った時
function doPost(e) { }


シート情報を取得する


シートのいろいろな参照

// アクティブなシートの取得

SpreadsheetApp.getActiveSpreadsheet();

// シート名を指定して取得
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名');

// 別のスプレッドシートのデータを取得する
// ①IDから取得
SpreadsheetApp.openById('1xxxxxxxxxxY');
// ②URLから取得
SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/' + '1xxxxxxxxxxY
/edit#gid=0000000
');


getActiveCell 実行中のセルを探す

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var activeCell = sheet.getActiveCell();


getLastRow getLastColumn 対象となるシートの最終行/列を取得

sheet.getDataRange().getLastRow(); // 最終行

sheet.getLastColumn(); // 最終列
sheet.getRange('B:B').getValues().filter(String).length; // B列の最終行


getColumn セルの対象カラムを探す

activeCell.getColumn() == '1' // A列

activeCell.getColumn() == '2' // B列


getValue 値を取得する

activeCell.getValue(); // チェックボックスの場合true||false、その他は文字列など

// 実行列と同列の別カラムの値を取得
var rowNum = activeCell.getRow();
sheet.getRange('A' + rowNum).getValue();
sheet.getRange('B' + rowNum).getValue();
sheet.getRange('C' + rowNum).getValue();


getValues 値を配列で取得する

sheet.getDataRange().getValues(); //多次元配列になっている

/**
* タイトル名からデータを列を取得する関数
* 列が追加されてもデータが狂わないようにするため
*/

function titlRowData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
// タイトルの書かれている行を指定する(この場合1列目なので配列の0番目を指定)
var data = sheet.getDataRange().getValues()[0];
var alfabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
return {
a: alfabet[data.indexOf('A列タイトル')] || '',
b: alfabet[data.indexOf('B列タイトル')] || '',
c: alfabet[data.indexOf('C列タイトル')] || '',
d: alfabet[data.indexOf('D列タイトル')] || ''
}
}

// データの目次行から、行数を調べる
var index = {
no: data[0].indexOf('番号') || 0,
name: data[0].indexOf('') || 0
};


スプレッドシートを編集する


setValue 値の書き換え

// ターゲットセルを書き換える

sheet.getRange('A1').setValue('hogehoge');

// 値を保存したい場合はシートに書き込む
var hoge = 'hoge';
hoge = 'hogefuga'; // これでは関数内でしか有効じゃない
// シートに書き込めばいつでも変更した値が取り出せる
sheet.getRange('A1').setValue('hoge'); // value = 'hoge'
sheet.getRange('A1').setValue(sheet.getRange('A1').getValue + 'fuga'); // value = 'hogefuga'


insertRow 行を追加する

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// 指定行の後ろに追加
sheet.insertRowAfter(1); // 新しい2行目ができる

// 指定行の前に追加
sheet.insertRowBefore(1); // 新しい1行目ができる


setBorder 指定の範囲にボーダーを付ける

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// 範囲を指定 (例: sheet.getRange('A1:F1'))
var target = sheet.getRange('A' + targetRow + ':' + sheet.getLastColumn() + targetRow)

// setBorder(上, 左, 下, 右, 垂直, 水平, 線の色, 線の種類);
// 線の色, 線の種類は省略可。だがどちらかだけにすることはできないので、引数は6個 or 8個となる;
// SpreadsheetApp.BorderStyle.DASHED(破線)
// SpreadsheetApp.BorderStyle.DOTTED(点線)
// SpreadsheetApp.BorderStyle.SOLID(Default)(実践)

target.setBorder(true, true, true, true, true, true); // 全て線で囲む
target.setBorder(true, false, false, false, false, false); // 上線だけ付ける
target.setBorder(false, false, false, false, false, true); // 上下線だけ付ける
target.setBorder(false, false, false, false, false, true, '#ff0000', SpreadsheetApp.BorderStyle.DOTTED); // 赤色の点線を付ける


プルダウンを作る

var pullDown = SpreadsheetApp.newDataValidation();

pullDown.requireValueInList(['いち', '', 'さん'], true); // 第一引数:選択肢の配列,第二引数:プルダウンを見せるか否か
sheet.getRange('A1').setDataValidation(pullDown);


msgBox モーダル(popup)を出す

uiクラスからhtmlで作成したpopupを作ることもできますが、

一番簡単なpopupはmsgBoxで出せます。

htmlを表示するモーダルの表示方法は下で紹介しています。


いろいろな種類のモーダル

Browser.msgBox('popupです');

Browser.msgBox('1選択 OK', Browser.Buttons.OK);
Browser.msgBox('1選択 はい', Browser.Buttons.YES);
Browser.msgBox('1選択 いいえ', Browser.Buttons.NO);
Browser.msgBox('1選択 キャンセル', Browser.Buttons.CENCEL);
Browser.msgBox('2選択', Browser.Buttons.OK_CANCEL);
Browser.msgBox('2選択', Browser.Buttons.YES_NO);
Browser.msgBox('3選択', Browser.Buttons.YES_NO_CENCEL);
Browser.msgBox('タイトル', 'サブタイトルは第2引数', Browser.Buttons.OK);
Browser.inputBox('文字入力画面');


モーダルで押されたボタンを調べる

var popup = Browser.msgBox('2選択', Browser.Buttons.OK_CANCEL);

if (popup == 'ok') {
Browser.msgBox("okを押しました");
} else if (popup == 'cancel') {
Browser.msgBox("キャンセルを押しました");
} else if (popup == 'no') {
Browser.msgBox("いいえを押しました");
} else if (popup == 'yes') {
Browser.msgBox("はいを押しました");
}


モーダルに入力画面を出す

var age = Browser.inputBox('あなたの年齢は?');

Browser.msgBox(age + '');


メニューバーに追加する

/**

* onOpen ファイルを開いたときのイベントハンドラ
*/

function onOpen(e) {
var ui = SpreadsheetApp.getUi(); // Uiクラスを取得する
var menu = ui.createMenu('スクリプト'); // Uiクラスからメニューを作成する
// メニューにアイテムを追加する
menu.addItem('テスト', 'hoge');
menu.addToUi(); // メニューをUiクラスに追加する
}

function hoge() {
// メニュー選択時実行する関数
}


HtmlService HTMLを表示する


createHtmlOutput 一番簡単な表示のしかた

var html = HtmlService

.createHtmlOutput('<p><a href="http://hoge" target="blank">リンク▶</a></p>')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(150)
.setHeight(150);


createTemplateFromFile htmlファイルを表示する


html/sumple.html

<!DOCTYPE html>

<html>
<head>
<base target="_top">
<style type="text/css">
body {
font-size: 11px;
}
h2 {
background-color: #FFFFAA;
}
}
</style>
</head>
<body>
<h1>サンプル</h1>
サンプルページです
</body>
</html>


sumple.gas

var html = HtmlService

.createTemplateFromFile('html/sumple')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME);


showSidebar 画面横に小窓でHTMLファイルを表示する


sumple.gas

var html = HtmlService

.createTemplateFromFile('html/sumple')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle('サンプル');

SpreadsheetApp.getUi().showSidebar(html);



showModelessDialog サイズ指定したポップアップを表示する

var html = HtmlService

.createHtmlOutput('<p><a href="http://hoge" target="blank">リンク▶</a></p>')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(150)
.setHeight(150);

SpreadsheetApp.getUi().showModelessDialog(html, 'リンク付きpopup');


ファイル自体を作成・移動する


ファイルのコピーを作成する

// ここではDocsデータだが、スプシでもなんでも変わりません

var file = DriveApp.getFileById('元とするファイルのID');

// コピーを作成
var copyFile = file.makeCopy('コピー');

// コピーファイルの中身を参照
var id = copyFile.getId();
var body = DocumentApp.openById(id).getBody();


特定のディレクトリに保存する

var file = DriveApp.getFileById('ID');

// IDはフォルダを開いた際のURLから取得
var folderTarget = DriveApp.getFolderById('対象のフォルダのID');
folderTarget.addFile(file);


特定のディレクトリから削除する

var file = DriveApp.getFileById('ID');

// IDはフォルダを開いた際のURLから取得
var folderTarget = DriveApp.getFolderById('対象のフォルダのID');
folderTarget.removeFile(file);


Docsデータをいじる


Docsを取得する

var docFile = DocumentApp.openById(ORDER_DOCS_ID); // ドキュメントをIDで取得①

var docText = docFile.getBody().getText(); // ドキュメントの内容を取得

var file = DriveApp.getFileById('元とするファイルのID');// ドキュメントをIDで取得②


Docsを新しく作る

var docs = DocumentApp.create(['test-Docs']);

var ssId = docs.getId();
var file = DriveApp.getFileById(ssId);


Docsの中身を書き換える

var timeStamp = Utilities.formatDate(new Date(), "JST", "yyyy年MM月dd日");

var body = DocumentApp.openById(`ID`).getBody();

// ファイル内に特定の文字列を書き込んでおく
body
.replaceText('{time}', timeStamp)
.replaceText('{sumple}', 'サンプル')
.replaceText('{hoge}', 'hoge');


メール連携


メールを送る


  • sendEmail(message)

  • sendEmail(recipient, subject, body)

  • sendEmail(recipient, subject, body, options)

  • sendEmail(to, replyTo, subject, body)

MailApp.sendEmail('メールアドレス', '件名', '本文');


cc(bcc)に追加する

GmailApp.sendEmail('メールアドレス', '件名', '本文', {

cc: 'ccメールアドレス',
bcc: 'bccメールアドレス'
});


送り元のアドレスを変更する

自身の持つメアドとは別のアドレスから送信することができます。

それには自身のgmailに送信元のメアドを追加する必要があります。

公式サイトのヘルプより手順 1: 所有しているアドレスを追加するの内容を行ってください。

GmailApp.sendEmail('メールアドレス', '件名', '本文', {

from: '送信元のメールアドレス',
name: '送信元の任意の名前(なければメアドで表示される)'
});