liquidjumper Googleスプレッドシート,アプリ/ネットサービス Googleスプレッドシート-セルとシート名を連動させる方法|Google Apps Script(GAS)

Googleスプレッドシート-セルとシート名を連動させる方法|Google Apps Script(GAS)

googlespreadsheet_sheetname-to-cellvalue_image01_topimage

Googleスプレッドシートで、セルに入力した文字列と、シート名を連動させたい場面はありませんか?

Googleスプレッドシートで全国の人口や特産物などのデータをまとめる際に、各都道府県をシートで分けたい場合、いちいちシート名に「北海道」「宮城」「広島」など都道府県名を入力するのは手間。尚且、入力した内容とシート名の都道府県名を間違うミスも起きやすい。

例えば、シート名が自動的にセルに入力される、又は、セルの文字列を取得して自動的にシート名が変更されるようにすれば、入力内容とシート名を間違うミスも防ぐことができそう。

この記事では、Googleスプレッドシートで、セルの入力テキストとシート名を連動させる方法を紹介します。


シート名を取得してセルに入力する

残念ながらGoogleスプレッドシートにはシート名が取得できる関数はありません。

EXCELと同じ関数名の「cell()」はあるのですが、情報の種類を指す引数「”filename”」がGoogleスプレッドシートに無いため、シート名を取得する事ができません。

なので、Googleスプレッドシートでシート名を取得する場合は、Google Apps Script(GAS)でスクリプトコードを作成する必要があります。

シート名を取得しセルに入力する関数を作成

セルに数式を入力してシート名を取得する場合は、Google Apps Script(GAS)で独自の関数を作成します。

Google Apps Script(GAS)のスクリプトを作成するので、メニューから ツール > スクリプトエディタ をクリックし、スクリプトエディタを開きます。

ツール > スクリプトエディタ


スクリプトエディタが開いたら次のコードを入力します。関数名を「GETSHEETNAME」としました。

function GETSHEETNAME()
{
    return SpreadsheetApp.getActiveSheet().getName();
}


解説:

return SpreadsheetApp.getActiveSheet().getName();

アクティブなシートの名前を取得し、[return]で戻り値をセルに入力する。

保存したらGoogleスプレッドシートに戻ります。

実行:

任意のセルに関数名「GETSHEETNAME」で数式を入力すると、スクリプトが実行されシート名を取得しセルに表示されます。

=GETSHEETNAME()


シート名の文字列「北海道」が数式を入力したセルに表示されました。

文字列「北海道」が数式を入力したセルに表示されました。

入力するセルを指定する方法

前項の方法は、Google Apps Script(GAS)で作成した独自の関数を、任意のセルに設定できるのは利点ではありますが、シート毎に関数を入力する必要があるため、シート数が多い場合は非効率ともいえます。

取得したシート名を入力するセルの位置が決まっていて、追加したシートに関数を入力する手間を省きたい場合は、こちらの方法がオススメ。

■この項目で紹介する方法の利点

・シート名の変更が、即座にセルの文字列に反映される。
・スクリプトは全てのシートが対象になるので、シート毎に数式を入力する必要がない。

シート名を取得して指定したセルに取得したテキストを入力するスクリプトコードを書いて保存します。

function sheetnameToCellvalue(){
  
  let seetname = SpreadsheetApp.getActiveSheet().getName();
  SpreadsheetApp.getActiveSheet().getRange(23,3).setValue(seetname);

}


解説:

let seetname = SpreadsheetApp.getActiveSheet().getName();

アクティブシートのシート名を取得し、変数「seetname」に代入

SpreadsheetApp.getActiveSheet().getRange(1,1).setValue(seetname);

行:1,列:1 のセルに変数「seetname」に代入されたシート名のテキストを入力


■トリガーの設定

作成したスクリプトは、スプレッドシートを変更したタイミングで実行させたいのでトリガーの設定をします。

スクリプトエディタの左のメニューにある「トリガー」をクリック、又はスクリプトエディタのメニューバーから 編集 > 現在のプロジェクトのトリガー を選択

現在のプロジェクトのトリガー を選択


右下の[トリガーを追加]をクリック。

右下の[トリガーを追加]をクリック


実行する関数を選択:[実行するスクリプトの関数名を選択] 例:「sheetnameToCellvalue」

イベントのソースを選択:[スプレッドシートから]

イベントの種類を選択:[変更時]

「保存」をクリックして閉じます。

トリガーを設定


自身のGoogleのアカウントをクリック

Googleのアカウントをクリック


このアプリは確認されていませんと表示されたら、「詳細」をクリック。

「詳細」をクリック


“(安全ではないページ)に移動” をクリック。

無題のプロジェクト(安全ではないページ)に移動


“Googleアカウントへのアクセスをリクエストしています”で、「許可」をクリック。

「許可」をクリック


トリガーが追加されました。

これでセルへの入力をきっかけにして、スクリプトが実行されるはずです。

トリガーが追加されました


実行:

Googleスプレッドシートのセルに値を入力すると、スクリプトで設定したセルにシート名が入力されました。

スクリプトで設定したセルにシート名が入力されました
▲シート名を変更すると即座にセルの表記が変更したシート名に変わります。


他のシートでもスクリプトが実行されるので、シート毎に関数・数式を入力する手間がありません。

他のシートを編集しても、スクリプトが実行される

セルの値を取得してシート名を変更する

前項では、シート名を取得してセルに入力する方法でした。

では逆の手順、セルに入力されたテキストを取得してシート名を変更するにはどうしたら良いのでしょう。

前項で作成したトリガーを削除して、Google Apps Script(GAS)のスクリプトエディタに次のコードを入力。トリガーを「変更時」に設定し保存します。

function cellvalueToSheetname(){ 
  
  let cellvalue = SpreadsheetApp.getActiveSheet().getRange(1,1).getValue(); 
  SpreadsheetApp.getActiveSheet().setName(cellvalue); 

}


解説:

let cellvalue = SpreadsheetApp.getActiveSheet().getRange(1,1).getValue();

行:1,列:1 のセルのテキストを取得、変数「cellvalue」に代入

SpreadsheetApp.getActiveSheet().setName(cellvalue);

シート名を変数「cellvalue」に代入されたテキストに変更します。

実行:

指定したセルのテキストを変更すると、即座にシート名も変更されます。

指定したセルのテキストを変更すると、即座にシート名も変更されます


【PR】Amazon Music Unlimited 無料体験の新規登録

まとめ

Googleスプレッドシートで、セルの入力テキストとシート名を連動させる方法を紹介しました。

Googleスプレッドシートでデータ入力に夢中になると、シート名の編集が疎かになりがち。

整理されたスプレッドシートを作成するなら、データの内容と同様にシート名にも気を配るのを忘れないようにしたいところ。

そのような場合に今回紹介した方法は便利なので、是非参考にしてみてください。






【オンラインプログラミングスクール受講者No.1 TechAcademy(テックアカデミー)】

GmailやスプレッドシートなどGoogleの様々なサービスと連携、作業を自動化させるプログラム

[Google Apps Script (GAS)]

初心者でもGoogle Apps Scriptを使ったスプレッドシートの自動化やプログラミングの書き方を習得できる

Google Apps Scriptコース

専属のパーソナルメンターがビデオとチャットで学習の目標達成までサポート。

TechAcademy(テックアカデミー)の学習システムを体験できるTechAcademy 無料体験 も実施されています。



Twitter Facebook Pocket LINE はてブ

おすすめの関連記事
コチラの記事も読まれています
あわせてよく読まれている記事