Skip to content

Instantly share code, notes, and snippets.

@ttsukagoshi
Created November 18, 2020 03:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ttsukagoshi/d9fad5b8865066ddefa64be9cff208d0 to your computer and use it in GitHub Desktop.
Save ttsukagoshi/d9fad5b8865066ddefa64be9cff208d0 to your computer and use it in GitHub Desktop.
A Google Apps Script sample for a simple solution to manage the choices in list or multiple-choice items of a specific Google Form using Google Sheets.
const CONFIG = {
'sheetName': 'Choices', // Name of sheet
'cellFormUrl': { 'rowNum': 4, 'colNum': 2 }, // Row and column numbers of the cell that designates the URL of Google Form.
'rangeOffsetChoices': { 'rowNum': 6, 'colNum': 3 }, // Left- and upper-most cell of the range in which choices are listed.
'timestampItemTitle': '最終更新日時' // Title of the item in the target Google Form that denotes the timestamp of which the form was last updated.
};
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Choice Editor')
.addItem('Edit Choice', 'editChoice')
.addToUi();
}
function editChoice() {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
try {
// Gets the active spreadsheet and the target Google Forms file
let sheet = ss.getSheetByName(CONFIG.sheetName);
let form = FormApp.openByUrl(sheet.getRange(CONFIG.cellFormUrl.rowNum, CONFIG.cellFormUrl.colNum).getValue());
// Gets the choice list from the spreadsheet & formats the list into an array of objects
let choicesList = sheet.getRange(
CONFIG.rangeOffsetChoices.rowNum,
CONFIG.rangeOffsetChoices.colNum,
sheet.getLastRow() - CONFIG.rangeOffsetChoices.rowNum + 1,
sheet.getLastColumn() - CONFIG.rangeOffsetChoices.colNum + 1)
.getValues();
let questions = choicesList.shift();
let choices = questions.map((question, index) => {
return {
'questionTitle': question,
'questionChoices': choicesList.reduce((acc, val) => {
if (val[index].length > 0) {
acc.push(val[index]);
}
return acc;
}, [])
};
});
// Gets the form items
let formItems = form.getItems();
// For each question and its choices entered manually in the spreadsheet, set the choices for the corresponding item in Google Form
choices.forEach(question => {
let targetItems = formItems.filter(item => item.getTitle() == question.questionTitle);
if (targetItems.length > 1) {
throw new Error(`2 or more items in the form with the same question: ${question.questionTitle}`);
} else if (targetItems.length == 0) {
return;
}
let targetItemType = targetItems[0].getType();
if (!(targetItemType == FormApp.ItemType.LIST || targetItemType == FormApp.ItemType.MULTIPLE_CHOICE)) {
throw new Error(`The answer for the question "${question.questionTitle}" is neither a list nor a multiple choice. Check the item type.`);
}
let targetItem = (targetItemType == FormApp.ItemType.LIST ? targetItems[0].asListItem() : targetItems[0].asMultipleChoiceItem());
targetItem.setChoiceValues(question.questionChoices);
console.log(`Choices ${question.questionChoices} set for question "${question.questionTitle}"`); // log
});
// Updating the timestamp item
let timestampItem = formItems.find(item => item.getTitle() == CONFIG.timestampItemTitle);
let timestamp = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd HH:mm:ss');
timestampItem.asSectionHeaderItem().setHelpText(`フォーム更新日時: ${timestamp}`);
console.log(`Timestamp updated to ${timestamp}.`); // log
// Complete
console.log('editChoice completed.'); // log
ui.alert('All choices updated.');
} catch (error) {
let message = error.stack;
console.log(message); // log
ui.alert(message);
}
}

How to Use 使い方

Preparing 初期設定

  1. Copy the sample spreadsheet.
    サンプルスプレッドシートをコピーする。
  2. Enter, in the cell marked yellow (B4), the URL of the Google Form that you are editing.
    黄色セル(B4)に、編集しているGoogleフォームのURLを入力する。

Editing the Choices フォームの選択肢を編集する

  1. Enter the title, i.e., the questions, that you want to edit in the header section of the table of the spreadsheet (Row 6 of the sample sheet)
    編集対象であるフォームの質問文をスプレッドシートの表のヘッダ行(サンプルでは行番号6)に入力する。
  2. Enter the choices that you want to set under the respective titles. Note that the choices will be overwritten.
    それぞれの質問文の下に、選択肢を入力する。既存の選択肢は上書きされてしまうことに注意。
  3. From the menu, Choice Editor > Edit Choice
    メニューからChoice Editor > Edit Choiceで実行。
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment