見出し画像

#82 スプレッドシートのファイル名をフォームの名前に合わせる

ともかつのノート

Google フォームで回収した回答を、スムーズに Google スプレッドシートで集計できるように、QUERY や VLOOKUP といった関数を駆使したシートを作成されるケースがあると思います。

そういったケースでは、

  • 現状：

    1. フォームの出力先となるスプレッドシートを作成

    2. そのスプレッドシートにシートをコピー

といった感じで、フォームから先に操作しているケースが多いように思います。
これを、

  • 変更案：

    1. フォームのコピーを作成して適切な名前を設定

    2. そのスプレッドシートにフォームの回答を出力させる

とスプレッドシートから先に操作するようにしたら、準備したスプレッドシートがうまくコピーできるように思います。

しかしながら、この「変更案」を実現しようとすると、

テンプレートのコピーを生成する際に「集計シートのコピー」みたいなファイル名になり、たくさん集計シートを活用する場合はファイル名を都度変えないと何の集計を行ったのかわからなくなりそう

という懸念がでてきそう。この懸念を解消するためのプログラムを作ってみました。

作成したプログラム

GAS で利用できる API を利用すると、フォーム側からリンクされているスプレッドシートをたどる API を見つけられませんでしたが、シート側はリンクされているフォームをたどることはできました。

冒頭の「変更案」のように操作したときに、スプレッドシートのファイル名を設定・変更せず、「…のコピー」というような名前になっていたとしても、今回作成したプログラムを実行すれば、リンクされているフォームの名前にあわせて、スプレッドシートのファイル名を変更できます。

Google アカウントでログインした状態で、以下の URL にアクセスして、スプレッドシートをコピーしてください。

http://bit.ly/3ICAfm6　

コピーされたスプレッドシートには下図のような動作を説明したシート「説明」だけが存在しています。

コピーされたスプレッドシート

追加されているメニュー「ファイル名 変更」から「変更実行」を選択すると、スプレッドシート内にフォームにリンクされているシートが存在していれば、そのフォームの名前にあわせてスプレッドシートのファイル名を変更します。

メニューから「ファイル名 変更」→「変更実行」

フォームにリンクされているシートが存在していなければ、画面右下に下図のようなメッセージが表示されます。

リンクされているシートが存在していない場合のメッセージ

フォームにリンクされているシートが複数存在している場合には、左側にあるシートを対象にファイル名の変更を行います。

プログラムをはじめて実行する際には、アカウントによる確認作業が必要になります。詳しくは以下の投稿をご覧ください。

実際のプログラム

以下のような 40行余りのプログラムです。今回のプログラムでは、このプログラムだけが重要で、シート「説明」の中身には、説明だけでプログラムの動作には関係がありません。

目的のスプレッドシートに、以下のプログラムをコピペすることで、そのスプレッドシートにこのプログラムの機能を持たせることできます。

'use strict'                                        // 変数の宣言を強制

const FILENAME_ADD = '（回答）';                      // ファイル名に付加する文字列
const MODE = true;                                  // true:ファイル名、false：フォームのタイトル

function onOpen() {
  let ui = SpreadsheetApp.getUi();                    // Uiクラスを取得する
  let menu = ui.createMenu('ファイル名 変更');        // Uiクラスからメニューを作成する
  menu.addItem('変更実行', 'changeSpreadsheetName');  // メニューにアイテムを追加する
  menu.addToUi();                                     // メニューをUiクラスに追加する
}

/**
 * この関数を呼び出されたシートがリンクされているフォームの「ファイル名」もしくは「タイトル」を
 * もとにして、スプレッドシートの名前を変更する。
 */
function changeSpreadsheetName() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // 現在のスプレッドシートを取得
  let fileName = '';                                // 設定するファイル名
  let sheets = ss.getSheets();                      // スプレッドシート内のシートをすべて取得
  for (let i = 0; i < sheets.length; i++) {         // 存在しているシートの文だけくり返す
    let url = sheets[i].getFormUrl();               // リンクされているフォームを取得
    if (url == null) {                              // このシートは、フォームにリンクされていない
      continue;                                     // → 次のシートへ
    }

    // リンクされているフォームの URL からファイル ID を取得する
    let formId = url.replace('https://docs.google.com/forms/d/', '').replace('/viewform', '');
    if (MODE == true) {                             // 【ファイル名】から
      fileName = DriveApp.getFileById(formId).getName();
    }
    else {                                          // 【フォームのタイトル】から
      fileName = FormApp.openById(formId).getTitle();
    }
    ss.rename(fileName + FILENAME_ADD);             // スプレッドシートのファイル名を変更する
    break;                                          // 処理したので、くり返しは終了
  }

  if (fileName == '') {                             // フォームにリンクされたシートが存在しなかった
    ss.toast('このスプレッドシートには、フォームにリンクされたシートが存在していませんでした。');
  }
}

プログラムとしては、いかのような流れで処理を行っています。

  1. スプレッドシート内のシートについて、フォームにリンクされているシートであるかを getFormUrl() で確認する。

  2. フォームにリンクされていれば、得られたフォームの URL からファイル ID を抜き出す。

  3. ファイル ID から、フォームのファイル名を取得する。　※もしくは、フォームのタイトルを取得する。

  4. フォームのファイル名に付加する文字列「（回答）」を付加したものを、スプレッドシートに設定する

カスタマイズ１：付加する文字列

上記の配布しているプログラムでは、Google フォームで回答を書き出すスプレッドシートを新規に作成するときと同様に、Google フォームのファイル名に「（回答）」を付加するようにしてあります。

別の文字列を付加したい場合や、負荷したくない場合には、プログラムの冒頭で定義されている FILENAME_ADD の内容を変更してください。

カスタマイズ２：元になる名前

上記の配布しているプログラムでは、Google フォームの動作と同じように、Google フォームのファイル名を元にスプレッドシートのファイル名を設定しています。

プログラム冒頭で定義されている MODE を false に変更することで、フォームのファイル名ではなく、フォームのタイトルを元にスプレッドシートのファイル名を設定するように変更できます。

最後に

今回のプログラムは、校務自動化の取り組みについて扱う「自動化工房『楽』」でやり取りされた内容で作成したものです。Google のアプリと、学校の運用のずれを、今回のようなプログラムでうまくつなげられたらいいなと思っています。

最後に、お決まりのフレーズなどを書いておきます。

  • 一応の動作確認は行っているものの、不慮のトラブルによって損害等が生じても、責任はとれませんので予めご了承ください。

  • コメントを含めても 40行あまりのスクリプトであり、実行に際して目的外の場所への書き出しや収集などは行っていません。

  • 特別なエラー処理は行っていないので、意図しないケースでエラーが発生してしまうかもしれません。どうにもならない場合には、ご連絡ください。

わたし自身にしてみると、このような「スクリプトを作ること」が目的になっているような感じですが、このスクリプトが何かの役に立てば幸いです。
「スキ ♡」を押してもらえると、このようなプログラム作成の励みになります。😍

この記事が気に入ったらサポートをしてみませんか？