LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Saturday, May 28, 2016

Gmailのデータを取得する

GmailにあるデータをGoogle Apps Scriptで取り出すということをやってみました。
件名や本文、差し出し人などのデータを取得することができます。

まずは最新の2件の件名を取得してメッセージボックスに表示するということをやって、次にその2件の件名をシートに書き出すということをやってみました。
最後にラベルを指定して最新の2件をシートに書き出すということもやってみました。

スレッドに複数のメッセージがある場合は、それらも取得できるようにしています。


1. 最新の2件の件名をメッセージボックスに表示します

コード.gs

function getMail_subject() {
    var thread = GmailApp.getInboxThreads(0, 2); 
    var messages = GmailApp.getMessagesForThreads(thread);
    for (var i = 0; i < thread.length; i++) {
        for (var j = 0; j < messages[i].length; j++) {
            var subject = messages[i][j].getSubject();
            Browser.msgBox(subject);
        }
    }
}



実行すると最新のメールから1件ずつメッセージボックスに表示されます。
以下の例はこのコードを書いた時点での僕のGmailにあった最新2件の件名です。

まず最新の1件が表示されます。

OKをクリックすると次の1件が表示されます。



2. 最新の2件の件名をシートに書き出します

ここでは書き出すシート名を「シート1」としています。

コード.gs

function getMail_subject_write() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("シート1"); //sheet_name
    var threads = GmailApp.getInboxThreads(0, 2); 
    var messages = GmailApp.getMessagesForThreads(threads);
    var row = sh.getRange("A:A").getValues().filter(String).length + 1;
    for (var i = 0; i < threads.length; i++) {
        for (var j = 0; j < messages[i].length; j++) {
            var subject = messages[i][j].getSubject();
            sh.getRange(row, 1).setValue(subject);
            row++;
        }
    }
}



実行すると、最新の2件の件名がシート1のA列に書き出されます。



3. ラベルを指定して最新の2件をシートに書き出します

LABEL名に指定したいラベル名を入力します。

コード.gs

function getMail_by_label() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("シート1");
    var label = GmailApp.getUserLabelByName("LABEL名"); //label_name
    var threads = label.getThreads(0, 2);
    var messages = GmailApp.getMessagesForThreads(threads);
    var row = sh.getRange("A:A").getValues().filter(String).length + 1;
    for (var i = 0; i < threads.length; i++) {
        for (var j = 0; j < messages[i].length; j++) {
            var subject = messages[i][j].getSubject();
            sh.getRange(row, 1).setValue(subject);
            row++;
        }
    }
}


実行すると、指定したラベルの最新の2件の件名がシート1のA列に書き出されます。



おまけ

ここでは件名だけを取得しましたが、本文はgetBody()、差し出し人はgetFrom()で取得できます。
その他取得したい情報については公式サイトを御覧ください。

Class GmailMessage
https://developers.google.com/apps-script/reference/gmail/gmail-message#methods

スプレッドシートを開いた時に処理を実行する

function onOpen() {}
の{}の中に実行したい処理を書くと、スプレッドシートを開いた時に処理を実行できるようになります。
簡単な例として、スプレッドシートを開いた時にメッセージボックスを表示するというコードを書いてみました。
{}の中に Browser.msgBox("メッセージを表示する"); と書くだけで、そのシートを開いた時にメッセージボックスが表示されるようになります。



コード.gs

function onOpen() {
Browser.msgBox("メッセージを表示する");
}





次回スプレッドシートを開いた時に表示される画面



スプレッドシートにメニューを追加する雛形

スプレッドシートにメニューを追加することで、そこから関数を実行することができるようになります。こうすることで、スクリプトエディタを開いて実行メニューから選択するというひと手間を減らせます。
これにはスプレッドシートを開いた時に実行されるfunction onOpen() {}を利用します。

簡単なコードで試すために、メッセージボックスを表示するだけの関数をメニューに追加するというコードを書いてみました。


コード.gs
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('SCRIPT')
      .addItem('Message', 'myFunction')
      .addToUi();
}

function myFunction() {
  Browser.msgBox("メッセージを表示する")
}



