Submit form(ReactJS) to Google Spreadsheet - change the message validation

Question

I want to register data in a Google Sheet from a ReactJS form (2 fields if the user has possible suggestion or comments).

This is my feedback form in React :

import React,{useState,useEffect} from 'react';
import './App.css';

const formUrl = 'https://script.google.com/macros/s/AK.../exec'
export default function FrmTable(){  
    const [loading,setLoading] = useState(false)
    return(
        <div className="section-form">
            <form name="frm"  
                  method="post"
                  action={formUrl}
            >   
                <div className="form-elements">
                    <div className="pure-group">
                        <label className="pure-group-label">Suggestion content pdf</label>
                        <input id="Suggestion content pdf" name="Suggestion content pdf" className="pure-group-text"
                               type="text" 
                        />
                    </div>


                    <div className="pure-group">
                        <label className="pure-group-label" >Comments</label>
                        <textarea id="Comments" name="Comments" rows="10" className="pure-group-text" 
                          placeholder="" 
                          maxLength="1000"
                        ></textarea>
                    </div>
                </div>

                <p className="loading-txt">{loading == true ? 'Loading.....' : ''}</p>
                <div className="pure-group pure-group-btn">
                    <button className="button-success pure-button button-xlarge btn-style" >Send</button>
                </div>
            </form>
            </div>
    )
}

The GSheet script in order to register the suggestion content and comments :

        var SHEET_NAME = "Feedback";
//  2. Run > setup
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}
function doPost(e){
  return handleResponse(e);
}
function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp(Date)' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Everything works fine I can register the 2 fields(suggestion and comment) in the GSheet but I would like to have another view after submiting

I've followed some tutorials because I'm new into React. At the end after submiting you are sent to script.googleusercontent.... because in the GSheet script we have this code

return ContentService
              .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
              .setMimeType(ContentService.MimeType.JSON);

I want just to show a simple message like a popup in order to say the submit form is ok. Any idea is welcomed :) thank you very much.

New Edit : I've changed my code (React + Google Script) but I have an CORB blocked cross-origin.

import React,{useState,useEffect} from 'react';
import './App.css';

const formUrl = 'https://script.google.com/macros/s/AKfycbz4hMELOHff2Yd_ozpOid2cAWFSWPm_7AOD15OIeQRdYrocv0wa/exec'
export default function FrmTable(){  
   
   
    const jsonp = (url, callback) =>  {
        var callbackName = 'jsonp_callback_' + Math.round(100000 * Math.random());
        window[callbackName] = function(data) {
            alert("Formulaire envoyé ");
            delete window[callbackName];
            document.body.removeChild(script);
            callback(data);
        };
    
        var script = document.createElement('script');
        script.src = url + (url.indexOf('?') >= 0 ? '&' : '?') + 'callback=' + callbackName;
        document.body.appendChild(script);
    }

    const mySubmitHandler = (event) => {
        event.preventDefault();
        
        jsonp(formUrl + '?La+FAQ+en+question=' + encodeURIComponent(faqName), (data) => {
          // alert(data);
        });
        event.target.reset();
        
      }
    // const { register, errors, required ,handleSubmit } = useForm();
    const [loading,setLoading] = useState(false)
    const [faqName,setFaqName] = useState('')
  
    const myChangeHandler1 = (event) => {
        setFaqName(event.target.value);
      }
     
    return(
        <div className="section-form" >
            <form name="frm"  
                  method="post"
                  onSubmit={mySubmitHandler}
            >   
                <div className="form-elements">

                    <div className="pure-group ">
                        <label className="pure-group-label">La FAQ en question </label>
                        <input name="FAQ en question" className="pure-group-text"
                               type="text" onChange={myChangeHandler1}
                        />
                    </div>
                </div>

                <input type='submit' />
            </form>
            </div>
    )
}

The Google Script :

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}
function doPost(e){
  //return handleResponse(e);
}
function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp(Date)' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
     var callback = e.parameter.callback;
    // return json success results
   // return ContentService
   //       .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
   //       .setMimeType(ContentService.MimeType.JSON);
     return ContentService.createTextOutput(callback+'('+ JSON.stringify({"result":"success", "row": nextRow})+')').setMimeType(ContentService.MimeType.JAVASCRIPT);
  } catch(error){
    // if error return this
    //return ContentService
      //    .createTextOutput(JSON.stringify({"result":"error", "error": e}))
//          .setMimeType(ContentService.MimeType.JSON);
    var callback = e.parameter.callback;
     return ContentService.createTextOutput(callback+'('+ JSON.stringify({"result":"error", "error": error})+')').setMimeType(ContentService.MimeType.JAVASCRIPT);
  } finally { //release lock
    lock.releaseLock();
  }
}
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Answer 1

