投稿者: SekineKaoru

このサンプルでは、Google スプレッドシート に試験問題をリストし、そこから Google フォーム のテストを作成する方法をご紹介します。

以下のリンクからサンプルシートがコピー可能です。まずはサンプルで利用したい Google アカウント を選択し、スプレッドシート をコピーしてお使いください。

Google フォーム の操作サンプル

スプレッドシート をコピーしたら、適当な質問をリストし、以下のテスト作成ボタンをクリックしてください。その後 Google ドライブ を確認すると、テストのフォーム が作成されています。

＜応用編＞

それではここから一歩進んで、100題などの問題をスプレッドシート にリストしておき、その中から好きな数の問題数をランダムに抽出してフォーム を作成することをシミュレーションしたスプレッドシート を作成してみたいと思います。以下から別のサンプルをコピーしてください。

Google フォーム の操作サンプル – ランダム抽出対応

先程はボタンにスクリプトを実行する機能を追加していましたが、メニューに追加する方法もあります。以下のスクリプトをコピーしてスプレッドシート についている Google Apps Script の コード.gs の先頭に貼り付けてみます。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('テスト作成');
  menu.addItem('すべての問題でフォーム を作成', 'createForm1');
  menu.addItem('ランダムに問題選択', 'selectQuestions');
  menu.addItem('問題抽出シートからフォーム を作成', 'createForm2');
  menu.addToUi();
}

function createForm1(){
  ShtName = 'フォームテスト';
  CreateTestForm(ShtName);
}

function createForm2(){
  ShtName = '問題抽出シート';
  CreateTestForm(ShtName);
}

保存し、スプレッドシート のほうをリロード、もしくはファンクションの onOpen を選択して実行してみてください。これはシートが開かれたときに実行されるイベントハンドラ（何かのイベントで実行される機能）ファンクションです。

こちらはメニューバーにアイテムを追加するスクリプトです。以下のようにメニューが追加されたのがわかるかと思います。

・すべての問題でフォーム を作成：createForm1（すべての問題でフォーム を作成）

