title: How do I update specific rows in Google Spreadsheets using Reactjs?
body: <p>What needs to happen is that from my React web app there's this form and data inputted should be sent to Google Spreadsheet.</p>
  
  <ol>
  <li>Is there away to avoid using Timestamp when inserting new data in the spreadsheet?</li>
  <li>How do I update a SPECIFIC row? 
  For example, i have Joe and I need to update his number to 456</li>
  </ol>
  
  <pre><code>| NAME  |  NUMBER | 
  |-------|---------|
  |Joe    |123      |
  |Brian  |420      |
  |Raine  |143      |
  </code></pre>
  
  <p>How do I do this coming from the web app? </p>
  
  <p>I've tried this but still no luck</p>
  
  <p><a href="https://mashe.hawksey.info/2018/02/google-apps-script-patterns-conditionally-updating-rows-of-google-sheet-data-by-reading-and-writing-data-once/" rel="noreferrer">https://mashe.hawksey.info/2018/02/google-apps-script-patterns-conditionally-updating-rows-of-google-sheet-data-by-reading-and-writing-data-once/</a></p>
  
  <p>This is from the web app with some code in the <code>componentDidMount()</code></p>
  
  <p>This code is used to the form data and to prevent the page from redirecting to the App script link. Basically it stays on the page and even reloads the form.</p>
  
  <pre><code>    componentDidMount() {
  
          console.log("Contact form submission handler loaded successfully.");
          // bind to the submit event of our form
          var forms = document.querySelectorAll("form.gform");
          for (var i = 0; i &lt; forms.length; i++) {
              forms[i].addEventListener("submit", handleFormSubmit, false);
          }
  
  
          //document.addEventListener("DOMContentLoaded", loaded, false);
  
          function validateHuman(honeypot) {
              if (honeypot) {  //if hidden form filled up
                  console.log("Robot Detected!");
                  return true;
              } else {
                  console.log("Welcome Human!");
              }
          }
  
          // get all data in form and return object
          function getFormData(form) {
              var elements = form.elements;
              var honeypot;
  
              var fields = Object.keys(elements).filter(function (k) {
                  if (elements[k].name === "honeypot") {
                      honeypot = elements[k].value;
                      return false;
                  }
                  return true;
              }).map(function (k) {
                  if (elements[k].name !== undefined) {
                      return elements[k].name;
                      // special case for Edge's html collection
                  } else if (elements[k].length &gt; 0) {
                      return elements[k].item(0).name;
                  }
              }).filter(function (item, pos, self) {
                  return self.indexOf(item) === pos &amp;&amp; item;
              });
  
              var formData = {};
              fields.forEach(function (name) {
                  var element = elements[name];
  
                  // singular form elements just have one value
                  formData[name] = element.value;
  
                  // when our element has multiple items, get their values
                  if (element.length) {
                      var data = [];
                      for (var i = 0; i &lt; element.length; i++) {
                          var item = element.item(i);
                          if (item.checked || item.selected) {
                              data.push(item.value);
                          }
                      }
                      formData[name] = data.join(', ');
                  }
              });
  
              // add form-specific values into the data
              formData.formDataNameOrder = JSON.stringify(fields);
              formData.formGoogleSheetName = form.dataset.sheet || "responses"; // default sheet name
              //formData.formGoogleSend = form.dataset.email || ""; // no email by default
  
              console.log(formData);
              return { data: formData, honeypot };
          }
  
          function handleFormSubmit(event) {  // handles form submit without any jquery
              event.preventDefault();           // we are submitting via xhr below
              var form = event.target;
              var formData = getFormData(form);
              var data = formData.data;
  
              // If a honeypot field is filled, assume it was done so by a spam bot.
              if (formData.honeypot) {
                  return false;
              }
  
              disableAllButtons(form);
              var url = form.action;
              var xhr = new XMLHttpRequest();
              xhr.open('POST', url);
              // xhr.withCredentials = true;
              xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
              xhr.onreadystatechange = function () {
                  console.log(xhr.status, xhr.statusText);
                  console.log(xhr.responseText);
                  form.reset();
  
                  return;
              };
              // url encode form data for sending as post data
              var encoded = Object.keys(data).map(function (k) {
                  return encodeURIComponent(k) + "=" + encodeURIComponent(data[k]);
              }).join('&amp;');
              xhr.send(encoded);
          }
  
          function disableAllButtons(form) {
              var buttons = form.querySelectorAll("button");
              for (var i = 0; i &gt; buttons.length; i++) {
                  buttons[i].disabled = true;
              }
          }
  
  
      }
  </code></pre>
  
  <p>This is the snippet form in the web app</p>
  
  <pre><code>
  &lt;form className="gform" method="POST"
                      action="GOOGLE APP SCRIPT"&gt;
                      &lt;div className="row Late-body-container"&gt;
                          &lt;div className="col-6"&gt;
                              &lt;select name="Message" className="form-control reason-text" id="selectLateReason" onClick={this.checkOption} onChange={this.loaded}&gt;
                                  &lt;option value="--"&gt;--&lt;/option&gt;
                                  &lt;option value="Sick Leave"&gt;Sick Leave&lt;/option&gt;
                                  &lt;option value="Emergency Leave"&gt;Emergency Leave&lt;/option&gt;
                                  &lt;option value="Family Affairs"&gt;Family Affairs&lt;/option&gt;
                                  &lt;option value="To travel"&gt;To travel&lt;/option&gt;
                                  &lt;option value="Other reasons"&gt;Other reasons&lt;/option&gt;
                              &lt;/select&gt;
                          &lt;/div&gt;
                          &lt;button type="submit" className="btn btn-send" id="btnSubmit" value="Submit" &gt;Send&lt;/button&gt;
                      &lt;/div&gt;
                  &lt;/form&gt;
  </code></pre>
  
  <p>and then finally this is the Google app script code </p>
  
  <pre><code>
  /******************************************************************************
   * This tutorial is based on the work of Martin Hawksey twitter.com/mhawksey  *
   * But has been simplified and cleaned up to make it more beginner friendly   *
   * All credit still goes to Martin and any issues/complaints/questions to me. *
   ******************************************************************************/
  
  // if you want to store your email server-side (hidden), uncomment the next line
  // var TO_ADDRESS = "example@email.net";
  
  // spit out all the keys/values from the form in HTML for email
  // uses an array of keys if provided or the object to determine field order
  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 += "&lt;h4 style='text-transform: capitalize; margin-bottom: 0'&gt;" + key + "&lt;/h4&gt;&lt;div&gt;" + sanitizeInput(obj[key]) + "&lt;/div&gt;";
      // for every key, concatenate an `&lt;h4 /&gt;`/`&lt;div /&gt;` 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;
  
      // 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)
        });
      }
  
      return ContentService    // return json success results
            .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
  
      // loop through the header columns
      for (var i = 1; i &lt; 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 &gt; -1) {
          fieldsFromForm.splice(formIndex, 1);
        }
      }
  
      // set any new fields in our form
      for (var i = 0; i &lt; 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 &gt; 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;
  }
  
  
  </code></pre>
  
  <p>As mentioned earlier 
  I have Joe and I need to update his number to 456</p>
  
  <pre><code>| NAME  |  NUMBER | 
  |-------|---------|
  |Joe    |123      |
  |Brian  |420      |
  |Raine  |143      |
  </code></pre>
  
  <p>so the Expected result should be that </p>
  
  <pre><code>| NAME  |  NUMBER | 
  |-------|---------|
  |Joe    |456      |
  |Brian  |420      |
  |Raine  |143      |
  </code></pre>
  
  <p>Additional note:
  The code works but when I insert new data 
  I can't get rid of the Timestamp. How do I get rid of it?
  <a href="https://i.stack.imgur.com/zJMiT.png" rel="noreferrer"><img src="https://i.stack.imgur.com/zJMiT.png" alt="enter image description here"></a></p>