¶ Lookup via Google Sheets
Google Sheets can be an excellent database for storing the information that will be used in the integration with a product on the Designer. Google Sheets are cloud hosted and access can be granted to multiple users. This makes adding new records and maintaining the database simple.
The steps required for a successful integration of a Designer product and a Google Sheet are:
- Place database in Google Sheet
- Determine unique Google Sheet ID of Google Sheet
- Modify PDFlib blocks for those blocks that will use data extracted from Google Sheet
- Register link between Designer productID and Google Sheet
¶ Configure data in Google Sheet
Below is an example database stored in a Google Sheet:
Here are the requirements for the content of the Google Sheet:
- The Google Sheet must contain a sheet with label "Data"
- The Google Sheet must contains a sheet with label "Photos"
- The first column of the sheet "Data" must contain a unique value in each row
- The Google Sheet must contain a column with label "Merge"
¶ Column 1 of sheet "Data"
The Designer will always use the data in the first column of the sheet "Data" as the "Key field" for the database lookup. The value in each row must be unique. The label of first column of sheet "Data" is not important.
¶ Column 1 of sheet "Photos"
Column 1 of sheet "Photos" should contain the same values as are used in column 1 of sheet "Data". Unlike column 1 in sheet "Data", there is no requirement that the values in column 1 be unique. This means that each row in sheet "Data" can have more than one matching rows in sheet "Photos".
¶ Column in sheet "Data" with label "Merge"
The column with label "Merge" is used only when using the bulk "Merge" function. Those rows with value = "TRUE" will be included in the bulk merge. All other rows will be excluded from the bulk merge.
¶ Google Sheet ID
The URL of the Google Sheet will have a form similar to this:
https://docs.google.com/spreadsheets/d/1BjLgyzi5GqIUA_dQm1bFzKSG6RvE3AYjPsHjbO10/
The Google Sheet ID is the long string of alpha-numeric characters following the "/spreadsheets/d/"
In the example above, the Google Sheet id is:
1BjLgyzi5GqIUA_dQm1bFzKSG6RvE3AYjPsHjbO10
You must identify the Google Sheet ID for the database you create. You will need the Google Sheet ID when you register the link between the Designer productID and the Google Sheet .
¶ Modify PDF blocks in template file with Block prefix
When using a database stored in a Google Sheet, you have the option to have each block in the template file be pre-filled with data from the Google Sheet or from the default data configured in the PDFlib block.
For blocks where you want to have the block pre-filled with the default data configured in the PDFlib block, simply leave the BLock Name for the block in the standard format which is:
Block_X
For blocks where you want to have the block pre-filled with data from the Google Sheet, you will need to modify the Block Name so that it contains a concatenation of 2 words separated by a period:
- Block prefix which is an alpha-numeric string you are free to choose yourself
- Column name of column containing data in Google Sheet
Let us take the case where we want to configure a block to use data extracted from the column "Birthplace" in the Google Sheet shown below.
In this example, we have chosen the Block Prefix to be "president". We must now modify the Block Name of the PDFlib block to be the text "president" followed by a period and the name of the column in the Google Sheet: "Birthplace". This yields a Block Name of:
president.Birthplace
The example Block Name is shown below:
¶ Register link between Designer productID and Google Sheet
You should register the link between the Designer productID and the Google Sheet. In the left menu, select "Data files", then the "Gsheet Lookup" tab. Click the link at top right to "Add Gsheet Lookup":
When you register the link between the Designer productID and the Google Sheet, you will enter 3 pieces of data:
- ProductID on the Designer server
- Google Sheet ID
- Block prefix used inside Block Name of certain PDFlib blocks
Once the link between the Designer productID and the Google Sheet is registered, then, whenever a Designer session for the productID is launched, the Designer will use data extracted from the Google Sheet to pre-fill certain PDFlib blocks.
¶ Adjust Styling of Record Selection page
When your customer first launches the Designer session for the product, your customer will see a page where the customer will select the record from the Google Sheet that will be used to supply the default data for the blocks.
For each connection to a Google Sheet, you can configure text labels that appear in the FORM. You can also add HTML that will be displayed to the top, bottom, left and right of the record selection FORM. Finally, you can specify CSS that will be applied to the various sections of the page as shown below:
An example of the record selection page with styling applied is shown below:
¶ Link Designer account to Google account
In order to allow the Designer server to access and extract data from the Google Docs account, you must link the 2 accounts. The link is achieved by clicking the button "Sign in with Google" at the bottom of the Account page in the Designer Server interface as shown below. After clicking this link, Google will ask you to confirm that you are granting access to your Google Docs account:
