セルの値とシート名連携あれこれ~Googleスプレッドシートのシート名とセルの値を連動させる~タイトルセルを変更するとシート名も自動で変更させたい

今回の試行錯誤の顛末のスクリプト。関数名がローマ字読みなところがダサい。

在庫管理などで、スプレッドシート(Spreadsheet)で月ごとにシートを作成する場合がある。最初にフォーマットとなるシートを作成してから、そのシートを毎月(または一年分)コピーして作成することが多い気がしている。

さらに、私のようなスプレッドシート初心者は、タイトルとなるセルを左上に作成し、そこにシート名と同じ内容を入力しておきたくなる。

フォーマットとなるシート(先月のシートなど)をコピーしてから、シート名の変更を忘れることはないのだけど(他のページへの参照設定でエラーが出るため気づく。。。)、タイトルセルの内容を変更し忘れていることもたまにあり、わかりやすくしたつもりが仇となるケースも。

一年分一気に作成する場合も、このシート名とセルの変更を何回もすることがまあ面倒に感じてくる。(コピペすら面倒になってくるから不思議。。。)

同じ内容ならどちらかを変更したときに連動して、もう一方に同じ内容が自動で入力されればいいのに、といつも思っていたので、調べて試行錯誤の末になんとか実現できた、という話です。

結果から書きますと、当記事の冒頭の画像のスクリプトで下記の動画のような動作となりました。

黄色いセルを変更するとシート名も自動で変更される。
function sheetNameRenkei() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getActiveSheet();
  var range = sht.getRange(1, 2);
  var date = range.getValue();
  var value = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy年M月');
  ss.renameActiveSheet(value);
}

↑スクリプトです。解説は後ほど。

試行錯誤あれこれ

最初に検索して出てきた方法が↓コチラ

●シート名を取得してセルの値にセットする

【コピペでOK】スプレッドシートでシート名を取得する方法

丁寧でわかりやすく、さらにコピペでOKという素晴らしい言葉に感服です。

こちらの方法は、スクリプトエディタで関数を作成し、その関数をセルに入力するという方法。

本当にコピペで簡単にできました。感動です。

独自の関数を作ってセルに入力して他の関数と同じように使えるということを初めて知ったので、感動も2倍に。

作成者のtanuhack様ありがとうございます。

さっそく解決できたと思ったところ、この方法は、初回の実行時はシート名がセルに入力されるのだけど、その後シート名を変更した場合はセルに反映されないのでした。。。

トリガーを「編集」時、「変更」時と作成しても、シート名の変更は「編集」、や「変更」とは判定されないようで。。。

トリガー追加画面。「編集」と「変更」の違いがよくわからない。編集はセルの内容が変更されたとき、変更はスプレッドシートの構造(シートや列の追加等)が変更されたとき、らしい。。。

なので、色々調べた結果、↓の動画のようにしてみました。

(尚、図形を配置してスクリプトを割り当てて実行ボタンにする方法は、iPad版スプレッドシートアプリでは使えませんでした。。。)

動画のキャプチャの方法がわからず、アナログにiPhoneで画面を撮影している。斜めなところが気持ち悪い。。。

作成した独自関数「sheetname()」の()内に「A1」と入力。

すると、A1セルが編集された時に関数が実行される。(トリガー設定は不要でした)

iPad等での入力も考えて、A1にチェックボックスを挿入。

関数を実行したい時にチェックを入れる(外す)。

───か、完璧だ。(ゴクリ)

やればできるじゃないか。我よ。

40歳目前でもやればできるのだ。

えーと、シートをコピーしてぇ。。。

シート名を打ち直してぇ。。。

チェックボックスを。。。ポチッと!

あーいいねぇ!iPadでもちゃんと動くねぇ!

もう一回。

えーと、シートをコピーしてぇ。。。

シート名を打ち直してぇ。。。

チェックボックスを。。。ポチッ。。。。。

───これってコピペと同じじゃない?手数的に。

手入力でカーソル動かしたり削除して再入力とかも手数が多い気がしてくる。。。

●セルの値を取得してシート名にセットする

というわけで、発想の転換。

タイトルとなるセルの値を取得して、その値でシート名を変更する。

これでは日付の表示が超長くなるのでした。。。

色々と調べた(盗んだ)結果、なんとか上記のように書いてみた。

セルの値を取得するにはgetRangeでgetValueであると。。。

getRangeでセルの場所を指定するには、()内の引数の最初の数字が行番号、次の数字が列のアルファベットをAを1としてカウントした数字であると。。。

なので今回はB1セルに日付が入っているので、getRange(1, 2)となると。。。

(※追記※ ダブルクォーテーションで囲めばセル番号のまま指定できるようで、getRenge(“B1”)でもできました。。。)

getValueした値をvalueに代入して、それをrenameActiveSheetで使うと。。。

実行。

─── シート名の日付が。。。。

そんなに正確に表現しなくていいのに。。。

まあ日付を正確な数字として理解してくれるからそれを元に計算ができるわけなのだけど。。。(「先月在庫」を今月マイナス1で引っ張ってこれるのもこのおかげ)

なので、簡略化した表記にするために一手間加える必要があるようで。。。

