Please click 'App Script' in the 'Extensions' tab of Google Sheets.
Please add a new script.
Please delete all the default content and paste the following code:
function doPost(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
ss.setSpreadsheetTimeZone('Asia/Seoul');
var data = JSON.parse(e.postData.contents);
var formAnswers = data.form_answers;
var submittedAt = new Date(data.form_response.submitted_at);
var responseId = data.form_response.response_id.slice(0,10);
if (sheet.getLastRow() === 0) {
var fieldLabels = formAnswers.map(function(answer) {
if (answer.type === 'RADIO_GRID' || answer.type === 'CHECKBOX_GRID') {
return answer.response.map(el => `${answer.label} - ${el.lineLabel}`);
}
return answer.label
}).flat();
var headers = ['Response ID', 'Time Submitted', ...fieldLabels]
sheet.appendRow(headers);
}
var rowData = formAnswers.map(field => {
switch (field.type) {
case 'CHECKBOX':
case 'RADIO': {
var isResponseArray = Array.isArray(field.response);
if (isResponseArray) {
return field.response.filter(el => Boolean(el)).join(', ');
}
return field.response || '';
}
case 'CHECKBOX_GRID':
case 'RADIO_GRID': {
var isLineResponseArray = Array.isArray(field.response.lineResponse)
return field.response.map(el => {
if (isLineResponseArray) {
return el.lineResponse.filter(el => Boolean(el)).join(', ');
}
return el.lineResponse || '';
})
}
case 'GEOLOCATION': {
var response = field.response || {};
var responseArray = Object.entries(response).map(el => `${el[0]}: ${el[1]}`);
return responseArray.join(', ');
}
case 'HIDDEN': {
return field.response === undefined || field.response === null
? ''
: field.response;
}
default: {
var response =
field.response === undefined || field.response === null ? '' : field.response;
if (Array.isArray(response)) {
return response.join(', ');
}
return response;
}
}
}).flat();
sheet.appendRow([responseId, submittedAt, ...rowData]);
return ContentService.createTextOutput(JSON.stringify({result: "success"})).setMimeType(ContentService.MimeType.JSON);
}
Please click 'New Deployment'.
Set the type to 'Web App' and choose 'Anyone' for who has access.
Please approve the access.
Please copy the URL.
Please click the 'Webhook Management' button in the 'Integration' tab of Walla.
Please paste the URL and add the webhook.
After saving the changes, please activate it in the 'Webhook Settings'.
When a response arrives, the response details will be recorded in the Google Sheet.