#StackBounty: #reactjs #google-apps-script #google-sheets #google-sheets-api Submit form(ReactJS) to Google Spreadsheet – change the me…

Bounty: 50

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 enter image description here

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());
}

enter image description here


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.