0

I am submitting a Form in Google Sheet. After submitting I am getting a printed message like below

{"result":"success","data":"{\"74\":[\"\"],\"76\":[\"\"],\"77\":[\"\"],\"80\":[\"\"],\"81\":[\"\"],\"1_a\":[\"dsfasdfafas\"],\"1_b\":[\"\"],\"2_extra\":[\"\"],\"1_c\":[\"\"]}"} 

enter image description here

My code in Code.gs is like below

return ContentService    
      .createTextOutput(JSON.stringify({"result":"success","data": JSON.stringify(e.parameters) }))
      .setMimeType(ContentService.MimeType.JSON);

But I would like to redirect to previous URL (Form URL) after displaying a message. How can I do that ?

Here is my whole Code.js

 function formatMailBody(obj, order) {
  var result = "";
  if (!order) {
    order = Object.keys(obj);
  }
  
  // loop over all keys in the ordered form data
  for (var idx in order) {
    var key = order[idx];
    result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
    // for every key, concatenate an `<h4 />`/`<div />` pairing of the key name and its value, 
    // and append it to the `result` string created at the start.
  }
  return result; // once the looping is done, `result` will be one long string to put in the email body
}

// sanitize content from the user - trust no one 
// ref: https://developers.google.com/apps-script/reference/html/html-output#appendUntrusted(String)
function sanitizeInput(rawInput) {
   var placeholder = HtmlService.createHtmlOutput(" ");
   placeholder.appendUntrusted(rawInput);
  
   return placeholder.getContent();
 }

function doPost(e) {

  try {
    Logger.log(e); // the Google Script version of console.log see: Class Logger
    record_data(e);
    
    // shorter name for form data
    var mailData = e.parameters;

    // names and order of form elements (if set)
    var orderParameter = e.parameters.formDataNameOrder;
    var dataOrder;
    if (orderParameter) {
      dataOrder = JSON.parse(orderParameter);
    }
    
    // determine recepient of the email
    // if you have your email uncommented above, it uses that `TO_ADDRESS`
    // otherwise, it defaults to the email provided by the form's data attribute
    
    var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;
    
    var url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
    
    // send email if to address is set
    if (sendEmailTo) {
      MailApp.sendEmail({
        to: String(sendEmailTo),
        subject: "Contact form submitted",
        // replyTo: String(mailData.email), // This is optional and reliant on your form actually collecting a field named `email`
        //htmlBody: formatMailBody(mailData, dataOrder)
        htmlBody: url
      });
    }

    return ContentService    
          .createTextOutput(JSON.stringify({"result":"success","data": JSON.stringify(e.parameters) }))
          .setMimeType(ContentService.MimeType.JSON);
    
    
  } catch(error) { // if error return this
    Logger.log(error);
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": error}))
          .setMimeType(ContentService.MimeType.JSON);
  }
}


/**
 * record_data inserts the data received from the html form submission
 * e is the data received from the POST
 */
function record_data(e) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing
  
  try {
    Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
    
    // select the 'responses' sheet by default
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName = e.parameters.formGoogleSheetName || "responses";
    var sheet = doc.getSheetByName(sheetName);
    
    var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var newHeader = oldHeader.slice();
    var fieldsFromForm = getDataColumns(e.parameters);
    
    //var row = [new Date()]; // first element in the row should always be a timestamp
    var row = [];
    
    // loop through the header columns
    for (var i = 0; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
      var field = oldHeader[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      
      // mark as stored by removing from form fields
      var formIndex = fieldsFromForm.indexOf(field);
      if (formIndex > -1) {
        fieldsFromForm.splice(formIndex, 1);
      }
    }
    
    // set any new fields in our form
//    for (var i = 0; i < fieldsFromForm.length; i++) {
//      var field = fieldsFromForm[i];
//      var output = getFieldFromData(field, e.parameters);
//      row.push(output);
//      newHeader.push(field);
//    }
    
    // more efficient to set values as [][] array than individually
    var nextRow = sheet.getLastRow() + 1; // get next row
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // update header row with any new data
    if (newHeader.length > oldHeader.length) {
      sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
    }
  }
  catch(error) {
    Logger.log(error);
  }
  finally {
    lock.releaseLock();
    return;
  }
}

function getDataColumns(data) {
  return Object.keys(data).filter(function(column) {
    return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
  });
}

function getFieldFromData(field, data) {
  var values = data[field] || '';
  var output = values.join ? values.join(', ') : values;
  return output;
}

My HTML is like below

<form class="gform" method="POST"
                action="https://script.google.com/macros/s/AKfycbw7gGtJ37j_yLtHVBPmWuYyDB21STeVV3A/exec">
<input type="text" name="1_a" />
<button type="submit" class="activebtn showElement">
                Submit
            </button>
   </form>
Rubén
  • 34,714
  • 9
  • 70
  • 166
abu abu
  • 6,599
  • 19
  • 74
  • 131
  • 1
    How do you load the form initially? – Cooper Nov 15 '20 at 16:14
  • Thanks @Cooper for your reply. My Form is inside a Web Page. I am loading the Form browsing `http://127.0.0.1/shimulvai/project.html`. Thanks. – abu abu Nov 15 '20 at 16:38
  • 1
    I think you could just use `window.onload=function(){}` to detect something in the content that triggers a setInterval function that will redirect after a given delay. – Cooper Nov 15 '20 at 16:44
  • 1
    Related [Redirection after successful form submit](https://stackoverflow.com/q/42927819/1595451) – Rubén Nov 15 '20 at 18:45
  • 1
    Also you could include a button in the return so the user could reset the form – Cooper Nov 15 '20 at 19:36
  • 1
    Can you provide a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example), focusing on the specific issue you're having? – Iamblichus Nov 16 '20 at 09:22

0 Answers0