{"type":"doc","content":[{"type":"layoutSection","content":[{"type":"layoutColumn","attrs":{"width":66.66},"content":[{"type":"paragraph","content":[{"text":"DocuPhase Forms forms can be initialized with data from a Google Sheet by retrieving the data from the spreadsheet using a ","type":"text"},{"text":"business rule","type":"text","marks":[{"type":"link","attrs":{"__confluenceMetadata":{"spaceKey":"frevvo111","isRenamedTitle":true,"linkType":"page","contentTitle":"Business Rules","versionAtSave":"1"},"href":"https://frevvo-docs.atlassian.net/wiki/spaces/frevvo111/pages/1161959467"}}]},{"text":". Rule syntax and best practices to follow are discussed in the ","type":"text"},{"text":"Writing Rules to Retrieve and Update Data in a Google Sheet","type":"text","marks":[{"type":"link","attrs":{"__confluenceMetadata":{"spaceKey":"frevvo111","isRenamedTitle":true,"linkType":"page","contentTitle":"Writing Rules to Retrieve and Update Data in a Google Sheet","versionAtSave":"1"},"href":"https://frevvo-docs.atlassian.net/wiki/spaces/frevvo111/pages/1161970682"}}]},{"text":" topic.","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"The example discussed below uses a form that has a dropdown control to select a customer from a list. Once selected, a business rule queries a Google Sheet and retrieves the full address details for the selected customer. City, State, and Zip Code controls are also populated with the customer information.","type":"text"}]}]},{"type":"layoutColumn","attrs":{"width":33.33},"content":[{"type":"paragraph","content":[{"text":"On this page","type":"text","marks":[{"type":"strong"}]}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"minLevel":{"value":"1"},"maxLevel":{"value":"6"},"outline":{"value":"false"},"_parentId":{"value":"1507411767"},"style":{"value":"default"},"type":{"value":"list"},"printable":{"value":"true"}},"macroMetadata":{"macroId":{"value":"90221f93-20ca-4271-aa34-7bc8001fac43"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"df01a32b-b7cd-42cc-8d4e-9bf074b57127"}}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Example 1 - Populate form fields with data from a Google Sheet","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 1: Obtain an access token for your Google Account","type":"text"}]},{"type":"paragraph","content":[{"text":"If you do not have an access token for the Google Account where you are going to create your spreadsheet, perform ","type":"text"},{"text":"this one-time step.","type":"text","marks":[{"type":"link","attrs":{"__confluenceMetadata":{"isRenamedTitle":true,"linkType":"page","contentTitle":"Obtain an Access Token (Legacy)","versionAtSave":"2"},"href":"https://frevvo-docs.atlassian.net/wiki/spaces/docuphaseforms113/pages/1507411467"}}]}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Escape the slash characters in your OAuth token with a backslash in the rule below like this: ","type":"text"},{"text":"OAuth21\\/\\/","type":"text","marks":[{"type":"strong"}]},{"text":"<###...>","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 2 - Create your form","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a simple example contact form with a few fields. Note the Select Customer control is a dropdown. If you need help creating a form, check out the Quick Demo and Form Designer tutorial ","type":"text"},{"text":"videos on our website","type":"text","marks":[{"type":"link","attrs":{"href":"http://www.frevvo.com/videos"}}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"inline-media-image","parameters":{"width":"600","id":"11402a66-cbad-43a1-a835-947faaf88aba","collection":"contentId-1507411926","height":"250"}}},{"type":"hardBreak"},{"type":"hardBreak"},{"text":"Step 3: Create your Google Sheet","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a Google Sheet containing the customer address details. ","type":"text"},{"text":"The column name on a sheet must match the control name. The matching is case-insensitive and any spaces in the column name is ignored. When you reference the column name in the rule, use only lower case and no spaces.","type":"text","marks":[{"type":"textColor","attrs":{"color":"#1d1c1d"}}]}]},{"type":"table","attrs":{"layout":"default","width":905.0,"localId":"2c9d52c0-7c0d-409a-b390-ed3d526f4d3d"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Control Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Column Header","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Column Header in Rule","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"CustomerName","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"customer name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"customername","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"FullAddress","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"full address","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"fulladdress","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Street","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"street","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"street","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"City","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"city","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"city","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"ZipCode","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"zip code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"zipcode","type":"text"}]}]}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":832,"widthType":"pixel"},"content":[{"type":"media","attrs":{"__fileSize":94339,"__fileMimeType":"image/png","width":700,"id":"b4f1aa77-c24f-41dd-b4dc-496fbb4e2a4a","collection":"contentId-1507411412","type":"file","__fileName":"v613googleconnectoraddresssheet.png","height":229}}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Copy the Spreadsheet key to use in the Business Rule","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]}]},{"type":"paragraph","content":[{"text":"It is best practice to use the spreadsheet key in your business rule to specify the Google sheet. This method is recommended because each spreadsheet has its own unique key. Using the key instead of the spreadsheet/worksheet name avoids issues that may occur if you have more than one spreadsheet with the same name.","type":"text"}]},{"type":"paragraph","content":[{"text":"The Google connector supports the use of the key as a path parameter:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"key - the GUID of your Google sheet. Use the key to specify your Google sheet instead of the sheet name (ssname) in your rule. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To find the Google sheet key, ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Open your Google sheet.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The key is the long string of characters between the d/ and the /edit in the URL in the address bar.","type":"text"}]}]}]}]}]},{"type":"paragraph","marks":[{"type":"indentation","attrs":{"level":3}}],"content":[{"text":" ","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]},{"text":"Ex: ","type":"text"},{"text":"https://docs.google.com/spreadsheets","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}},{"type":"link","attrs":{"href":"https://docs.google.com/spreadsheets"}}]},{"text":"/d/1FXYb7PJpozlFcJol12YNEwhFFwa-tvpO6OjU1wzxDTI","type":"text","marks":[{"type":"textColor","attrs":{"color":"#ff0000"}}]},{"text":"/edit?usp=sharing ","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 4: Add the Business Rule","type":"text"}]},{"type":"paragraph","content":[{"text":"This business rule calls the Google Connector to retrieve the address details from a worksheet named ","type":"text"},{"text":"Sheet1","type":"text","marks":[{"type":"strong"}]},{"text":" in the Google Sheet associated with the Google Sheet key provided.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"javascript"},"content":[{"text":"/*member fulladdress, city,results, state, zipcode, password, user*/\nvar x;\n\nvar headers = \n{\"user\":\"\",\"password\":\"\"}; \nvar readquery = '/google/spreadsheets/query/key/?wsname=Sheet1&query=customername=\"' + Customer.value + '\"';\n eval('x=' + http.get(readquery,headers)); \n\nif (x.results == null) {\n FullAddress.value = 'error';\n} else if (x.results.length === 0) {\n FullAddress.value = 'No Match';\n} else {\n FullAddress.value = x.results[0].fulladdress;\n City.value = x.results[0].city;\n State.value = x.results[0].state;\n Zipcode.value = x.results[0].zipcode;\n} \n","type":"text"}]},{"type":"paragraph","content":[{"text":"Let's take a look at the components of this rule:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The Google user id and access token for the password are supplied via user and password headers. Note the line beginning with var headers =. If you want to try this example with your own Google sheet, change to your Google account user name and to the ","type":"text"},{"text":"access token","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/pages/resumedraft.action?draftId=1507411767#ReadingfromaGoogleSheet-ObtaininganAccessToken"}}]},{"text":" for your account. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"On Premise customers need to also specify the server and port where their Google Connector is hosted. The query url will start with '","type":"text"},{"text":"/","type":"text","marks":[{"type":"strong"}]},{"text":"google/spreadsheets/'.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The path parameter, /key, is used to provide the spreadsheet key to identify the spreadsheet. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The following URL query parameters are used:","type":"text"},{"type":"hardBreak"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"wsname - the rule queries the worksheet (tab) of the Google Connector Address sheet named ","type":"text"},{"text":"Sheet1","type":"text","marks":[{"type":"strong"}]},{"text":". To connect to your Google sheet, change ","type":"text"},{"text":"Sheet1","type":"text","marks":[{"type":"strong"}]},{"text":" to reflect the name of the worksheet (tab) in your Google Sheet.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"query= - This is where you specify which rows to match based on the data in your spreadsheet. The query parameter selects row(s) from the spreadsheet which match values in specific columns. In this example the spreadsheet has a column named 'customer name'. The query parameter retrieves the spreadsheet row where customer name equals the name we select in our form's dropdown control name 'Customer'. ","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The http.get returns all the values from the matched row(s) to a variable named \"x.results\". The rule checks for null which means that no row matched the selected customer. ","type":"text"}]}]}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"A few important notes:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The Google spreadsheet column name is 'customer name'. When you pass this to the query, remove spaces as you see above query=customername. Any references to Google Sheet columns in your rule ","type":"text"},{"text":"MUST be lower case and have all spaces removed.","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If you have spaces in your worksheet or Google Sheet name, do not remove them. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Notice the double quote characters in the query string added around Customer.value. This is important in cases where your data contains spaces, you need to surround them with double quotes.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You will always get back an array even for 1 result.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If there are no results, the returned JSON Object x.results will be null ","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Example 2 - Read from a Google Sheet using OR condition","type":"text"}]},{"type":"paragraph","content":[{"text":"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 a text area control named FullAddress when the form loads.","type":"text"}]},{"type":"paragraph","content":[{"text":"If you want to try this example with your own Google sheet, remember to change the and the to reflect your Google account information. Change to the key for your Google sheet and to the name of the tab in your Google sheet.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"javascript"},"content":[{"text":"/*member fulladdress, results, password, user*/\n \nif (form.load) {\n var x;\n var opts= [];\n \nvar headers = \n{\"user\":\"\",\"password\":\"\"}; \nvar readquery = '/google/spreadsheets/query/key/?wsname=&query=state=MA or state=CT';\n eval('x=' + http.get(readquery,headers));\n \nvar fi = ''; \nif (x.results == null) {\n fi = 'Error';\n} else if (x.results.length === 0) {\n fi = 'No Match';\n} else {\n for (var i=0; i < x.results.length; i++) {\n fi = fi + '\\n' + x.results[i].fulladdress;\n }\n}\nFullAddress.value = fi;\n}","type":"text"}]},{"type":"paragraph","content":[{"text":"If your Google Sheet has this data, then your form will look like the image to the right when the form loads and the rule runs.","type":"text"}]},{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"section","parameters":{"macroParams":{"_parentId":{"value":"1507411767"}},"macroMetadata":{"macroId":{"value":"34bb96fd-cf08-47bc-9317-e8ffc90640da"},"schemaVersion":{"value":"1"},"title":"Section"}},"localId":"8009ccfb-b9e4-4f5c-ac03-24e885c7f0a7"},"content":[{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

Google Address Spreadsheet

Form showing only customer addresses from CT or MA

","nestedContent":{"type":"doc","content":[{"type":"bodiedExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"column","parameters":{"macroParams":{"_parentId":{"value":"1507411767"}},"macroMetadata":{"macroId":{"value":"402c8589-2346-43ba-b27c-2a1b9d05cee1"},"schemaVersion":{"value":"1"},"title":"Column"}}},"content":[{"type":"mediaSingle","attrs":{"layout":"align-start","width":88.0},"content":[{"type":"media","attrs":{"__fileSize":32337,"__fileMimeType":"image/png","width":600,"id":"61bb807a-947d-46ca-b242-f67dd0ae4498","collection":"contentId-1507411767","type":"file","__fileName":"ReadwithORConditionform.PNG","height":203}}]},{"type":"paragraph","content":[{"text":"Form showing only customer addresses from CT or MA","type":"text","marks":[{"type":"strong"}]}]}]}],"version":1},"reason":"A column macro in a rich body macro can't be created or edited in the new editor."}}},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Example 3 - Generating Sequential Numbers from a Google Sheet","type":"text"}]},{"type":"panel","attrs":{"panelType":"success"},"content":[{"type":"paragraph","content":[{"text":"DocuPhase Forms now offers a ","type":"text"},{"text":"built-in Sequential Number feature","type":"text","marks":[{"type":"strong"}]},{"text":"! Please review ","type":"text"},{"text":"this documentation","type":"text","marks":[{"type":"link","attrs":{"__confluenceMetadata":{"isRenamedTitle":true,"linkType":"page","contentTitle":"Sequence Numbers","versionAtSave":"1"},"href":"https://frevvo-docs.atlassian.net/wiki/spaces/docuphaseforms113/pages/1507402408"}}]},{"text":" for how to add this easy-to-use, no integration required feature to your form.","type":"text"}]},{"type":"paragraph","content":[{"text":"The Google Sheets method below is available for customers employing this legacy paradigm or who have another use-case-specific need for this model.","type":"text","marks":[{"type":"em"}]}]}]},{"type":"paragraph","content":[{"text":"The Google connector can also be used to generate unique sequential numbers. This technique should not be used if you have multiple people using the form at the same time as it's possible that two people simultaneously opening the same form could get the same sequential number.","type":"text"}]},{"type":"paragraph","content":[{"text":"In this example we have a checkbook form where we want to initialize a field named CheckNum with the next sequential checkbook number when the form loads. Here is our Google spreadsheet:","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":70.0},"content":[{"type":"media","attrs":{"__fileSize":32885,"__fileMimeType":"image/png","width":808,"id":"7acc4a83-9b82-410b-97f9-e3fe6672cd1b","collection":"contentId-1507411767","type":"file","__fileName":"SequentialNumberSpreadsheet.PNG","height":265}}]},{"type":"paragraph","content":[{"text":"Here is the rule that reads the next sequential number from the spreadsheet and updates plus 1. Note this rule uses http headers to provide authentication information, access the Google sheet using the key, and passes the worksheet name as a query parameter. This is the recommended approach.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Replace and with your information in the user and password headers.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Replace with the key for your spreadsheet in the readquery and update statements.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Replace with the name of the worksheet (tab) in your Google sheet in the readquery and update statements.","type":"text"},{"type":"hardBreak"}]}]}]},{"type":"codeBlock","attrs":{"language":"javascript"},"content":[{"text":"/*member nextid, password, user, results */\nvar x;\nif (form.load) {\n var headers =\n {\"user\":\"\",\"password\":\"\"};\n var readquery =\n '/google/spreadsheets/query/key/?wsname=&query=formname=\"Checkbook\"';\n var update =\n '/google/spreadsheets/update/key/?wsname=&query=formname=\"Checkbook\"';\n eval('x=' + http.get(readquery, headers));\n var id = 'unknown';\n if (x.results === null) {\n CheckNum.value = 'error';\n } else if (x.results.length === 0) {\n CheckNum.value = 'No Match';\n } else {\n id = x.results[0].nextid;\n CheckNum.value = id;\n id = id * 1 + 1;\n }\n if (id !== 'unknown') {\n eval('x=' + http.put(update + '&updates=nextid=' + id, null, headers, false));\n }\n}","type":"text"}]},{"type":"paragraph","content":[{"text":"The rule reads the number in the nextid column of the spreadsheet and populates the Checknum field in the form when the form loads. It then adds 1 and updates the nextid column in the spreadsheet. The Checknum field for the next user to load the form is populated with the incremented value.","type":"text"}]},{"type":"paragraph","content":[{"text":"Remember that On Premise customers need to also specify the server and port where their Google Connector is hosted. The query url will start with '","type":"text"},{"text":"/","type":"text","marks":[{"type":"strong"}]},{"text":"google/spreadsheets/'.","type":"text"}]}],"version":1}

Browser not supported