Versions Compared

Old Version 2

changes.mady.by.user Megan Ellis

Saved on

compared with

New Version Current

changes.mady.by.user Megan Ellis

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Section


Column

Let's take a look at how you can update existing values in a sheet. It’s another common scenario that can be used for a variety of tasks (e.g. limiting the number of submissions for a particular form or creating a sequentially increasing counter). We’ll use this sample Google Sheet to discuss. It has a row for each employee: Employee Id, Location and Extension.

Info

First, it’s important to note that Google Sheets is not a transactional system like a database and results can be unpredictable if multiple users update the same Sheet at the same time.



Column
width300px

 On This Page:

Table of Contents


...

Code Block
languagejs
/*member password, user*/

if (UpdateGoogleSheet.clicked) {
  var eid = EId.value; // Unique key in the Google Sheet row  
  var headers = {"user":"<google id>","password":"<your access token>"};
  var updatequery = '/google/spreadsheets/update/key/<your spreadsheet key>?wsname=Locations&query='+encodeURIComponent('employeeid="' + eid + '"');
  var updateparams = '&updates='+encodeURIComponent('location=' + Location.value + ',extension=' + Extension.value);
        
  eval('x=' + http.put(updatequery + updateparams, null, headers, false, false));
}
  1. It’s triggered by clicking on the Update Google Sheet button.
  2. We setup headers and an update query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet). In this example, the name of the Google Sheet tab is Locations. Change the wsname= parameter to the name of your Google Sheet tab if you named the tab something different.
  3. Add updateparams: we’re updating location and extension with new values.
  4. Run the update – perform an http.put() and eval the results.
Warningtip

The column name on a Google sheet column names must match Control Names exactly. Keep in mind that Control Names cannot contain spaces, special characters or start with a numbermust match the control name. The matching is case-insensitive and any spaces in the column name are ignored. A control named "FirstName" matches a column header "first name." However, references to Google Sheet columns in your rule must be lower case and cannot contain spaces. The correct reference for this example is "firstname."


Note

Reserved characters in a URL need to be encoded. The example above uses the encodeURIComponent function in JavaScript for every parameter value and invoke the http function with the extra encode parameter set to false.

Step 3: Try it yourself using the sample form

...