Linking with a Personal Google Sheet

✏️ In the built-in Google Sheets Integration feature in Walla, the owner of the sheet is Paprika Data Lab. Please be cautious when registering sensitive personal information. If you want to integrate the data with your company account, you can link the Google Sheet via Webhook.
  1. Please click 'App Script' in the 'Extensions' tab of Google Sheets.


  2. Please add a new script.


  3. 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');
      // Parsing the POST data to get the required data
      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);
      // First row: labels
      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);
    }


  4. Please click 'New Deployment'.


  5. Set the type to 'Web App' and choose 'Anyone' for who has access.


  6. Please approve the access.


  7. Please copy the URL.


  8. Please click the 'Webhook Management' button in the 'Integration' tab of Walla.


  9. Please paste the URL and add the webhook.


  10. After saving the changes, please activate it in the 'Webhook Settings'.


  11. When a response arrives, the response details will be recorded in the Google Sheet.