GAS入門スプレッドシートでの使い方

kabu

これ、僕ずっとやりたかったんですけど、
ようやくですね。

帰家

え？
何がですか？？

kabu

GASの記事です。

帰家

？
2024年の5月で国の補助が終わってしまうので、値上がりが気になっていて、何か良い対策があるなら教えて欲しいです！

kabu

・・・
今回はこんな感じで進めていきたいと思います。
① GASについて
② 実際にGASを動かしてみる
③ GASにできること、できないこと

GASについて
実際にGASを動かしてみる
セルからセルに値をコピペするスクリプト
スプレッドシートからスクリプトを実行できるボタンの設置
- GASで取得する行数を、スプレッドシート側で指示するためのセルを設置する
GASにできること、できないこと
- GASで出来ること
- GASでは出来ないこと
まとめ

GASについて

kabu

えー
世にはもう、そうりゃあ多くのGAS入門の記事があるので、
あえてここでは多くは語りませんが、

GASとは・・・
Google Apps Script のことで、Googleのプラットフォームに特化・適応したプログラムです。
スプレッドシートをはじめ、Googleドキュメント、Gmailなどなど、Googleのサービス上でユーザーが作成して動かすことができます。
WEBサイトに使われているJavaScriptに似ていて、使用する用途的にExcelのVBAとよく比較されますが、似て非なるものですね。

簡単に言うと・・・

スプレッドシートのセルの情報を自動で更新する
Gmailでメールを作成・送信する
GA4やGoogle Search Consoleからデータを取得する

kabu

Googleのサービス内や、そのサービス間をまたいで動かすプログラムが作れます。

帰家

おおお・・・
Googleサービスを連携して動かせるなんて、なんだか革新的な香りがします・・・っ！
でも、難しいんでしょ？？

kabu

難しいことをやろうと思えば難しいプログラムになりますが、簡単なことなら簡単なプログラムで実行できます。
ものは試しってことでやってみましょう！

実際にGASを動かしてみる

kabu

ここでは、スプレッドシートの「セルの値を別のセルにコピペする」って内容でGASの動かし方を見ていきましょう。

kabu

最終的な仕上がりはこんな感じ。
スプレッドシート側で行番号を指定し、指定したセルの値をコピペするってスクリプトです。
スクリプト自体もスプレッドシートで実行できるようにしています。

※ 仕上がりイメージ

function myFunction() {

  //************ ファイル・シート・セルなどの設定 ************//
  
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //スプレッドシートの指定シートを取得
  var sheet = ss.getSheetByName("コピペ");
  
  //シートの指定セルと、その値を取得
  var copy_num = sheet.getRange('B1').getValue();
  var paste_num = sheet.getRange('B2').getValue();

  //「copy_num」で取得した値でセルを指定し、値を取得
  var copy = sheet.getRange('A' + copy_num).getValue();


  //************ 上で取得した値を動かすスクリプト ************//
  
  //「copy_num」で取得した値でセルを指定し、値を削除
  sheet.getRange('A' + copy_num).clearContent();

  //「paste_num」で取得した値でセルを指定し、「copy」の値を入力
  sheet.getRange('A' + paste_num).setValue(copy);

  //ログ出力
  Logger.log(copy);
}

※　最終的なGASのスクリプト

新規ファイルの作成

まずは新しいファイルを作りましょう。
上部メニュー　＞　「ファイル」　＞　「新規作成」　＞　「スプレッドシート」　ですね。

新しいファイルが立ち上がったらシートの名前を変更します。
これ、忘れがちですが意外と重要だと思います。
名前は好きなものを入力してください。

続いてGASを作成します。
上部メニューから　「拡張機能」　＞　「Apps Script」　です。

こんな画面が立ち上がります。
これが、GASの管理画面になります。

プロジェクトに名前をつけておきます。
好きな名前をつけて結構です。

スプリクトには次のように入力してください。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
  Logger.log(copy);
}

スクリプトとスプレッドシートを紐づける

kabu

各行について説明していきますね。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
  Logger.log(copy);
}

この行の意味は、アクティブな（直近で動かした）スプレッドシートを取得しています。

kabu

「このスクリプトで、どのスプレッドシートを操作しますか？」
ってのを指定しているイメージですね。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
  Logger.log(copy);
}

この行では、スプレッドシートの中の「シート」を指定しています。

