【GAS】コピペで使える!頻出の基本サンプルコード集

GAS頻出基本コード GAS

こんにちは!
おでぃーです。

今回は、GASで頻出するあらゆる用途のソースコードをまとめていきます。

こんな経験ないでしょうか??

  • 何度も実装しているのにコードを書けない。
  • 「この処理どうやってやるんだっけ…?」と思ってことあるごとに調べる。
  • 処理ごとに何度も同じ記事にたどり着き読み直す。

特に1つ目は私もそうで、簡単な処理であっても0から書こうとすると意外と苦労します。

なので調べるんですが、情報が散らばっていたり、余計な処理が入っていたりします。

読み解いて自分に必要な部分だけ持ってくるのが面倒くさい。。

ということで、

このページさえブックマークしておけば、いちいちググって調べる必要なし!

そんなページを目指して、コードを随時更新していきます。

この記事を書いた人
おでぃー

GASを使った業務自動化に関するテクニックを発信。
本業ではプロダクト開発でエンジニアのディレクションをしながら、副業にてブログ運営やツール・スキルの販売を実施。
ネット広告運用経験からSNS領域の自動化に注力。

おでぃーをフォローする

スプレッドシート取得

まずこれがなきゃ始まらないですね。

メジャーなのは「getActiveSheet」などのActive系メソッドだと思います。
しかし、スプレッドシートを複数開いている場合や、対象のスプレッドシートを閉じている場合に上手く動きません。

なので私は基本的に「openById」などのopenBy系メソッドを使って確実に動作するようにします。

下記のスクリプトは「getValues」でセル内の文字を取得する処理までとなります。
ちなみに範囲が単一セルの場合は、「getValue」でOK。

 var spreadsheetId = 'スプレッドシートID';
  var spreadsheet = SpreadsheetApp.openById('spreadsheetId');
  var sheet = spreadsheet.getSheetByName('sheetName');
  var Values = sheet.getRange('A1:C').getValues();

スプレッドシートIDは、スプレッドシートのURLに含まれているIDを指します。https://docs.google.com/spreadsheets/d/ここがスプレッドシートID/edit#gid=○○○

メニュー&実行ボタン作成

私の場合、個人で使用するだけなら用途は少ないですが、複数でスプレッドシートを共有する場合や、ツールとして他人に共有する場合は分かりやすいのでよく使います。

変数menu1をmenu2などと新たにつくれば、メニューを増やせます。
addItemを増やせば同一メニュー内のボタン数を増やせます。

function onOpen() {
 var ui = SpreadsheetApp.getUi()
  
  var menu1 = ui.createMenu("メニュー名");
  
  menu1.addItem("実行ボタン名","関数名");
  
  menu1.addToUi();
}

基本的には上記を設定してスプレッドシートをリロードすれば上部のメニューバーに反映されています。

しかし、反映されないときがあります。

そのようなときは、上記記事の方法で解決するか、もしくはトリガーを作成します。

左側メニューから「トリガー」を選択。

右下にある「トリガーを追加」を選択。

下の画像のように設定し、保存。

これでスプレッドシートをリロードするとメニューバーが表示されるようになります。

日時の整形

日時の操作に使います。

var date = new Date();
var formattedDate = Utilities.formatDate(date,"JST", "yyyy/MM/dd") //日付まで
var formattedDate = Utilities.formatDate(date,"JST", "yyyy/MM/dd hh:mm:ss") //秒数まで

スクリプトプロパティ取得

私はツール販売をするときに、スプレッドシートIDを「○○.gsの何行目のこの場所に設定」というよりもスクリプトプロパティを設定してもらうほうが、いつも説明が同じで楽なのでこれを使っています。

ほかにもAPIキーを設定するとき、他人にソースコード上で見られたくないときなんかに使うと便利です。

const scriptProps = PropertiesService.getScriptProperties();   
var spreadsheetId = scriptProps.getProperty('spreadsheetId');

トリガー作成

「GASエディタで設定できるじゃん」と言われればそうなのですが、
こちらもツールとしてお渡しするときに、スプレッドシートをコピーしてもらった後に再度設定してもらう必要があるので、メニューバーにトリガー作成ボタンを置くかたちで使用しています。

// 分ベースのトリガーを作成
function createEveryMinutesTrigger(triggerName, minute) {
  ScriptApp.newTrigger(triggerName).timeBased().everyMinutes(minute).create();
}

// 日ベースのトリガーを作成
function createEveryDaysTrigger(triggerName, days, hours) {
  ScriptApp.newTrigger(triggerName).timeBased().everyDays(days).atHour(hours).create();
}

トリガー削除

こちらもメニューバーにトリガー作成ボタンを置くかたちでよく使用しています。

下記はすべてのトリガーを削除するスクリプトです。

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

メッセージボックス作成

たった1行ですが、メニューを使って実行するとログが見れないので、これがあるだけで安心させてくれます。

Browser.msgBox("Success!");

繰り返し処理(for)

いわゆるfor文です。

他にも記述の仕方はあるのですが、私は慣れてしまったのでいつもこれです笑

配列変数(下記の例だとdata)に対して「.length」をすると、その配列の要素数を出力できるので、

それを繰り返しの上限としています。

for(i = 0 ; i < data.length ; i++){
 break
}

Vlookup関数のような処理

これめちゃくちゃ頻繁に使います。

