スプレッドシートで簡単GAS入門：すぐ忘れる自分のためのメモ

はじめに

はじめまして、ココネ株式会社でエンジニアをしているOです。

ココネアドベントカレンダー2023年の19日目になります。今年もあっという間でしたね。
皆さんはGAS(Google Apps Script)書いてますか。自分は本当にたま〜に書きます。そして、毎回忘れてます(泣)。
あまりに毎回忘れているので簡単にメモを兼ねて、スプレッドシートを例に簡単な書き方を書いておこうと思います。

GASはJavaScriptをベースした言語ですので、プログラミング経験がある方でしたらなんとなく分かると思います。
ここでは文法などについては特に記載しない予定です。

エディタ起動&実行

何はともあれスプレッドシートを開きましたらまずはエディタを起動しましょう。
メニューの「拡張機能」-「Apps Script」より起動できます。

最初からmyFunctionという関数があると思いますので、その中に以下のように記載して、保存してから実行してみましょう。
この状態で実行ボタン or デバッグボタンを押すとログにHello!!と出ます。

var text = "Hello!!"
Logger.log(text);

ついでにデバッグ実行の仕方も書いておきましょう。上の図ですと三行めの横に青い●がついていると思いますが、
行数の左横をクリックするとブレークポイントを設定できます。この状態でデバッグボタンで実行すると、そこで一旦実行がとまり変数状態などが確認できます。ここだとtextに"Hello!!"と入っているのが確認できますね。

デバッグ画面の▶️ボタンで再開できます。

これで実行できました。簡単ですね。
ただ実用上エディタから実行するというケースはめったにないと思うので次はボタンから実行してみましょう

ボタンから実行

まずはボタンを作成します。「挿入」-「図形描画」

「図形描画」画面で適当にボタンを描画して、保存して終了ボタンを押下します

作成されたボタンを右クリックすると右上に点々の設定メニューがでますので、そこから「スクリプトを割り当て」を押します。

先ほど作成したmyFunctionを指定します。

一応これで完了ですが、ボタンから実行した場合、前のmyFunction()ですとログが出るだけで何が起きているか分からないので以下のように修正します。Browser.msgBoxはダイアログを表示するための関数です。

function myFunction() {
  Browser.msgBox("Hello");
}

この状態でボタンをクリックすると以下のようにダイアログが表示されます。

これで基本的な実行方法は終わりです。
今回自分がGASをいじることになったのはシートの情報を他のシートに一部抜粋してコピーしたいという要望があってのことでした。
ですので、セルの情報を取得したり挿入したりといったことをやっていきたいと思います。

スプレッドシートの基本操作

お試し用に以下のようなシートを用意してみました。このシートに対して色々とやってみましょう。
「全体シート」と「開発シート」の二つのシートがあります。

最終的にはセルの情報を取得したいというケースが多いと思いますので、セルの情報を取得してみましょう。
セルの情報を取得するには「スプレッドシートの取得」→「シートの取得」→「セルの取得」の順に取得していく必要がありますので、順々に見てきましょう。

スプレッドシートの取得

//現在開いているスプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();

//IDを指定してスプレッドシートを取得
var id = "1234567";
var ss = SpreadsheetApp.openById(id);

//URLを指定して取得 (edit部分まで含める必要あり)
var url = "https://docs.google.com/spreadsheets/d/1234567/edit";
var ss = SpreadsheetApp.openByUrl(url);

ID指定の場合はスプレッドシートのURLの下の太字部分がIDになります。(1234567は例です。)
docs.google.com/spreadsheets/d/1234567/edit

シートの取得

シートは上の例でいうと「全体シート」や「開発Tシート」になります。以下のように色々な方法で取得できます。

//開いているシートを取得
var sheet = ss.getActiveSheet();

//シート名で取得
var allTaskSheet = ss.getSheetByName("全体シート");

//まとめて取得してindex指定
var developTaskSheet = ss.getSheets()[1];//0番目が全体シート、1番目が開発Tシート


Logger.log(allTaskSheet.getName());//全体シートと表示
Logger.log(developTaskSheet.getName());//開発Tシートと表示

各セルの取得

//特定のセルを取得
var a1Data = allTaskSheet.getRange('A1').getValue();
var c2Data = allTaskSheet.getRange(2,3).getValue();//2行目、3列目(C2)のデータ

//2次元配列でシート全体のデータを取得
var datas = allTaskSheet.getDataRange().getValues();

以下のようにログを出してみるとわかりますがgetValuesで取得したデータは行と列の２次元配列になってます。

for (var i = 0; i < datas.length; i++) {
    var data = datas[i];
    var category = data[0];
    var release = data[1];
    var project = data[2];
    var task = data[3];
    var person = data[4];

    Logger.log("カテゴリ:"+category+" リリース日:"+release+" 施策名:"+project+" タスク："+task+" 担当:"+person);
}

データを検索して色々とやりたい場合はgetValuesで取得してfor文などで回してデータを探すといいでしょう。

これでデータのセルデータの取得はできました。次はセルの更新や挿入をしてみましょう。

特定のセルへの書き込み

セルへの書き込みはsetValueを使います。以下は指定したシートのA1へ書き込みます。

sheet.getRange('A1').setValue("テスト");
//以下でもいいですね
sheet.getRange(1,1).setValue("テスト");

行の挿入

insertRowAfterで指定した行の下、insertRowBeforeで指定した行の上に追加です。

sheet.insertRowAfter(1);//1行目の下に追加
sheet.insertRowBefore(1);//1行目の上に追加

セルへの書き込みと行の挿入を組み合わせれば特定の行のしたに挿入してデータを書き込むといったことができますね。

まとめ

いかがだったでしょうか。簡単ですが以上のことができればスプレッドシートに対してはこれだけでも色々とできると思います。
今回はボタンからの実行でしたが、特定の時間がきたときやスプレッドシートが編集されたときをトリガーとしてスクリプトを実行といったこともできるので、色々と試してみてください。

おまけ - 備忘録

コードのフォーマット

エディタで整形するには
Shift + Alt + F (Macの場合は Shift + Option + F)

画像のダウンロード & セルに表示

var ss = SpreadsheetApp.getActiveSpreadsheet();
var allTaskSheet = ss.getSheetByName("全体シート");

var imageUrl = 'https://xxx.png'; // 画像のURL
var response = UrlFetchApp.fetch(imageUrl); // URLから画像を取得
var blob = response.getBlob(); // Blob形式に変換
allTaskSheet.insertImage(blob, 1, 7);//A7に表示

特定のセルの画像を削除

//cellNotationは'A1'などを指定する
function removeImageInCell(sheet, cellNotation) {
  // シート内の全ての画像を取得
  var images = sheet.getImages();

  // それぞれの画像についてチェック
  for (var i = 0; i < images.length; i++) {
    // 画像がアンカーされているセルを取得
    var anchorCell = images[i].getAnchorCell();
  
    // そのセルが指定されたセルかどうかをチェック
    if (anchorCell.getA1Notation() == cellNotation) {
      // 画像を削除
      images[i].remove();
    }
  }
}

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

エディタの横のギアマークの下にスクリプト　プロパティというものがあり、そこで設定した値をGASで取得できる

var scriptProperties = PropertiesService.getScriptProperties();
var myData = scriptProperties.getProperty('MY_DATA');

ダイアログボックスでYES/NO

var ui = SpreadsheetApp.getUi();
var response = ui.alert('更新確認', "よろしいですか？", ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
    
}else{

}