スプレッドシートに追加されるメニュー


Messageを選択すると、myFunctionが実行されて画面上にメッセージボックスが表示されます。


Friday, May 27, 2016

スプレッドシートにサイドバーを表示する雛形

スプレッドシート上にGUIを表示するとき、GUIで画面を覆ってしまいたくない場合はサイドバーに表示することもできます。
サイドバーに表示するとシート上でカーソル操作をしながら入力を行うことができます。

複雑な処理を書く前の雛形として、サイドバーにテキストエリアを表示し、そこに入力した文字をアクティブセルにも自動で入力するというだけの簡単な動作をするアプリを作ってみました。


コード.gs
function openSidebar() {
    var html = HtmlService.createTemplateFromFile('index')
        .evaluate()
        .setSandboxMode(HtmlService.SandboxMode.IFRAME)
        .setTitle('Input App');
    SpreadsheetApp.getUi().showSidebar(html);
}

function set_value(value){
  var sh = SpreadsheetApp.getActiveSheet();
  var range = sh.getActiveRange();
  range.setValue(value);
}


index.html
<!DOCTYPE html>
<html>
<head>
    <base target="_top">
</head>
<body>
    <textarea id="ta" onkeyup="run_input()"></textarea>
</body>
<script>
    function run_input() {
        var value = document.getElementById("ta").value;
        google.script.run.set_value(value);
    }
</script>
</html>


スプレッドシートに表示される画面


コードを書いて実行するまでの手順です。

1. コード.gsにコードを書いて保存します。

2. ファイル>新規作成>HTMLファイル を選択します。

3. ファイル名を入力してOKをクリックします。

4. index.htmlに上記コードを入力して保存します。

5. コード.gsタブでopenSidebarを実行します。

6. 許可を確認をクリックします

7. 許可をクリックします

実行後、スプレッドシートへ戻ると、作成したサイドバーが表示されます。

表示されたサイドバーのテキストエリアに文字を入力すると、アクティブセルに同じ文字が入力されるアプリケーションが作れました。



参考

Dialogs and Sidebars in G Suite Documents / Custom sidebars
https://developers.google.com/apps-script/guides/dialogs

Wednesday, May 25, 2016

Googleドライブの特定のフォルダ内のファイル一覧とプレビューのリンクをシートに書き出す

Googleドライブにあるファイルのプレビューを開きたい時、下記のようにするとリンクから一発で開けました。最初は画像をプレビューするリンクを探していましたが、シートもプレビューできるようです。
https://drive.google.com/file/d/ファイルのID/view

ドライブの中のフォルダの中にいくつかファイルがある場合、そのファイル一覧を取得して、プレビューのリンクをシートに書き出すというのをやってみました。

1. フォルダIDを取得する
2. フォルダの中のファイルを取得する
3. ファイル名、IDを取得する
4. シートのA列にファイル名、B列にID、C列にプレビューのリンクを書き出す
5. D列にMimeType, E列に作成日, F列に更新日も書き出してみる

コード.gs
function myFunction() {
  var names= [];
  var ids = [];
  var types = [];
  var createds = [];
  var updateds = [];
  var folder = DriveApp.getFolderById("FOLDER_ID");
  var contents = folder.getFiles();
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    id = file.getId();
    type = file.getMimeType();
    created = file.getDateCreated();
    updated = file.getLastUpdated();
    types.push(type);
    createds.push(created);
    updateds.push(updated);
    names.push(name);
    ids.push(id);
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = 1;
  var view_path = "https://drive.google.com/file/d/";
  for(var i = 0; i < ids.length; i++){
    sh.getRange(row, 1).setValue(names[i]);
    sh.getRange(row, 2).setValue(ids[i]);
    sh.getRange(row, 3).setValue(view_path + ids[i] + "/view");
    sh.getRange(row, 4).setValue(types[i]);
    sh.getRange(row, 5).setValue(createds[i]);
    sh.getRange(row, 6).setValue(updateds[i]);
    row++;
  }
}








Tuesday, May 24, 2016

スプレッドシートに入力ツールを表示する