ExcelでVlookup関数を使うようなものと考えると当然ですよね。

var searchValue = "検索文字列"
var range = sheet.getRange("A2:A");
var values = range.getValues()
for(i = 0 ; i < Values.length ; i++){
    var Value = Values[i][0];
    if(Value == searchValue){
    sheet.getRange("B" +(i+2)).getValue();
    break;
    }
}

//検索文字列をA列から探し出して、同じ行のB列の値を取得
// =Vlookup(searchValue,A2:B,2,false)

値を含む連続するセルの最終行を取得(getNextDataCell)

実際には見出しのような使い方になりますが、正しく説明すると下記のようになります。

  1. 起点から見て、値を含む連続しているセルの最終行を取得
  2. 起点から見て、値を含まない連続しているセルの最終行の次の行を取得

注意しなければならないのは、

「データが連続しているセル最終行」

「値を含む連続しているセルの最終行」

だけではないので、このような表現で理解するのは誤っているということです。

上記のような、行ごと・列ごとにデータが含まれる最終行が異なるシートを例とします。

今回は、A列の値を含む最終行を取得したいとします。

この場合、該当するのは6行目もしくは9行目になりますよね。

どちらを取得したいかはシーンによって異なると思いますが、両方とも取得することできます。

var lastRow = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
Logger.log(lastRow); //6
//A1セル(1,1)を起点に下方向(DOWN)に進み、値の有無が連続しているセルの最終行を取得


var lastRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
Logger.log(lastRow); //9
//A列最終セル(getMaxRows)を起点に上方向(UP)に進み、値の有無が連続しているセルの最終行を取得

前者は、A1セルから見たときにA2:A6が値有りで連続していて、取得する行数は6です。

つまり1の使い方となります。

後者は、最終セルA15から見たときにA10:A14が値無しで連続していて、取得する行数は最終行である10の次の行の9となります。

つまり2の使い方となります。

他の例としてB列を見てみましょう。

画像を再掲します。

var lastRow = sheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
Logger.log(lastRow); //3
//B1セル(1,1)を起点に下方向(DOWN)に進み、値の有無が連続しているセルの最終行を取得


var lastRow = sheet.getRange(sheet.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
Logger.log(lastRow); //12
//B列最終セル(getMaxRows)を起点に上方向(UP)に進み、値の有無が連続しているセルの最終行を取得

前者は、B1セルから見たときにB2:B3が値有りで連続していて、取得する行数は3です。

つまり1の使い方となり、1つ目の例と同じ使い方でもあります。

後者は、最終セルB15から見たときにB12:A14が値有りで連続していて、取得する行数は12です。

つまりこれも1の使い方となります。

最後にC列を見てみましょう。

var lastRow = sheet.getRange(1, 3).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
Logger.log(lastRow); //15
//C1セル(1,1)を起点に下方向(DOWN)に進み、値の有無が連続しているセルの最終行を取得


var lastRow = sheet.getRange(sheet.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
Logger.log(lastRow); //1
//C列最終セル(getMaxRows)を起点に上方向(UP)に進み、値の有無が連続しているセルの最終行を取得

前者は、C1セルから見たときにC2:C15が値無しで連続していて、取得する行数は最終行である15の次の行の16のはずですが、15までしか存在しないので15となります。

このとき取得行が1ではない点に注意です。

非常に分かりにくいですが、「起点とするセルは含まない」という仕様になっているようです。

使い方としては2のほうになります。

後者は、最終セルC15から見たときにC2:C14が値無しで連続していて、取得する行数は最終行である2の次の行の1となります。

つまりこれも2の使い方となります。

値を含む連続するセルの最終列を取得(getNextDataCell)

前項の最終列バージョンです。

1行目を例にします。

var lastCol = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
Logger.log(lastCol); //3
//A1セル(1,1)を起点に右方向(NEXT)に進み、値の有無が連続しているセルの最終行を取得

var lastCol = sheet.getRange(1, sheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
Logger.log(lastCol); //6
//1行目最終列を起点に左方向(PREVIOUS)に進み、値の有無が連続しているセルの最終行を取得

前者は、A1セルから見たときにB1:C1が値有りで連続していて、取得する列数は3です。

つまり1の使い方となります。

後者は、最終セルH1から見たときにG1のみが値無しなので、取得する列数は最終列である7の次の列の6となります。

つまり2の使い方となります。

まとめ

今回は、よく使うソースコードをまとめました。

そのまま使って開発を効率的に進めましょう!

ココナラでは、GASやスプレッドシートを使った自動化ツール作成のサービスを低価格で販売しています。

GAS・APIを使って業務を効率化、自動化します 【一律¥3000】各種SNSやGoogleサービスも対応! | 作業自動化・効率化 | ココナラ
当サービスでは、Google Apps Script (GAS)を使用して、皆様のビジネスプロセスを自動化するためのツール開発を提供します!もちろん、スプレッド...
  • こんなツールを作ってほしい!
  • これ自動化できる?
  • 今使っているツールにこんな機能を加えてほしい!

などのご要望がもしありましたらお気軽にご連絡ください!

下記からご登録いただくと、1000円分のポイントが付与されお得にはじめられます!
ご登録の際は、招待コード:GA127Vをぜひご活用ください。

招待コード:GA127V

お読みいただきありがとうございました!

コメント