kabu

ここでは、先ほど名前をつけた「コピペ」という名前のシートを選択するよう指示しています。

セルの値を取得する

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
  Logger.log(copy);
}

kabu

この行では、取得したシートの中の「A1」セルを選択し、
その「A1」セルの値を「copy」という変数に格納しています。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
  Logger.log(copy);
}

kabu

これはログ（GAS上で結果を表示する機能）を表示させています。

スクリプトを実行する

スクリプトを作成したら「保存」しておきます。
メニューにあるフロッピーディスクマークが「保存」ですね。

スクリプトを「実行」します。

セキュリティ確認

帰家

え！？
あれ？？
なんか「承認が必要です」って出てきましたよ汗

kabu

はい。
作成したファイルで初めてスクリプトを動かすときには必ず承認が必要になってきます。
これ、結構面倒ですが慣れちゃいましょう。

「権限を確認」を押します。

「アカウント」を選択します。

「このアプリはGoogleで確認されていません」と出てきます。
そりゃそうですよね。僕が今作ったものですので。

左下にちっちゃく表示されている「詳細」をクリックします。

更に「（安全ではないページ）に移動」を押して進みます。

帰家

なんだかすごく危険なことしているみたいに感じちゃいますね・・・

kabu

Googleさん的には、あんまりGASを作って欲しくないのかもしれませんね。
それか、「作ったものには自分で責任持てよ」と。

「スクリプトが Google アカウントへのアクセスをリクエストしています」とでるので、「許可」を押します。

kabu

これでようやくGASを実行出来る準備が整いました。

改めて、「実行」をクリックします。

実行結果を確認する

実行結果が「実行ログ」に出力されます。
スクリーンショットでは「11：23：07 情報」となっているものがそうです。

帰家

あのう・・・
これは上手くいったってことなんですか？？

kabu

エラーが出ていないので「上手く」いっています。
でも、これでは何も手応えがないですよね。

kabu

それもそのはずで
このスクリプトでは、作成したファイルの「コピペ」シート、「A1」セルの値を取得してくるものでした。
「コピペ」シートにはまだ何も入力されていないですからね。

「コピペ」シートの「A1」セルになんでもいいので文字を入れてみてください。

kabu

また「実行」を押します。

すると今度は「A1」セルに入力した文字が「実行ログ」に表示されました。

帰家

おおお！
本当だ！出来ました出来ました！！

スプレッドシート進捗管理表条件付き書式で見やすいリスト作り

条件付き書式を活用し、次にやらなければならないタスクが一目でわかる「見やすい・分かりやすい管理表」の作成方法をスクリーンショットを交え分かりやすく紹介します。

スプレッドシート「メールアドレス」を抽出する関数を「名前付き関数」で作る

この「REGEMAIL」関数を使えば、日本語とメールアドレスが混在したテキストの中から「アドレス」のみを抽出できます。複数のアドレスが入っていても抽出可能ですし、区切り文字の設定によって、「; 」でつなげたり、アドレスごとに改行したり、なかなか自由度が高い関数になっているかと思います。

スプレッドシート QUERY関数・除外リスト実装した「名前付き関数」

QUERY関数で1項目ずつの除外指定ではなく、「列範囲」で除外指定できる数式の作成方法について紹介・解説しています。今までは「not like ('%%')」なんて除外個数文書いていましたが、この数式を使えば「B1:B」といった形で指定出来ます。

セルからセルに値をコピペするスクリプト

帰家

ただ・・・
これをどう業務に使うのか、イメージがわかないのですが・・・

kabu

そうですね。
では、もう少し実用的な・・・
セルからセルに値をコピペするスクリプトに改変しましょう。

sheet.getRange('A2').setValue(copy);

この一文を追加してみてください。

kabu

また「実行」を押してみてください。

スプレッドシートを見てみると、「A2」セルに「A1」セルの内容がコピペされているのが分かります。

sheet.getRange('A1').clearContent();

続いてこの一文も追記します。

また「実行」を押してから、スプレッドシートを確認してみてください。

帰家

あっ！
A1セルの値が消えていますね！

kabu

追記したテキストについて解説していきますね。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
　　　　sheet.getRange('A1').clearContent();
　　　　sheet.getRange('A2').setValue(copy);
  Logger.log(copy);
}

