GAS備忘録（その2・スプレッドシート・ドキュメント・ドライブ）

Reference

目次

1. スプレッドシート
   1. Spreadsheetサービスの概要
   2. スプレッドシートからデータを取得する
   3. スプレッドシートにデータを書き込む
   4. UIのカスタマイズ
   5. カスタム関数とデータ検証ルール
2. ドライブ
   1. 作成
   2. 取得
   3. 検索
3. ドキュメント

スプレッドシート

GASを使ってスプレッドシートの作成や変更ができます

基本的にはコンテナバインドで利用します
スプレッドシートから「拡張機能」から 「 Apps Script」を選択してエディタを開きます

Google スプレッドシート

Spreadsheetサービスの概要

• SpreadsheetApp : グローバルオブジェクト（トップレベルのオブジェクト）
  直下のSpreadsheetの場合やアクティブなSheetやRangeを取得するメソッドがあります
• Spreadsheet： スプレッドシートを操作するメソッドを持つクラス
• Sheet ：シートの操作やセル範囲を取得するメソッドを持つクラス
• Range：セルの範囲を操作するメソッドを持つクラス

SpreadsheetApp→Spreadsheet→Sheet→Range
ポイント：関数の戻り値が何か？ Spreadsheet→Sheet→Range

スプレッドシートからデータを取得する

• SpreadsheetAppクラスメソッド
  • getActiveSpreadsheet()：戻り値は「現在アクティブなSpreadsheet」
  • getActiveSheet()：戻り値は「現在アクティブなsheet」
  • openById(id)：IDを指定・戻り値は「Spreadsheet」
  • openByUrl(url)：URLを指定・戻り値は「Spreadsheet」
• Spreadsheetクラスメソッド
  • getSheetByName(name)：シート名を指定・ 戻り値 は「sheet」
  • getSheets() ：戻り値 は「Sheet[]」（シートの配列）
• Sheetクラスメソッド
  • getDataRange()：戻り値は「Range」(データが存在するセル範囲)
  • getRange(row, column, numRows, numColumns)：戻り値は「Range」（セル範囲を指定）

• Rangeクラスメソッド
  • getValue()：戻り値は「Object」（セルの値）
  • getValues()：戻り値は「Object[][]」（セル範囲の値）

下記の表のA列とB列を取得してログ出力します
重要：getValues()で「セル範囲の値を取得」して、ループ処理することで、実行時間を短縮できます

function sheetInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  console.log(data)
  for (var i = 0; i < data.length; i++) {
    console.log('A列:' + data[i][0]);
    console.log('B列:' + data[i][1]);
  }
}

// console.log(data)の結果　2次元配列
[ [ 'A1', 'B1' ],
  [ 'A2', 'B2' ],
  [ 'A3', 'B3' ],
  [ 'A4', 'B4' ],
  [ 'A5', 'B5' ],
  [ 'A6', 'B6' ] ]

スプレッドシートをIDで取得する

function getSpreadsheetById(spreadsheetId) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  return spreadsheet;
}
const spreadsheetId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
const spreadsheet = getSpreadsheetById(spreadsheetId);

「緑の枠線で囲まれた部分」を範囲として取得します
getRange(row, column, numRows, numColumns)で「セル範囲を指定」します
＊整数値で指定します

• row ：範囲の開始行インデックス（行のインデックスは 1から始まります）
• column：範囲の開始列インデックス（列のインデックスは1から始まります）
• numRows ： 取得したい範囲の行数（省略可能）
• numColumns ： 取得したい範囲の列数（省略可能）
  ＊省略した場合は1行または1列を取得

//var data = sheet.getDataRange().getValues();の部分を下のコードに変更する

var data = sheet.getRange(1, 1, 3, 2).getValues();

getRang()の引数について

• getRange(2, 2) ：[ [ ‘B2’ ] ] 指定された座標の左上のセルを含む範囲を返します
• getRange(2, 2, 2) ：[ [ ‘B2’ ], [ ‘B3’ ] ] 指定された座標の左上のセルと指定された行数の範囲を返します
• getRange('B2') ：[ [ ‘B2’ ] ]
• getRange('A1:B3') ：[ [ ‘A1’, ‘B1’ ], [ ‘A2’, ‘B2’ ], [ ‘A3’, ‘B3’ ] ]
• getRange('2:2')： A2 B2・・・・・２行目を全て
• getRange('B:B')： B1 B2 B3 B4 B5・・・・B列を全て
  

Sheetクラスメソッド（戻り値は整数値）

