|
Rule syntax and best practices to follow are discussed in the Writing Rules to Retrieve and Update Data in a Google Sheet topic.
If you do not have an access token for your Google Account, perform this one time step.
Use a rule to read information from the Google Sheet and populate the employee pick list. Here’s the relevant business rule:
/*member employeeid, firstname, lastname, password, user, results*/ var x; if (Connect.clicked) { var headers = {"user":"<google id>","password":"<access token>"}; var readquery = '/google/spreadsheets/query/key/<your spreadsheet key>?wsname=<the name of your worksheet>'; var results = http.get(readquery,headers); GSResults.value = results; eval('x=' + results); var opts = ['']; if (x.results) { for (var i = 0; i < x.results.length; i++) { if (x.results[i].employeeid) { opts.push(x.results[i].employeeid + '=' + x.results[i].firstname + ' ' + x.results[i].lastname); } } } EId.options = opts; } |
The column name on a Google sheet must 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." |
You can try it yourself by clicking this link or clicking on the image.
Here is another example of a simple rule that populates a Checkbox or Radio control named Colors with color options from a Google Sheet.
Create a Form with a Checkbox/Radio control named Colors.
Use this rule to populate the Checkbox/Radio options with the colors Red, Blue, Green and Orange. Note this rule uses http headers to provide authentication information. The Google Sheet is identified by the spreadsheet key and the worksheet name is passed as a query parameter. This is the recommended approach.
Replace <Google User ID> and <Google Account access token> with your information in the user and password headers.
Change <your spreadsheet key> to the key for your Google Sheet and <the name of the worksheet> to reflect the name of the worksheet tab in your Google Sheet.
/*member colors, password, user, results */ var x; if (form.load) { var headers = {"user":"<Google user Id>","password":"<Google Account access token>"}; var readquery = '/google/spreadsheets/query/key/<your spreadsheet key>?wsname=<the name of the worksheet>'; eval('x=' + http.get(readquery,headers)); var opts = []; if (x.results) { for (var i = 0; i < x.results.length; i++) { if (x.results[i].colors) { opts[i] = x.results[i].colors; } } } Colors.options = opts; //Colors is the name of the Checkbox/Radio control } |