スプレットシートとGAS組み合わせて仕事の効率をあげる

BLOG
2019.08.01

Google Apps Script (GAS)でプログラミング

google driveは皆さん使ったことはあるでしょうか。マイクロソフト社のOfficeのようなアプリがWebブラウザ上で無料で利用でき、ストレージサーバとしても利用できる便利なサービスです。

私が良く使っているのはスプレットシート（Excelのようなもの）とスライド（パワーポイントのようなもの）です。使い慣れたExcelやパワーポイントと比較すると、操作性や機能がちがうところがあるので慣れるまで大変かもしれませんが、なかなか便利な機能を持っています。その機能のひとつが、Google Apps Script (GAS)が利用できることです。

GASはOfficeで言えば、VBマクロのようなものです。用意されている関数を使ったり、独自にプログラミングするｋとおでセルのデータを参照したり、インターネット上のデータを取得したりできます。無料版では実行時間や回数に制限がありますが、うまく工夫することでいろいろなアプリケーションや便利ツールを開発することができます。

 

週末にGoogleでプログラミングを気軽に学ぶ
このページでは「週末にGoogleでプログラミングを気軽に学ぶ」をテーマにした内容を紹介しています。
www.gldexp.com
2019.07.09

 

仕事の効率をあげてくれる便利なGAS

私は仕事でも、プライベートでもGASを使っています。Webサイトの分析やレポートの出力などはスプレットシートと組み合わせると、とても仕事が捗ります。メール通知機能も活用すればなんでも通知することができるので応用の幅が広いです。

いろいろスプレットシートに便利なGASを作っていると、結構な頻度で関数を使いまわしたりするので、有用な関数やプログラムはさっと使えるようにしておくのがいいですよね。

以下では、私が普段よく使っている関数をいくつか紹介します。GASはそこまで難しくはないのですが、他のプログラミング言語と似ているため間違えたりすることがあるので、実は自分の備忘録もかねてます。

GASの基本的な使い方やリファレンスはGoogleの公式ページやよくまとまったブログがぐぐるとたくさんあると思うので、そちらを参照するといいとおもいます。

スプレッドシートに関数実行用のメニューを追加する

基本的にGASを使うときは書いておきます。シートの上の方にサブメニューが付きます。関数のうち、自分のタイミングで実施したい関数を登録しておきます。

.addMenu(‘テキスト’, 関数名と関数の配列);：スプレットシートのメニューに関数メニューが追加される。

function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menus = [
    {name: 'データの複写', functionName: 'copy_data'}, 
    {name: 'データを削除', functionName: 'delate_data'},
  ];
    ss.addMenu('関数一覧', menus);
    }

 

特定文字が含まれていないかをチェックする

特定のデータにフィルターを掛けたり、抽出したりするときに正規表現とテキストマッチングを使います。例えば、URLリストから特定の文字列が含まれるデータを除外するなどに使います。

RegExp：正規表現オブジェクトを生成します。
.test()：正規表現されたオブジェクトとテキストを比較します。

for(var i = 0; i < filters.length; i++) {
   var regex = new RegExp(filters[i]);
   if (regex.test(url)) {
   return true;
   }
}

 

スプレットシートのシートAのデータをシートBにコピーする

計算した結果をバックアップしたり、記録したりするときに使います。GASの場合は、シート上で計算させた結果をGASに取り込んで、さらに計算するという方法を多用することがあるので覚えておいて損はしません。

.getRange(コピー元).getValues()：コピー元のデータを取得します。
.getRange(コピー先).setValues(データ)：データをコピー先に設定します。
SpreadsheetApp.flush()：計算など実施後、スプレットシートに計算結果を表示させるためにつかってます。

function data_copy(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var top_ss = ss.getSheetByName('シートA');
　var mirror_ss = ss.getSheetByName('シートB');
 
    //複写元のセルを範囲指定 
    var from_range = "A1:AD65";

    //対象データのシートBにコピーする
    var copyValue = top_ss.getRange(from_range).getValues();
    mirror_ss.getRange(from_range).setValues(copyValue);   
    SpreadsheetApp.flush(); 
    }

 

GASの特定トリガーを削除する

GASにはトリガーを設定することができますが、実行後に設定したトリガーが不要になったりすることがあります。これはゴミとしてのこってしまうので削除するのがベストです。このために設定されているトリガーを抽出して削除します。

ScriptApp.getProjectTriggers();設定してあるトリガーを取得します。
ScriptApp.deleteTrigger();指定したトリガーを削除します。

// トリガーを削除する
function deleteTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for(var i=0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == "printpdfn") {
     ScriptApp.deleteTrigger(triggers[i]);
   }
 }
}

文字の置換

文字の置換はスプレットシート上でやるよりGASのほうが複雑なことができます。以下はURLを簡易的にデーコードするために作りました。スクレイピングと合わせて使うと効果的です。

//引数のデコード（最小限）
var url=[];
url=link.split('url=');
url=url[1].split(String.fromCharCode(38));
link=url[0].replace(/%3F/gi,"?");//デコード「?」
link=link.replace(/%3D/gi,"="); //デコード「＝」
link=link.replace(/%26/gi,"&"); //デコード「&」

//タイトルからHTMLタグを除去
title =title.replace(/<b>/gi,"");
title =title.replace(/<\/b>/gi,"");

スプレットシートの複製

データを分析させるシートの場合、計算結果をコーピーして保存しておきたい場合がよくあります。その時はスプレットシートそのものを複製するということができます。

.getId();スプレットシートのIDを取得する
.makeCopy(rename, OutputFolder);指定フォルダに対象のスプレットシートを複製する

//開いているスプレッドシートIDを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getid = ss.getId();
var templateFile = DriveApp.getFileById(getid);

// 出力フォルダの指定
var OutputFolder = DriveApp.getFolderById('保存先のフォルダID');
//スプレットシートの複写
var newfile = templateFile.makeCopy(rename, OutputFolder);"

その他よく使う関数

今まで紹介したような関数の他、よく使う関数をまとめました。この辺を抑えておくと、デバックやアプリケーションのようなスプレットシートを作ることができます。

//一定時間経過後に関数を実施する場合に利用
ScriptApp.newTrigger('printpdfn').timeBased().after(10*1000).create();

// 一定時間、GASの実行を停止
Utilities.sleep(7*1000);

//ログを残す。デバックに使う
Logger.log();

//メールを送信する
GmailApp.sendEmail(mail, mailtitle, mailtext);  

//シートのクリア
sheet.clear();

 

まだまだGASは勉強中

自分がよく使うものをまとめました。もう少しコードを少なく、各方法もあると思います。上記で紹介した以外にも、いろいろありますが、思いついたら追記していこうと思います。

タイトルとURLをコピーしました