• getLastColumn() ：コンテンツのある最後の列の位置を返します
• getLastRow() ： コンテンツのある最後の行の位置を返します
• getMaxColumns() ： シートの現在の列数を返します（＊コンテンツの有無は関係なく）
• getMaxRows() ： シートの現在の行数を返します（＊コンテンツの有無は関係なく）

スプレッドシートにデータを書き込む

appendRowメソッドを使ってデータ範囲の最終行に書き込む

最終行に書き込む
sheet.appendRow()：一行ずつ追加しています

 let data =[['A7', 'B7'],['A8', 'B8'],['A9', 'B9']]
 var sheet = SpreadsheetApp.getActiveSheet();
 for (var i = 0; i < data.length; i++) {
   sheet.appendRow(data[i]);
 }

setValue・setValuesメソッドを使ってシートの範囲を指定してデータを書き込む

範囲を指定して書き込む
range.setValues()：配列を一括で追加します

let data =[['A7', 'B7'],['A8', 'B8'],['A9', 'B9']]
var sheet = SpreadsheetApp.getActiveSheet();
let range = sheet.getRange('A7:B9');
range.setValues(data)

//範囲指定に.getLastRow()を使う
let data =[['A7', 'B7'],['A8', 'B8'],['A9', 'B9']]
let start = sheet.getLastRow()+1;
let row = data.length;
let col = data[0].length;
let range = sheet.getRange(start, 1, row, col);
range.setValues(data);

シートをクリアする

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

//シート内のコンテンツだけをクリアする
sheet.clearContents();

Rangeクラス

UIのカスタマイズ

setBackground(color) : 範囲内のセルの背景色を設定します
setFontColorObject(color)：範囲内のフォンの色を設定します

function setBg() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange('A1:B2');
  cell.setBackground('#ccc')
}

クリック（GASの関数の実行）可能な「画像」または「描画」を埋め込みます
＊コンテナバインドのみ

function showMessageBox() {
　　Browser.msgBox('Hey!!');
}

1. スプレッドシートの 「挿入」から 「画像」または「描画」を選択して挿入
2. 「画像」または「描画」をクリックしドロップダウンメニューの「スクリプトの割り当て」を選択
3. 表示されるダイアログ ボックスで、実行するGAS関数名を入力して「 OK」をクリック

カスタム関数とデータ検証ルール

カスタム関数について
＊カスタム関数を使用できるのはコンテナバインドのスプレッドシートのみです

スプレッドシートには「COUNTIF関数」「VLOOKUP関数」「QUERY関数」が使用できますが、
GASに「カスタム関数」を作成することもできます
＊カスタム関数と通常の関数を区別するには、通常の関数名の先頭にアンダースコア（_）をつけてプライベート関数にします

カスタム関数はスプレッドシートのセルで「 =関数名(引数)」で呼び出します

• カスタム関数に戻り値が設定されている：その値がセルに表示されます
• 引数がセルのアドレス：セルの値が引数として関数にわたります
• 引数がセル範囲：二次元配列で関数にわたります

範囲内のセルにデータ検証ルールを適応したい場合

データ検証ルールのビルダー

A1のに日付を入力するデータ検証ルールを設定します
newDataValidation()：データ検証ルールのビルダーを作成します
build()：ビルダーにデータ検証ルールを構築します
setDataValidation(rule)：範囲内のすべてのセルにデータ検証ルールを設定します

function validate() {
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireDate().build();
 cell.setDataValidation(rule);
}

setAllowInvalid(allowInvalidData)： 入力がデータ検証に失敗した場合に警告を表示するか、入力を完全に拒否するかを設定します
setHelpText(helpText) ： データ検証が設定されているセルにユーザーがカーソルを合わせたときに表示されるテキストを設定します

var rule = SpreadsheetApp.newDataValidation()
    .requireNumberBetween(1, 100)
    .setAllowInvalid(false)
    .setHelpText('1から10の数値を入力')
    .build();

ドライブ

GASからGoogleドライブのファイルとフォルダを作成・検索・変更できます

主なクラス
DriveApp→Folder→File

• DriveApp ：ドライブのファイルとフォルダを作成・検索・変更できるようにします
• Folder ：ドライブ内のフォルダ
• File：ドライブ内のファイル
• FolderIterator ：フォルダのコレクションを反復処理できるようにします
• FileIterator ：ファイルのコレクションを反復処理できるようにします

作成

フォルダやファイルを作成する主な「DriveAppクラス」のメソッド

• createFolder(name) ： 指定された名前ドライブのルートにフォルダを作成します（戻り値Folder）
• createFile(blob) ：ドライブのルートにBlob（任意のデータ）からファイルを作成します（戻り値File）
• createFile(name, content)： 指定された名前と内容でドライブのルートにテキストファイルを作成します（戻り値File）
• createFile(name, content, mimeType) ：指定された名前、内容、MIMEタイプでファイルを作成します（戻り値File）

