|
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); |
|
} |
|
} |