var date = range.getValue();
var value = Utilities.formatDate(date, ‘Asia/Tokyo’, ‘yyyy年M月’);

5行目を変更、6行目に追加

これで「@@@@年@@月」だけの表記になりました。

(スクリプト全体は記事冒頭を参照してください。)

トリガーを編集時(変更時も?)に設定すれば、セルの日付変更後、即シート名が自動で変更されます。

セル内なら「日付」としてクリックしてカレンダーから入力できるので、キーボードを使わないで操作可能。

ああスッキリ。

参考にさせて頂いたみなさん、本当にありがとうございました。

今後は

このままだと、シートをコピーして作成した時に、セルの内容が同じなので、同じシート名となってしまい、エラーが起きている模様。。。(エラー発生率参照)

エラーと言っても、なにかアラートが出て動きが止まる、ということもなく、特に作業に影響はなかったので、エラーを無視する、ということでやり過ごせしていいのかな?

丁寧にというか本来は、エラー対策として「シート名が同じ場合は処理を実行しない」というスクリプトを書く必要があるんでしょうね。。。

間違っている点、至らない点等あると思いますので、その際はご指導ご鞭撻いただけたら嬉しく思います。

最後までお読みいただきありがとうございました。

【追記】2021年10月15日

私の知識不足で、当ブログのプラットフォームであるWordPressではシングルクォーテーションやダブルクォーテーションが、アポストロフィに自動で変換されてしまうことを理解できていませんでした。

スクリプトをGoogleSpreadsheetのスックリプトエディタから、単純にWordPressにペーストしてしまっており、シングルクォーテーション等がアポストロフィに変換されていて、それをコピペすると当然エラーとなり動きません。。。

大変失礼いたしました。

スクリプトをブログに載せる際には、テキストの書式を「コード」にすること。そうすると、記号やインデントが正しく表示できる。ということを今理解しました。

諸先輩方に憧れて、コードを載せてみたものの、おこがましい結果となってしまいました。お恥ずかしい。。。大変勉強になりました。

また、当記事冒頭のスクリプトをシート名が同じだった場合のエラーに対応させたバージョンを下記に記します。エラー処理として正しいかはわかりませんが。。。

対応の内容としては、B1セルの日付が過去のシート名と同じ内容の場合は、シート名にコピーしない。違う場合はコピーする。エラー通知が鬱陶しいので、例外が発生しても何もしない。。。的な内容だと思います。。。

引き続きご指導ご鞭撻、ご感想等いただけると嬉しいです。

function sheetNameRenkei() {
  try {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sht = ss.getActiveSheet();
   var range = sht.getRange("B1");
   var date = range.getValue();
   var value = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy年M月');
   var shts = ss.getSheets();
   for(var i = 0; i < shts.length; i++){
     var sheetName = shts[i].getSheetName();
     if(sheetName == value) {
       break;
     } else {
       ss.renameActiveSheet(value);
     }
   }
  } catch(e) {
  }
}

最後までお読みいただきありがとうございました。

セルの値とシート名連携あれこれ~Googleスプレッドシートのシート名とセルの値を連動させる~タイトルセルを変更するとシート名も自動で変更させたい」への7件のフィードバック

    • コメントいただきありがとうございます。私もいろんな方々を参考にさせていただいているので、その方々のように有益な情報として扱っていただけたことを嬉しく思います。当ブログをご覧いただきありがとうございました。

      いいね

  1. コメント失礼します。
    以下のような表示がでてしまいます…。初心者なので、対処方法がわかりません…。。。。

    構文エラー: SyntaxError: Invalid or unexpected token 行: 6 ファイル: コード.gs

    いいね

    • 勉強中さま
      コメントいただきありがとうございます。
      すいません。私も初心者なのでエラーの対処がわかりません。
      6行目で、記号などが多かったり少なかったりしていないでしょうか?
      私もよくやってしまうのですが、行末のセミコロンを忘れていたり、単純にタイプミスしてしまったり。。。
      あと、シート名が同じ場合に起こるエラーに対応したスクリプトを書いてみました。
      こちらもお試しください。
      B1セルに入力した内容がシート名となります。

      function sheetNameRenkei() {
      try {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sht = ss.getActiveSheet();
      var range = sht.getRange(“B1”);
      var date = range.getValue();
      var value = Utilities.formatDate(date, ‘Asia/Tokyo’, ‘yyyy年M月’);
      var shts = ss.getSheets();
      for(var i = 0; i < shts.length; i++){
      var sheetName = shts[i].getSheetName();
      if(sheetName == value) {
      break;
      } else {
      ss.renameActiveSheet(value);
      }
      }
      } catch(e) {
      }
      }

      いいね

      • 返信有難うございます!
        コピペしてみたのですが、どうも日付のアポストロフィが駄目だったみたいで、ダブルクォーテーションに変更したら無事に動きました!
        めちゃ便利です。有難うございました!

        いいね: 1人

      • そこでしたか!これは私の確認不足でした。
        今検索したところ、当ブログのプラットフォームであるWordPressでは、シングルクォーテーションやダブルクォーテーションが、自動でアポストロフィに変換されてしまうようです。。。
        知りませんでした。。。
        気がつきませんでした。ご指摘いただきありがとうございました!

        いいね

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中