・ランダムに問題選択：selectQuestions (N2の黄色のカラムに入った数値分の問題を出現回数の低い順に選択して「問題抽出シート」に記述）

・問題選択からフォーム を作成: createForm1(「問題抽出シート」からフォーム を作成）

次に、「フォームの URL 」のタブには、作成されたフォーム のURL がアップデートされるように以下のコードを

Browser.msgBox(FormTitle+’の作成が完了しました。\\nフォームは Google Drive の『ドライブ』に存在します。’);

の直前に記入してみましょう。

 // フォーム作成完了表示
  var sht3 = SpreadsheetApp.getActive().getSheetByName("フォームのURL");
  
  sht3.getRange(1,2).setValue(Form.getPublishedUrl());
  sht3.getRange(2,2).setValue(Form.getEditUrl());

これでフォーム が作成された際、ドライブ にいかなくても、スプレッドシート の「フォーム のURL」のタブからURLのリンクがわかるようになりました。

完成したスクリプトのついたスプレッドシート はこちらです。

Google スプレッドシートからフォームを作成（応用編・改修版）

このサンプルでは、Google スプレッドシートで予定を入力し、Google カレンダーに出力するコードを記載しています。

以下のリンクからサンプルシートがコピー可能です。まずはカレンダー反映したいGoogle アカウント を選択し、スプレッドシート をコピーしてお使いください。

Google カレンダーの操作サンプル

予定をGoogle カレンダー で確認する

スプレッドシート をコピーしたら、適当な日付に予定を入力し、以下の出力ボタンをクリックしてください。その後カレンダーのリンクを確認すると、予定が反映されています。

＜応用編＞

1.カレンダー ID の認識

スプレッドシートから任意のカレンダーに出力します。

まずはカレンダー ID を確認します。自分のカレンダーはカレンダーの設定から「カレンダーの統合」を開くと載っています。

もっと簡単に先程のスプレッドシート の Apps Script からこれを確認する方法もあります。

まずは以下のスクリプトを先程コピーしたスプレッドシートのメニューの拡張機能＞Apps Scriptで開いてみましょう。

すでにコードが書かれていますが、その下に以下のコードをはりつけて、以下のアイコンから保存します。

/**
 * Lists the calendars shown in the user's calendar list.
 * @see https://developers.google.com/calendar/api/v3/reference/calendarList/list
 */
function listCalendars() {
  let calendars;
  let pageToken;
  do {
    calendars = Calendar.CalendarList.list({
      maxResults: 100,
      pageToken: pageToken

    });
    if (!calendars.items || calendars.items.length === 0) {
      Logger.log('カレンダーがありません。');
      return;
    }
    // Print the calendar id and calendar summary
    for (const calendar of calendars.items) {
      Logger.log('%s (ID: %s)', calendar.summary, calendar.id);
    }
    pageToken = calendars.nextPageToken;
  } while (pageToken);
}

左側のサービス からGoogle Calendar API を選択して追加します。

スクリプトの下部の実行ログに、カレンダーの名前とIDが表示されます。

2.任意のカレンダーに予定を書き出すようにスクリプトを変更

開いているスクリプトの15行目の以下の部分を書き換えます。これでデフォルトのカレンダーではなくカレンダーIDで指定しているカレンダーへの書き出しを指示します。

let Calendar = CalendarApp.getDefaultCalendar();
　　　　　　　　　　↓
let Calendar = CalendarApp.getCalendarById();

次に実行ログで出てきたカレンダー ID をコピーして、以下の部分に ” ” のコテーションマークとともに入力します。あるいは入力した文字を選択しながらShift + 2 をしても、文字列として認識します。

getCalendarById(CALENDAR_ID);
↓
getCalendarById(”abcdefg”);

プロジェクトを保存して、スプレッドシートに戻り、日付部分の出力ボタンを押すか、メニューから Convert2Calender を実行すると、今度は別のカレンダーに予定が反映されます。カレンダーから予定が反映されているか確認ができます。

<おまけ – カレンダーの予定をスプレッドシートに書き出し＞

最後にカレンダーの予定をスプレッドシートに書き出してみましょう。

最初にコピーしたスプレッドシートにシートを1枚追加します。

Apps Script に戻って一番下に以下のコードを貼り付けてみましょう。

以下の部分は適当に書き出したい期間を変更します。

const startTime = new Date(‘2022/04/01 00:00:00’); //スプレッドシートに書き込みたい期間のはじめの日付を変更して記述
const endTime = new Date(‘2022/12/01 00:00:00’);//スプレッドシートに書き込みたい期間の終わりの日付を変更して記述

function getCalendarEvents() {
  var mySheet=SpreadsheetApp.getActiveSheet(); //シートを取得
  const CALENDAR_ID = 'xxxxxxxxxxx'; //カレンダーIDを取得
  
  //ヘッダを入力
  mySheet.getRange("A1").setValue("イベント名");
  mySheet.getRange("B1").setValue("開始時間");
  mySheet.getRange("C1").setValue("終了時間");
  mySheet.getRange("D1").setValue("イベント登録者名");

  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);  
  const startTime = new Date('2022/04/01 00:00:00'); //スプレッドシートに書き込みたい期間のはじめの日付を変更して記述
  const endTime = new Date('2022/12/01 00:00:00');//スプレッドシートに書き込みたい期間の終わりの日付を変更して記述
   
  const events = calendar.getEvents(startTime, endTime);
  const values = [];
  
  for(const event of events){
    const record = [
      event.getTitle(),
      //event.getStartTime(),
      Utilities.formatDate(event.getStartTime(),"GMT+0900","yyyy年MM月dd日 HH時mm分") ,//文字列に変換
      //event.getEndTime(),
      Utilities.formatDate(event.getEndTime(),"GMT+0900","yyyy年MM月dd日 HH時mm分"),//文字列に変換
      event.getCreators()[0]
    ];
    values.push(record);
  }
 
  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
}

前と同様、カレンダー ID は表示したいものを入力します。保存して getCalendarEvents 関数を選び、実行します。

スプレッドシート上に該当する期間のカレンダーの内容が反映されているかと思います。

管理者向けサンプルでは、Google Workspace for Education のデータを Google スプレッドシートから Google Apps Script で操作する方法をご紹介します。ここで扱うサンプル ソースコードはそれぞれリンクされているGitHub ページからダウンロードが可能で、またそれぞれのページの以下のロゴのリンクからApps Script 付きのスプレッドシートがコピー可能です。

スプレッドシートをコピーするには Google アカウントが必要です。学校のアカウントでアクセスが制御されている場合などは Gmail アカウントでお試しください。

その他 Apps Script のサンプルは以下のリンクにも用意があります。
Apps Script Sample > GitHub