ドライブの特定のフォルダにファイルを作成する

//内容を指定してファイルを作成する
function createFile(){
  const folder = DriveApp.getFolderById('フォルダID'); 
//MimeTypeをPDFに設定する例
  folder.createFile('ファイル名', '内容', MimeType.PDF);
}

MineTypeについて

取得

フォルダやファイルを取得する主なDriveAppのメソッド

• getFolderById(フォルダID)：指定されたIDのフォルダを取得します（戻り値Folder）
• getFileById(ファイルID)：指定されたIDのファイルを取得します（戻り値File）I
• getFolders() ：ドライブ内のすべてのフォルダのコレクションを取得します（戻り値FolderIterator）
• getFiles() ：ドライブ内のすべてのファイルのコレクションを取得します（戻り値FolderIterator）

Folder・Fileクラスのメソッド例

• getName()：名前を取得します
• getUrl() ：アプリでを開くために使用できるURLを取得します

検索

ドライブ内のすべてのファイルの名前をログに出力

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

searchFiles(params)：指定された検索条件に一致するドライブ内のすべてのファイルのコレクションを取得します
2月28日以降に変更されたファイル全てのファイル（＊ドキュメントの例）

var files = DriveApp.searchFiles(
    'modifiedDate > "2013-02-28" and title contains "untitled"');
while (files.hasNext()) {
  var file = files.next();
  console.log(file.getName());
}

＊searchFolders(params)： 指定された検索条件に一致するドライブ内のすべてのフォルダのコレクションを取得します

params（引数）について

ドキュメント

GASからGoogleドキュメントファイルの作成・アクセス・変更ができます

ドキュメントのクラスと構造（主なクラスと階層構造）
＊ドキュメントの構造はHTMLと似ています

DocumentAppクラス：トップレベルのオブジェクト
「ドキュメントを新規作成したり」「既存のドキュメントを開いたり」します

• Document ：テーブルやリストなどのリッチ テキストと要素を含むドキュメント
  • HeaderSection ：ヘッダー セクションを表す要素
  • Body： ドキュメントの本文を表す要素
  • FooterSection： フッター セクションを表す要素
    • Paragraph： 段落を表す要素
    • ListItem ：リスト項目を表す要素
      • Text ：リッチテキスト領域を表す要素
        ※Textは文字列ではなくTextオブジェクトです

DocumentAppクラスのメソッド
戻り値はDocumentです

• create(name)：新しいドキュメントを作成して返します
• getActiveDocument()：スクリプトがコンテナにバインドされているドキュメントを返します
• openById(id) ：指定されたIDを持つドキュメントを返します
• openByUrl(url) ： 指定された URL のドキュメントを開いて返します

getBody()：アクティブなドキュメントのBodyを返します
＊HeaderSectionとFooterSection以外は通常、getBody() の呼び出しから始まります

新しいドキュメントを作成
ドキュメントと同じテキストを含む段落を挿入、その段落を見出しとして設定し
2次元配列の値に基づいてテーブルを追加、スタイルを設定
（＊ドキュメントのガイドより）

function createDoc() {
  var doc = DocumentApp.create('Sample');
  var body = doc.getBody();
  var rowsData = [['Plants', 'Animals'], ['Ficus', 'Goat'], ['Basil', 'Cat'], ['Moss', 'Frog']];
  body.insertParagraph(0, doc.getName())
      .setHeading(DocumentApp.ParagraphHeading.HEADING1);
  table = body.appendTable(rowsData);
  table.getRow(0).editAsText().setBold(true);
}

replaceText()でテキストの置き換え（＊ドキュメントのガイドより）
＊クライアントごとにカスタマイズされたドキュメントを作成する場合など

//雛形の作成
function createPlaceholders() {
  var body = DocumentApp.getActiveDocument().getBody();
  body.appendParagraph('{name}');
  body.appendParagraph('{address}');
  body.appendParagraph('{city} {state} {zip}');
}
//clientオブジェクトの内容に置き換え
function searchAndReplace() {
  var body = DocumentApp.getActiveDocument().getBody();
  var client = {
    name: 'Joe Script-Guru',
    address: '100 Script Rd',
    city: 'Scriptville',
    state: 'GA',
    zip: 94043
  };
  body.replaceText('{name}', client.name);
  body.replaceText('{address}', client.address);
  body.replaceText('{city}', client.city);
  body.replaceText('{state}', client.state);
  body.replaceText('{zip}', client.zip);
}