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('タイトル', 'サブタイトルは第２引数', Browser.Buttons.OK);
Browser.inputBox('文字入力画面');

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

var popup = Browser.msgBox('２選択', 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: '送信元の任意の名前(なければメアドで表示される)'
});