title: Retrieve and update values in Google spreadsheet by row ID by using HTML form
body: <p>I was able to create a google spreadsheet (with ideas found on here) with these columns:
  <strong>product | size | uid</strong>
  where uid is random five number generated ID (=RANDBETWEEN(10000;99999)).
  I then used the following apps script:</p>
  <pre><code>// original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
  // original gist: https://gist.github.com/willpatera/ee41ae374d3c9839c2d6 
  
  function doGet(e){
    return handleResponse(e);
  }
  
  //  Enter sheet name where data is to be written below
          var SHEET_NAME = &quot;Sheet1&quot;;
  
  var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
  
  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(&quot;key&quot;));
      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] == &quot;Timestamp&quot;){ // special case if you include a 'Timestamp' 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({&quot;result&quot;:&quot;success&quot;, &quot;row&quot;: nextRow}))
            .setMimeType(ContentService.MimeType.JSON);
    } catch(e){
      // if error return this
      return ContentService
            .createTextOutput(JSON.stringify({&quot;result&quot;:&quot;error&quot;, &quot;error&quot;: e}))
            .setMimeType(ContentService.MimeType.JSON);
    } finally { //release lock
      lock.releaseLock();
    }
  }
  
  function setup() {
      var doc = SpreadsheetApp.getActiveSpreadsheet();
      SCRIPT_PROP.setProperty(&quot;key&quot;, doc.getId());
  }
  </code></pre>
  <p>Then, I put together this php file:</p>
  <pre><code>&lt;?php
  if (isset($_GET['product'])) $product = $_GET['product'];
  if (isset($_GET['size'])) $size = $_GET['size'];
  if (isset($_GET['uid'])) $uid = $_GET['uid'];
  ?&gt;
  &lt;html&gt;
  &lt;head&gt;
  &lt;meta charset=&quot;utf-8&quot;&gt;
  &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width, initial-scale=1&quot;&gt;
  &lt;title&gt;Submit rows&lt;/title&gt;
  &lt;script src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js&quot;&gt;&lt;/script&gt;
  
  &lt;/head&gt;
  
  &lt;body&gt;
  
  Submit new row to google sheet.
  &lt;form id=&quot;test-form&quot;&gt;
    
    &lt;div&gt;
      &lt;label&gt;product&lt;/label&gt;
      &lt;input type=&quot;text&quot; name=&quot;product&quot; value=&quot;&lt;?php echo $product; ?&gt;&quot;/&gt;
    &lt;/div&gt;
  
    &lt;div&gt;
      &lt;label&gt;size&lt;/label&gt;
      &lt;input type=&quot;text&quot; name=&quot;size&quot; value=&quot;&lt;?php echo $size; ?&gt;&quot;/&gt;
    &lt;/div&gt;
    
    &lt;div&gt;
      &lt;label&gt;UID&lt;/label&gt;
      &lt;input type=&quot;text&quot; name=&quot;uid&quot; value=&quot;&lt;?php echo $uid; ?&gt;&quot;/&gt;
    &lt;/div&gt;
  
    &lt;div&gt;
      &lt;button type=&quot;submit&quot; id=&quot;submit-form&quot;&gt;Submit form&lt;/button&gt;
    &lt;/div&gt;
    &lt;div class=&quot;thanks&quot;&gt;&lt;/div&gt;
  
  &lt;/form&gt;
  
  
    &lt;script&gt;
          $(document).ready(function(){
            $.fn.serializeObject = function()
                {
                 var o = {};
                 var a = this.serializeArray();
                 $.each(a, function() {
                     if (o[this.name]) {
                         if (!o[this.name].push) {
                             o[this.name] = [o[this.name]];
                         }
                         o[this.name].push(this.value || '');
                     } else {
                         o[this.name] = this.value || '';
                     }
                 });
                 return o;
                };
              var form = $('form#test-form'),
                url = 'https://script.google.com/macros/s/KEY/exec';
                form.submit(function(e){
                  e.preventDefault();
                var jqxhr = $.ajax({
                  url: url,
                  method: &quot;GET&quot;,
                  dataType: &quot;json&quot;,
                  data: form.serializeObject()
                });
                  $(&quot;.thanks&quot;).html(&quot;Sent to sheet.&quot;).css(&quot;font-size&quot;,&quot;2rem&quot;);
                  $(&quot;.form-control&quot;).remove();
                  $(&quot;#submit&quot;).remove();
                });
              });
          &lt;/script&gt;
  
  
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  <p>Why like this? I have a QR code and when it is scanned, user is directed to url like this:
  htttps://samplesite.com/index.php?product=Pants&amp;size=large&amp;uid=12345</p>
  <p>So form fields are prepopulated by values from the qr code/link. Form is submitted, values are written into the google spreadsheet, all is fine.</p>
  <p>But now to the question part! :)
  There is another qr code on the product and when scanned, it opens url like this: htttps://samplesite.com/edit.php?uid=12345</p>
  <p>I would like this to open another page with form that lists the row values of the same google spreadsheet where uid is 12345. With php, it could be something like <em>select * from sheet_id where uid='12345'</em> and then defining the column values, but obviously it is not that easy.</p>
  <p>I did some searching and tabletop.js has been suggested, but they (<a href="https://github.com/jsoma/tabletop" rel="nofollow noreferrer">https://github.com/jsoma/tabletop</a>) don't recommend it anymore.</p>
  <ol>
  <li>How can I retrieve information from a google spreadsheet by unique row id and insert it as values in html form?
  I plan to fill in the form with this data and then use drop downs to allow user to change the (size) value. Which leads us to the second part...</li>
  <li>How can I update the google spreadsheet by specific row id? I want this form to overwrite the values in same row with id 12345.</li>
  <li>And since we're here already: is excel's randbetween function <em>good enough</em> for this use? Or would you recommend a bigger number/letters? The outdated backend system unfortunately does not allow us to progress uid numbers by one, like in a regular database.</li>
  </ol>
  <p>Thank you.</p>