この一文では「A2」セルを選択し、「setValue」＝「このセルに値を入れてね」、「copy」って変数の。って指示になります。
「copy」変数は「A1」セルの値でしたね。
なのでこの処理は「A1」→「A2」に値をコピペしている。ってことになります。

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("コピペ");
  var copy = sheet.getRange('A1').getValue();
　　　　sheet.getRange('A1').clearContent();
　　　　sheet.getRange('A2').setValue(copy);
  Logger.log(copy);
}

続いてこの処理についてですが、「A1」セルを選択し、「clearContent」すなわち、セルの値を消しています。

コード全体の流れをもう一度確認してみると、

アクティブなスプレッドシートを選択
そのスプレッドシートの「コピペ」というシートを選択
copy という変数に「A1」セルの値を格納
「A1」セルの値を削除
「A2」セルに copy の値を入力

という処理をおこなっています。

kabu

ちなみに「clearContent」と「setValue(copy)」の並び順はどちらが上でも構いません。

スプレッドシートからスクリプトを実行できるボタンの設置

帰家

ふむふむ。
書いてあるテキストについてはなんとなく分かりました！
でもこれ、私はスプレッドシートをメインに使っているので、スプレッドシートだけでポンポンっと実行することはできないですか？？

kabu

ポンポンはー・・・よく分かりませんが
ボタンを設置して、スプレッドシート側からスプリクトを実行できるようにしましょう。

上部メニューから　「挿入」　＞　「図形描画」　をクリックします。

図形描画エディタが開くので、「テキストボックス」でも入れてみましょう。

画面上でドラックして図形を描画してみてください。
テキストには「実行」と入力してみます。

描画した図形は「塗り色」の変更や

「枠線」スタイルの変更をおこなえます。

三点リーダ「…」（もっと見る）を押すと、
テキストの調整がおこなえます。

左右を「中央」揃えにしたり

上下も「中央」にしたり

「文字の色」も変更可能です。

背景色を「グラデーション」にすれば、こんな感じのボタンも作成できます。
出来上がったら「保存して閉じる」です。

先ほど作成したボタンも適当な大きさ、場所に調整して配置してください。
配置が終わったら、ボタンの右にある「…」を押します。

「スクリプトを割り当て」と出るので、スクリプトの名前を指定します。
デフォルトから変えてない方は「myFunction」ですね。
これで「確定」を押します。

ちなみに、スプリクトの名前とは作成したスクリプトの「function 」に続くテキストのことです。

kabu

これで、スクリプト側でおこなっていた「実行」が、スプレッドシート側に設置したこの「実行」ボタンでできるようになりました。

GASで取得する行数を、スプレッドシート側で指示するためのセルを設置する

帰家

スプレッドシートから「このセルをコピーして」って指定できるようにもできますか？？

kabu

うん。できますよ。
じゃあ続いて、コピペをするセル自体もスプレッドシート側で指示できるようにしたいと思います。

	A列	B列
1	copy	5
2	paste	7
3
4	Title
5	（任意のテキスト）

スプレッドシートを上記のように変更してください。
この「B1」セルでコピーする行番号を指定し、「B2」セルではペーストする行番号を指定します。
これらのセルの値をGAS側で取得し、スクリプトの処理を変えられるというものにしていきたいと思います。

kabu

GASのスクリプトを次の内容に上書きしてください。

function myFunction() {

  //************ ファイル・シート・セルなどの設定 ************//
  
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //スプレッドシートの指定シートを取得
  var sheet = ss.getSheetByName("コピペ");
  
  //シートの指定セルと、その値を取得
  var copy_num = sheet.getRange('B1').getValue();
  var paste_num = sheet.getRange('B2').getValue();

  //「copy_num」で取得した値でセルを指定し、値を取得
  var copy = sheet.getRange('A' + copy_num).getValue();


  //************ 上で取得した値を動かすスクリプト ************//
  
  //「copy_num」で取得した値でセルを指定し、値を削除
  sheet.getRange('A' + copy_num).clearContent();

  //「paste_num」で取得した値でセルを指定し、「copy」の値を入力
  sheet.getRange('A' + paste_num).setValue(copy);

  //ログ出力
  Logger.log(copy);
}

ちなみに、変えた箇所は4点

コメントの追加
「copy_num」変数の追加
「paste_num」変数の追加
値を取得するセルを変数を使用した書き方に変更

コメントの追加

