/**
* @license MIT
*
* © 2019-2020 xfanatical.com. All Rights Reserved.
*
* @since 1.1.2 interface fix
* @since 1.1.1 Optimize performance (continued)
* @since 1.1.0 Optimize performance
* @since 1.0.0 Add all edit response urls and update new urls for new submissions
*/
function registerNewEditResponseURLTrigger() {
// check if an existing trigger is set
var existingTriggerId = PropertiesService.getUserProperties().getProperty('onFormSubmitTriggerID')
if (existingTriggerId) {
var foundExistingTrigger = false
ScriptApp.getProjectTriggers().forEach(function (trigger) {
if (trigger.getUniqueId() === existingTriggerId) {
foundExistingTrigger = true
}
})
if (foundExistingTrigger) {
return
}
}
var trigger = ScriptApp.newTrigger('onFormSubmitEvent')
.forSpreadsheet(SpreadsheetApp.getActive())
.onFormSubmit()
.create()
PropertiesService.getUserProperties().setProperty('onFormSubmitTriggerID', trigger.getUniqueId())
}
function getTimestampColumn(sheet) {
for (var i = 1; i <= sheet.getLastColumn(); i += 1) {
if (sheet.getRange(1, i).getValue() === 'Timestamp') {
return i
}
}
return 1
}
function getFormResponseEditUrlColumn(sheet) {
var form = FormApp.openByUrl(sheet.getFormUrl())
for (var i = 1; i <= sheet.getLastColumn(); i += 1) {
if (sheet.getRange(1, i).getValue() === 'Form Response Edit URL') {
return i
}
}
// get the last column at which the url can be placed.
return Math.max(sheet.getLastColumn() + 1, form.getItems().length + 2)
}
/**
* params: { sheet, form, formResponse, row }
*/
function addEditResponseURLToSheet(params) {
if (!params.col) {
params.col = getFormResponseEditUrlColumn(params.sheet)
}
var formResponseEditUrlRange = params.sheet.getRange(params.row, params.col)
formResponseEditUrlRange.setValue(params.formResponse.getEditResponseUrl())
}
function onOpen() {
var menu = [{ name: 'Add Form Edit Response URLs', functionName: 'setupFormEditResponseURLs' }]
SpreadsheetApp.getActive().addMenu('Forms', menu)
}
function setupFormEditResponseURLs() {
var sheet = SpreadsheetApp.getActiveSheet()
var spreadsheet = SpreadsheetApp.getActive()
var formURL = sheet.getFormUrl()
if (!formURL) {
SpreadsheetApp.getUi().alert('No Google Form associated with this sheet. Please connect it from your Form.')
return
}
var form = FormApp.openByUrl(formURL)
// setup the header if not existed
var headerFormEditResponse = sheet.getRange(1, getFormResponseEditUrlColumn(sheet))
var title = headerFormEditResponse.getValue()
if (!title) {
headerFormEditResponse.setValue('Form Response Edit URL')
}
var timestampColumn = getTimestampColumn(sheet)
var editResponseUrlColumn = getFormResponseEditUrlColumn(sheet)
var timestampRange = sheet.getRange(2, timestampColumn, sheet.getLastRow() - 1, 1)
var editResponseUrlRange = sheet.getRange(2, editResponseUrlColumn, sheet.getLastRow() - 1, 1)
if (editResponseUrlRange) {
var editResponseUrlValues = editResponseUrlRange.getValues()
var timestampValues = timestampRange.getValues()
for (var i = 0; i < editResponseUrlValues.length; i += 1) {
var editResponseUrlValue = editResponseUrlValues[i][0]
var timestampValue = timestampValues[i][0]
if (editResponseUrlValue === '') {
var timestamp = new Date(timestampValue)
if (timestamp) {
var formResponse = form.getResponses(timestamp)[0]
editResponseUrlValues[i][0] = formResponse.getEditResponseUrl()
var row = i + 2
if (row % 10 === 0) {
spreadsheet.toast('processing rows ' + row + ' to ' + (row + 10))
editResponseUrlRange.setValues(editResponseUrlValues)
SpreadsheetApp.flush()
}
}
}
}
editResponseUrlRange.setValues(editResponseUrlValues)
SpreadsheetApp.flush()
}
registerNewEditResponseURLTrigger()
SpreadsheetApp.getUi().alert('You are all set! Please check the Form Response Edit URL column in this sheet. Future responses will automatically sync the form response edit url.')
}
function onFormSubmitEvent(e) {
var sheet = e.range.getSheet()
var form = FormApp.openByUrl(sheet.getFormUrl())
var formResponse = form.getResponses().pop()
addEditResponseURLToSheet({
sheet: sheet,
form: form,
formResponse: formResponse,
row: e.range.getRow(),
})
}Ref :
https://xfanatical.com/blog/how-to-edit-google-forms-responses-in-the-spreadsheet/
Isian Drop Down dari Google Sheet
/**
* Auto-populate Question options in Google Forms
* from values in Google Spreadsheet
*
* Written by Amit Agarwal (MIT License)
*
**/
const populateGoogleForms = () => {
const GOOGLE_SHEET_NAME = "NAMA SHEET ANDA";
const GOOGLE_FORM_ID = "ID GOOGLE FORM";
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [header, ...data] = ss
.getSheetByName(GOOGLE_SHEET_NAME)
.getDataRange()
.getDisplayValues();
const choices = {};
header.forEach((title, i) => {
choices[title] = data.map((d) => d[i]).filter((e) => e);
});
FormApp.openById(GOOGLE_FORM_ID)
.getItems()
.map((item) => ({
item,
values: choices[item.getTitle()],
}))
.filter(({ values }) => values)
.forEach(({ item, values }) => {
switch (item.getType()) {
case FormApp.ItemType.CHECKBOX:
item.asCheckboxItem().setChoiceValues(values);
break;
case FormApp.ItemType.LIST:
item.asListItem().setChoiceValues(values);
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item.asMultipleChoiceItem().setChoiceValues(values);
break;
default:
// ignore item
}
});
ss.toast("Google Form Updated !!");
};
Referensi :
No comments:
Post a Comment