Using a Google Drive Spreadsheet to provide dynamic content
In this example I’m going to create a Google Drive spreadsheet with a simple structure of four cells. The content of the cells, Availability and Price, will be loaded, dynamically into text fields in our Oomph Document. You can also watch the Youtube video tutorial here.
In order to follow along you will need the following:
- Oomph Viewer (minimum version 22.0.1)
- A Gmail Account (personal)
I’ll start by creating an Oomph project with a section containing a single pdf file, P1-1.pdf. The page will contain four text fields, two for the titles, Availability and Price, the other two for the data associated with them. After that I’ll create a Google Drive spreadsheet with cells that contain the text for the four fields.
A .txt.url file will be generated that references each of the text fields and a shared CSS styling will be applied to the text fields. Download our Cloud Sync Demo file that contains a working sample of the final product.
- Create a new Oomph bundle with the following assets in 10-Cover
- Availability [Folder]
- Price [Folder]
- Open InDesign and create a portrait document 768 x 1024px.
- Create four frames using the following settings;
- Frame1 x = 105, y = 280, w = 270, h = 48
- Frame2 x = 390 , y = 280, w = 270, h = 48
- Frame2 x = 105 , y = 346, w = 270, h = 48
- Frame2 x = 390 , y = 346, w = 270, h = 48
- Create hyperlinks for each frame as follows;
- Frame1 = Availability
- Frame2 = AvailabilityData
- Frame3 = Price
- Frame4 = PriceData
- Save it using Adobe PDF Presets as P1-1.pdf into your 10-Cover folder
Google Drive Spreadsheet
I’ll login to Google and create a new spreadsheet in Drive
Note: you cannot build an RSS feed on a company account it must be done via a personal Account
- Login to Google
- Select ‘Drive’ from the main menu
- Select ‘Create/Spreadsheet’ and give it the name CSDemo
The spreadsheet will be very simple and only 4 cells will contain data. The first step is to format the cells then add the data.
- Select the first nine cells and from the menu select Format/Numbers/PlainText. This stops the spreadsheet from automatically formatting the price data.
- Leave the first row and first column blank.
- In Column B Cell 2 enter Availability:
- In Column B Cell 3 enter Price:
- In Column C Cell 2 enter Available
- In Column C Cell 3 enter $200,000
The next step is to format the document as RSS so we can capture specific cell data into our text fields.
- Select Menu/File/Publish to the Web...
- Select ‘Start Publishing’
- Copy the generated link. It should be similar to the one below;
- Copy the “key” from the link that is generated. The “key” is the string of seemingly random numbers and characters between “https://docs.google.com/spreadsheets/d/“ and “/pubhtml”. In this case the key is:
Update the following URL with your “key”: https://spreadsheets.google.com/feeds/list/KEY/od6/public/values?alt=rss. So, for our previous example the final URL will be:
You can now close or logout of Google Drive.
Oomph Feed Connector
In order to load the data from the spreadsheet we need to create .txt.url files that point to the individual cells within the RSS document. Oomph have created an online form based page into which you can paste your RSS feed link and extract the .txt.url links.
- Navigate to http://services.oomphhq.com/oomphurlmaker
- Paste the final URL into the URL field and select ‘Grab Feed’
- The first column will contain rss (1) with a black arrow next to it, click on it to open the second column
- The second column will contain channel(10), click on it to open the third column
- The third column contains a number of elements including 2 item (7) elements with black arrows these represent the cells we have defined in the Google document.
- Select the first 'item' and a new column opens with 7 elements. Select the second last item.
- In the right hand text field you can see the content of this item/description is “Availability” and the path to the cell data is /rss/channel/item/gsx:_cokwr
- Select the link ‘Download .url file’
The next step is to move the downloaded feed.url file into our project folder and copy and update it for each cell.
- Locate the downloaded feed.url file and copy it to the Project folder CSDemo/10-Cover/Availability
- Rename it S1-1.txt.url
- Open the file with TextEdit or another text editing app
- At the end of the link add a carriage return and enter the following on two lines, TTL=1 and LiveUpdate=YES
The link should look like this:
Do the same for the remaining cells, making sure to save the correct .url file to its respective folder. i.e. In Oomph Feed Connector, find the 'item' with the content "Available". Save that .url file into the AvailabilityData folder. Find the 'item' with the content "Price" and save that .url file into the Price folder. Find the 'item' with the content "$200,000" and save that .url file into the PriceData folder. Add TTL=1 and LiveUpdate=YES to each one.
Apply a CSS file to the text fields
In order to have the text fields display the data in a suitable manner we will apply a CSS (Cascading Style Sheet) to the page.
- Create a new simple text document and add the following text:
font-family: "Gill Sans", sans-serif;
font-family: "Gill Sans", sans-serif;
This CSS will align the text in the left hand frames to the right and align the text in the right hand frames to the left and bold it.
- Create a new folder structure in the CloudSyncDemo folder; Shared/Layers/Default
- Copy the file into the Default folder and name it style-1.css
Testing Your App
Now it’s time to test the app and check to see we can see the updating content
- Attach your iPad to your computer if it isn’t already attached
- Open up iTunes and browse to Devices/‘YouriPad’/Apps/File Sharing
- Zip up the CloudSyncDemo folder
- Sideload it into the latest version of Oomph Viewer
As the App loads you will see a spinning icon in the bottom left hand corner indicating that the app is updating.
Note: The update is set for 60 seconds. If the app isn’t updating exit the app, and then clear the app from the iPad memory.