I would like to propose the following 2 patterns.

Modification points:

• In the current stage, there is no getPublicLock().
• In your shared Spreadsheet, there is only one sheet of Sheet2. But at var SHEET_NAME = "Feedback";, no sheet name is used. By this, var sheet = doc.getSheetByName(SHEET_NAME); is null and an error occurs at var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].
• At formUrl + "&Commentaires=" + encodeURIComponent(faqComment) in react side, in this case, the endpoint becomes https://script.google.com/macros/s/###/exec&Commentaires=test?callback=jsonp_callback_###.

Pattern 1:

In this pattern, your script is modified and JSONP is used.

React side: App.js

From:

jsonp(formUrl + "&Commentaires=" + encodeURIComponent(faqComment), data => {
  // alert(data);
});

To:

jsonp(formUrl + "?Commentaires=" + encodeURIComponent(faqComment), data => {
  // alert(data);
});

Google Apps Script side:

When you want to use your shared Spreadsheet, please modify as follows.

From:

var lock = LockService.getPublicLock();

To:

var lock = LockService.getDocumentLock();

And,

From:

var SHEET_NAME = "Feedback";

To:

var SHEET_NAME = "Sheet2";

In this case, you can also modify the sheet name from Sheet2 to Feedback instead of modifying the script.

Pattern 2:

In this pattern, your script is modified and fetch is used instead of JSONP. Because when above script is used, I could confirm that there is sometimes the error related to CORS. So as another pattern, I would like to propose to use fetch. When fetch is used, I could confirm that no error related to CORS occurs.

React side: App.js

From:

export default function FrmTable() {
  const jsonp = (url, callback) => {
    var callbackName = "jsonp_callback_" + Math.round(100000 * Math.random());
    window[callbackName] = function(data) {
      alert("Formulaire envoyé ");
      delete window[callbackName];
      document.body.removeChild(script);
      callback(data);
    };

    var script = document.createElement("script");
    script.src =
      url + (url.indexOf("?") >= 0 ? "&" : "?") + "callback=" + callbackName;
    document.body.appendChild(script);
  };

  const mySubmitHandler = event => {
    event.preventDefault();
    /*    const request = new XMLHttpRequest();
        const formData = new FormData();
        formData.append("La FAQ en question", form.faqName);
        formData.append("Suggestion contenu pdf", form.faqSuggest);
        formData.append("Commentaires", form.faqComment);
        request.open("POST", formUrl);
        request.send(formData); */

    jsonp(formUrl + "&Commentaires=" + encodeURIComponent(faqComment), data => {
      // alert(data);
    });
    event.target.reset();
  };

To:

export default function FrmTable() {
  const mySubmitHandler = event => {
    event.preventDefault();
    fetch(formUrl + "?Commentaires=" + encodeURIComponent(faqComment))
      .then(res => res.text())
      .then(res => console.log(res))
      .catch(err => console.error(err));

    event.target.reset();
  };

• In this case, res.json() can be also used instead of res.text().

Google Apps Script side:

When you want to use your shared Spreadsheet, please modify as follows.

From:

var lock = LockService.getPublicLock();

To:

var lock = LockService.getDocumentLock();

And,

From:

   var callback = e.parameter.callback;
   return ContentService.createTextOutput(callback+'('+ JSON.stringify({"result":"success", "row": nextRow})+')').setMimeType(ContentService.MimeType.JAVASCRIPT);
} catch(error){
  var callback = e.parameter.callback;
   return ContentService.createTextOutput(callback+'('+ JSON.stringify({"result":"error", "error": error})+')').setMimeType(ContentService.MimeType.JAVASCRIPT);
} finally { //release lock
  lock.releaseLock();
}

To:

  return ContentService.createTextOutput(JSON.stringify({"result":"success", "row": nextRow})).setMimeType(ContentService.MimeType.JSON);
} catch(error){
  return ContentService.createTextOutput(JSON.stringify({"result":"error", "error": error})).setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
  lock.releaseLock();
}

And,

From:

var SHEET_NAME = "Feedback";

To:

var SHEET_NAME = "Sheet2";

In this case, you can also modify the sheet name from Sheet2 to Feedback instead of modifying the script.

Note:

• When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• Class LockService