function myFunction() {

  //************ ファイル・シート・セルなどの設定 ************//
  
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //スプレッドシートの指定シートを取得
  var sheet = ss.getSheetByName("コピペ");
  
  //シートの指定セルと、その値を取得
  var copy_num = sheet.getRange('B1').getValue();
  var paste_num = sheet.getRange('B2').getValue();

  //「copy_num」で取得した値でセルを指定し、値を取得
  var copy = sheet.getRange('A' + copy_num).getValue();


  //************ 上で取得した値を動かすスクリプト ************//
  
  //「copy_num」で取得した値でセルを指定し、値を削除
  sheet.getRange('A' + copy_num).clearContent();

  //「paste_num」で取得した値でセルを指定し、「copy」の値を入力
  sheet.getRange('A' + paste_num).setValue(copy);

  //ログ出力
  Logger.log(copy);
}

「コメント」とは、処理とは関係のないテキストで、人間がわかりやすいように「この処理はこういう意味」っていうのをメモしているものですね。
今回の場合だと「タイトル」的に使っていたり、処理に何をさせているのかを書いています。

「copy_num」変数の追加

function myFunction() {

  //************ ファイル・シート・セルなどの設定 ************//
  
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //スプレッドシートの指定シートを取得
  var sheet = ss.getSheetByName("コピペ");
  
  //シートの指定セルと、その値を取得
  var copy_num = sheet.getRange('B1').getValue();
  var paste_num = sheet.getRange('B2').getValue();

  //「copy_num」で取得した値でセルを指定し、値を取得
  var copy = sheet.getRange('A' + copy_num).getValue();


  //************ 上で取得した値を動かすスクリプト ************//
  
  //「copy_num」で取得した値でセルを指定し、値を削除
  sheet.getRange('A' + copy_num).clearContent();

  //「paste_num」で取得した値でセルを指定し、「copy」の値を入力
  sheet.getRange('A' + paste_num).setValue(copy);

  //ログ出力
  Logger.log(copy);
}

この部分です。
これは、「B1」セルの値を取得して、「copy_num」という変数に格納しています。

「paste_num」変数の追加

function myFunction() {

  //************ ファイル・シート・セルなどの設定 ************//
  
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //スプレッドシートの指定シートを取得
  var sheet = ss.getSheetByName("コピペ");
  
  //シートの指定セルと、その値を取得
  var copy_num = sheet.getRange('B1').getValue();
  var paste_num = sheet.getRange('B2').getValue();

  //「copy_num」で取得した値でセルを指定し、値を取得
  var copy = sheet.getRange('A' + copy_num).getValue();


  //************ 上で取得した値を動かすスクリプト ************//
  
  //「copy_num」で取得した値でセルを指定し、値を削除
  sheet.getRange('A' + copy_num).clearContent();

  //「paste_num」で取得した値でセルを指定し、「copy」の値を入力
  sheet.getRange('A' + paste_num).setValue(copy);

  //ログ出力
  Logger.log(copy);
}

この部分では、「B2」セルの値を取得して「paste_num」という変数に格納しています。
スプレッドシートの「7」という数値が入る想定です。

値を取得するセルを変数を使用した書き方に変更

function myFunction() {

  //************ ファイル・シート・セルなどの設定 ************//
  
  //アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //スプレッドシートの指定シートを取得
  var sheet = ss.getSheetByName("コピペ");
  
  //シートの指定セルと、その値を取得
  var copy_num = sheet.getRange('B1').getValue();
  var paste_num = sheet.getRange('B2').getValue();

  //「copy_num」で取得した値でセルを指定し、値を取得
  var copy = sheet.getRange('A' + copy_num).getValue();


  //************ 上で取得した値を動かすスクリプト ************//
  
  //「copy_num」で取得した値でセルを指定し、値を削除
  sheet.getRange('A' + copy_num).clearContent();

  //「paste_num」で取得した値でセルを指定し、「copy」の値を入力
  sheet.getRange('A' + paste_num).setValue(copy);

  //ログ出力
  Logger.log(copy);
}

これらの処理は、「‘A1’」とかで指定していたものを「‘A’ + copy_num」とか「‘A’ + paste_num」での指定方法に変更しています。
これは、「A」という文字と、先ほど新しく作成した「copy_num」とか「paste_num」の中身をくっつけて、その名前のセルを参照するように書き換えたものです。