Google Apps Scriptを知ってからまず作りたいと思ったのが、スプレッドシート上に入力ツールを表示して入力作業を楽にするということでした。
画面の表示部分はHTMLで記述できるようになっています。
僕は新しく画面を作る時、CSSやJavaScriptで作りこんでいく前に、まずはテキストエリアを表示するだけの画面を雛形的に使っています。
コード.gsで表示するHTMLファイルを指定して、そのHTMLファイルを作成して実行すると、スプレッドシート上に表示することができます。
下記はそのテキストエリアを表示するだけの簡単なコードです。


コード.gs
function openGUI() {
  var html = HtmlService.createHtmlOutputFromFile('index').setWidth(600).setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html, 'myGUI');
}


追記
.setWidth(600).setHeight(600);
等でモーダルの幅と高さを調整できる


index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <textarea></textarea>
  </body>
</html>


スプレッドシートに表示される画面


コードを書いて実行するまでの手順です。

1. コード.gsにコードを書いて保存します。

2. ファイル>新規作成>HTMLファイル を選択します。

3. ファイル名を入力してOKをクリックします。

4. index.htmlの7行目にテキストエリア(今回の例)を配置して保存します。

5. コード.gsタブで実行する処理を選択します。

実行後、スプレッドシートへ戻ると、作成したGUIが表示されます。



Sunday, May 22, 2016

スプレッドシートにメッセージボックスを表示する


ここではGoogle Apps Scriptで最初に書くコードとしてBrowser.msgBox()の例を挙げます

以下のコードを書いて実行すると
スプレッドシート上にメッセージを表示することができます


コード.gs
function myFunction() {
  Browser.msgBox("メッセージを表示する")
}



スプレッドシートに表示される画面



スプレッドシートでスクリプトエディタに書いたコードを実行する手順は
2017/12/19現在以下のようになっています


コードを書く場所


スプレッドシート上部のメニューのツールにある「スクリプトエディタ...」を選択して


表示されるこの画面にコードを書いていきます


コードを実行する手順


1. コード.gsに上記コードを入力して保存します


2. 実行>myFunctionを選択します


3. 許可を確認をクリックします


3-1 アカウントの選択をします

3-2. 詳細をクリック


3-3. プロジェクト名をクリック


4. 許可をクリックします


この後スプレッドシートに戻ると、シート上にメッセージが表示されます




補足

Google Apps Scriptのファイル(プロジェクト)を書く場所 に別途まとめました

HTML Serviceで最初に書くコードはこちら


Saturday, May 21, 2016

HTMLでページを作る



HTML Serviceを利用してWebページやアプリケーションを作成できます
例としてテキストエリアを表示するコードを書きました



コード.gs
function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

タイトルを付ける場合
return HtmlService.createHtmlOutputFromFile("index").setTitle("TITLE");



index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <textarea></textarea>
  </body>
</html>



Webアプリとして公開する手順(2017/11/19現在)

1. コード.gsを書いて保存します


2. ファイル>新規作成>HTMLファイル を選択します


3. ファイル名(例ではindex)を入力してOKをクリックします


4. index.htmlの7行目にテキストエリアを配置して保存します


5. 公開>ウェブアプリケーションとして導入... を選択します


6. それぞれの項目を設定します
  • プロジェクト バージョン: 任意の説明(例ではfirst version)
  • 次のユーザーとしてアプリケーションを実行: 自分
    • または、ウェブアプリケーションにアクセスしているユーザ
  • アプリケーションにアクセスできるユーザー: 自分だけ
    • ※外部APIを使うときは全員(匿名ユーザを含む)を設定する


7. ウェブアプリケーションとして導入完了
  • 現在のウェブアプリケーションのURLが本番環境のURLで末尾が/exec
  • 最新のコードのリンクが開発環境のURLで末尾が/dev
    • 変更して保存してもバージョンを上げない限り本番には反映されない


最新のコードのリンクをクリックすると開発環境の画面が開きます

開発環境ではコードを変更して画面をリロードすると変更が反映されます
バージョンを上げると本番環境に反映されます



参考

HTML Service: Create and Serve HTML
https://developers.google.com/apps-script/guides/html/

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...