...
If your form contains multiple documents because one or more controls are from the palette while other controls are from one or more XSD [[V4_Data_Sources_and_Schemas#Adding_Schema_Elements_to_a_Form.E2.80.99s_Data_Sources|data sources]], you can write each document to a different Google Spreadsheet. To do this use the Google Connector Url Manual Doc URI wizard to configure each Uri to a different spreadsheet or workbook within the spreadsheet.<div style="background-color:#63ffc7">[[Image:Lightbulb.png]]See the [[Google_Connector_SpreadSheet_Tutorial | Google spreadsheet tutorial]]
| Info |
|---|
See the Google Connector Spreadsheet Tutorial for step by step instructions. |
...
Read from a
...
Spreadsheet
forms can be initialized with data from a Google Spreadsheet by retrieving data from the Google Spreadsheet via a [[Rules_Examples#Triggers_.26_Dynamic_Options | business rule]]. This form allows the user to select a customer from the dropdown. Once selected a business rule retrieves the address details from a Google Spreadsheet.[[image:GoogleRetrieveForm.png]]
This is the Google spreadsheet containing the customer address details.
[[image:GoogleDisclosureAddrs.png]]
The form's business rule calls the Google Connector, passing a parameter named 'query'. The query paramenter selects row(s) from the spreadsheet which match values in specific columns. In this example the spreadsheet has a column named 'customer name'. We want to retrieve the spreadsheet row where customer name equals the name we select in our form's dropdown control name 'Customer'.<pre>
| Code Block |
|---|
eval('x=' + http.get('http://www.frevvo.com/google/spreadsheets/query/u/<google username>/p/<google password> |
...
/s/DisclosureAddresses/w/ByName?media=json&query=customername="' + |
...
Customer.value + '"')); |
...
if (x.results == null) |
...
{ RequestID.value = 'error'; |
...
} else if (x.results.length == |
...
0) { RequestID.value = 'No Match'; |
...
} else |
...
{ RequestID.value = x.results[0].nextid; |
...
} |
...
if (x.results == null) { |
...
FullAddress.value = 'error'; |
...
} else if (x.results.length == |
...
0) { FullAddress.value = 'No Match'; |
...
} else { |
...
FullAddress.value = x.results[0].address; |
...
City.value = x.results[0].city; |
...
State.value = x.results[0].state; |
...
Zipcode.value = x.results[0].zipcode; |
...
} |
</pre>The http.get returns all the values from the matched row(s) to a varable named "x.results". The rule checks for null which means that no row matched the selected customer.
The Google connector has the following restlet parameters:*
- /query - This tells the connector to execute the retrieve data method
...
- /u/<google username> - Your google account username.
...
- /p/<google password> - The password for this google account
...
- /s/<spreadsheet name> - The actual name of your spreadsheet.
...
- /w/<worksheet name> - The actual name of the worksheet in this spreadsheet
And the following Url parameters:* media
- media=jason - This must be set as shown
...
- query=<query string> - This is where you specify which rows to match based on the data in your spreadsheet.
...
| Note |
|---|
A few important notes: |
...
|
...
|
...
|
...
|
...
|
...
|
</div>The query string supports 'and' and 'or' logic. This query will retrieve all rows where the customer lives either in MA or CT. The rule concatenates all the addresses into the FullAddress form field.<pre>
| Code Block |
|---|
if (form.load) { |
...
eval('x=' + http.get('http://www.frevvo.com/google/spreadsheets/query/u/<user>/p/<password> |
...
/s/DisclosureAddresses/w/ByName?media=json&query=state="MA" or state="CT"')); |
...
var fi = ''; |
...
if (x.results == null) { |
...
fi = 'Error'; |
...
} else if (x.results.length == 0) { |
...
fi = 'No Match'; |
...
} else { |
...
for (var i=0; i < x.results.length; i++) |
...
{ fi = fi + '\n' + x.results[i].address; |
...
} } FullAddress.value = fi; |
...
} |
</pre>You can test your queries directly in the web browser. Note that characters such as the equals and double quotes must be [http://www.ryerson.ca/perl/CGI/URLEncoding.html URL Encoded] as URL encoded as shown in the examples below where you see %22 for quote and %3D for =
<pre>
| Code Block |
|---|
http://www.frevvo.com/google/spreadsheets/query/u/<user>/p/ |
...
<password> /s/DisclosureAddresses/w/ByName?media=json&query=name%3DHMF |
...
http://www.frevvo.com/google/spreadsheets/query/u/<user>/p/<password> |
...
/s/DisclosureAddresses/w/ByName?media=json |
...
&query=name%3D%22AAA Insurance%22 |
...
...
Update Spreadsheet
...
Note: This feature is available in v4.1.5 and later version of Live Forms.
Cells
In addition to retrieving rows from a Google spreadsheet and adding rows to a spreadsheet, you can also update an data in an existing row. See the business rule example [[Rules_Examples#Sequential_Numbers | business rule example Sequential Numbers]].[
[image:GoogleSequentialNumberGen.png]]
The business rule calls the Google Connector, passing a parameter named 'query'. The query paramenter selects row(s) from the spreadsheet which match values in specific columns. In this example the spreadsheet has a column named 'form name'. We want to retrieve the spreadsheet row where form name equals the name 'Checkbook'. The paramenter named 'update' indicates which column(s) to update in the matched row(s). In this example the spreadsheet has a column named 'nextid'. We want to set the value of nextid to the new value of 33.<pre>
| Code Block |
|---|
var updatemethod = 'http://www.frevvo.com/google/spreadsheets/ |
...
update/u/<google username>/p/<google password>/ |
...
s/SequentialNumberGenerator/w/Sheet1?media=json&query=formname="Checkbook"'; |
...
var id = 33; |
...
eval('x=' + http.get(updatemethod + '&updates=nextid=' + id + '&_method=put')); |
</pre>The Google connector has the following restlet parameters:*
- /update - This tells the connector to execute the update data method
...
- /u/<google username> - Your google account username
...
...
- /p/<google password> - The password for this google account
...
- /s/<spreadsheet name> - The actual name of your spreadsheet.
...
- /w/<worksheet name> - The actual name of the worksheet in this spreadsheet
And the following Url parameters:
* media=jason - This must be set as shown
* query=<query string> - This is where you specify which rows to match based on the data in your spreadsheet.
* update=<update string> - This is where you specify which cell(s) to update in the matched row(s)
* method=put - A hard coded Url parameter that must exist in the Url string
...