Setting up Pattrn with Google Sheets¶
Prerequisites¶
Starting with Pattrn 2.0, the recommended way to set up a Pattrn instance is by using a GeoJSON dataset (see Getting started and GeoJSON datasets).
The legacy data backend introduced with Pattrn 1.0, however, is still supported for users who wish to use Google Sheets to set up a simple Pattrn instance quickly, or who wish to use the Pattrn Editor for a collaborative data reporting workflow.
When setting up a Pattrn instance using Google Sheets as the data backend, all you will need to set up your own Pattrn Platform is:
- one Google Account with Google Drive
- basic knowledge of Google Sheets
- some web hosting space
- basic knowledge of how to put a website online
Below is a step-by-step guide to set up a Pattrn Platform. All in all, the process should not take longer than 20 minutes.
Setting up the Google Sheets data source¶
Copying the Pattrn sheets to your Google Drive¶
Log in to the Google account you will use with Pattrn. Make sure you have activated Google Drive for this account.
Access the Pattrn sheets by clicking on this link.
Click on the file named PATTRN_Admin
, then click on the “Pop-Out” icon
in the top right corner of the viewer to open the file in Google Sheets.
Once it is open, go to File > Make a copy
. Rename the copy
PATTRN_Admin
(remove Copy of
) and click OK. A copy of this
document, of which you are now the owner, is added to your Google Drive.
Do the same operation for the four other spreadsheet files in the Pattrn
folder (PATTRN_Admin_Password
, PATTRN_Audit
, PATTRN_Master
,
PATTRN_WIP
), so as to copy them all to your drive.
Finally, in the Pattrn
folder, click on the PATTRN_Editor_Script
file,
then click on the “Pop-Out” icon in the top right corner of the viewer
to open the file in Google Apps Script. Similarly, go to File > Make
a copy
(Note that it may take a few seconds to load the copy of the
script). Rename the copied script as PATTRN_Editor
(remove Copy
of
).
Now, all the Pattrn sheets have been copied into in the root folder of your Google Drive.
Inputting the IDKeys¶
Go to your Google Drive
Open the PATTRN_Master
spreadsheet.
Copy the IDKey of the Spreadsheet. The IDKey is to be found in the URL of the spreadsheet displayed in your browser. For example:
https://docs.google.com/spreadsheets/d/1tn6K498HPon80F57QDBGHUz6K3tA-AN2zWT_g0ruaqY/edit#gid=1782249319
In the example above the IDKey is the part 1tn6K498HPon80F57QDBGHUz6K3tA-AN2zWT_g0ruaqY
.
Open the PATTRN_Admin
Spreadsheet in your Google Drive
Go to the Keys
sheet (tab at the bottom). Paste the IDKey of the
PATTRN_Master
spreadsheet in the corresponding cell.
Repeat this operation for the PATTRN_WIP
, PATTRN_Admin_Password
, and
PATTRN_Audit
spreadsheets, so as to input their IDKeys in the Keys
sheet of the PATTRN_Admin
spreadsheet.
Next, go to your Google Drive, create a new folder, and name it
PATTRN_Photos
. Double click the folder to open it.
In the address bar of your browser, copy the IDKey of this folder. The IDKey is the last part of the URL displayed. For example:
https://drive.google.com/drive/folders/0BwR9PFzDne86TTIwZFFSUmpocW8
In this example above, the IDKey of the folder is the part 0BwR9PFzDne86TTIwZFFSUmpocW8
.
Paste the IDKey of the PATTRN_Photos
folder in the corresponding cell,
in the Keys
tab of the PATTRN_Admin
spreadsheet.
Finally, get the IDkey of the PATTRN_Admin
spreadsheet, copy it, and
keep it in your clipboard.
Synchronising the Pattrn Editor script with the spreadsheets¶
In your Google Drive, double-click the PATTRN_Editor_Script
. Google
Drive will suggest to open it with a third-party app. Select Google Apps
Script. The script will open on Google Apps Script.
In code.gs
(tab active by default in the left column), on line 9,
Replace InputKey
by pasting the IDKey of PATTRN_Admin
spreadsheet copied
to the clipboard in the step above. Remember to keep the single quotation
marks around your IDKey. You should obtain a line looking like:
userProperties.setProperty('Admin','1v673QgP3li69umab5x0_e7krurJB0pYZLeyrreYiUnw');
With the actual IDKey of your PATTRN_Admin
spreadsheet.
In Google Apps Script, go to Run > Set_Script_Properties
The application will run the function Set_Script_Properties
You will get a message warning that an Authorization is required. Click
Review permissions
, then click Allow
.
Next, Go to Run > Load_keys_from_admin
The application will run the function Load_keys_from_admin
Once you have run both functions run, go back to line 9 in Code.gs
, and
delete the IDKey of your PATTRN_Admin
spreadsheet by reverting back
to InputKey
. At the end of the process, line 9 should look again like:
userProperties.setProperty('Admin', 'InputKey');
Warning: This is an important security measure. It is to prevent
anyone from getting the ID key of your ``PATTRN_Admin`` spreadsheet while
the script is deployed on the web – from which one could then access the
files, usernames, and passwords you are using with Pattrn. Remember to
delete your IDKey and replace it by InputKey
.
Go to File > Save
.
You have now synchronised the Pattrn Editor script with all your Pattrn spreadsheets.
Publishing the script and the Master spreadsheet¶
In Google Apps Script, with the PATTRN_Editor_Script
still open, go to
Publish > Deploy as web app...
In the parameters, select:
- Execute the app as: Me
- Who has access to the app: Anyone, even anonymous
Click Deploy
.
A message appears to confirm that the project is deployed as a web app, and the URL of the web app is displayed. Copy this URL.
In your Google Drive, open the PATTRN_Admin
spreadsheet.
Paste the URL of the script in cell A2
.
Finally, open the PATTRN_Master
spreadsheet.
Go to File > Publish to the web
.
In the menu that appears, keep the default settings to publish the
Entire Document
as a Web Page
.
Click Publish
The PATTRN_Master
spreadsheet is now published to the web.
Pattrn Editor configuration¶
Open the PATTRN_Admin
spreadsheet in your Google Drive.
You can adjust the settings of the Editor here. Those include:
- the country ISO code to be used for automatic geo-location of street addresses
- the GPS precision: the number of decimals used in the latitude and longitude coordinates
- the starting latitude and longitude of the pin that will be displayed on the Pattrn Editor’s map (when entering locational information by dropping a pin on the map)
- the minimum and maximum values allowed for latitude and longitude
- the default zoom of the map
- the name of the Pattrn instance that will be displayed in the Editor
- the
Last_ID
: this corresponds to the number that the Pattrn Editor will use to generate incremental numbers and populate theunique_event_ID
field of all new events. By default, it is set as1001
, so that all new events will have a four-digits ID like1001
,1002
,1003
... You can chose another format for your Event ID by entering a different number in theLast_ID
field.
Warning: Be sure to set up the Last_ID
field once and for all
when you start your Pattrn Platform. This field will be automatically
updated as you use the Pattrn Editor, with the last Event ID created
replacing the value in the Last_ID
field. Changing the value in the
Last_ID
field will mess the incremental process of creation of unique
Event IDs, and could result in the creation of events with the same
Event ID, or inconsistent Event IDs.
Creation of usernames and passwords for Editors¶
Open the PATTRN_Admin_Password
spreadsheet in your Google Drive.
Here you can create usernames and passwords for the Editors that will have access to the Pattrn Editor of your Platform.
You can use any combination of letters and numbers for both usernames and passwords.
No symbols, punctuation signs, or special characters are allowed for passwords.
Usernames and passwords are case sensitive.
There is no minimum length of usernames and passwords. Nonetheless, for security reasons, it is recommended that you use strong and unique passwords for all Editors (8+ characters).
Note: For security reasons, make sure to keep the
PATTRN_Admin_Password
spreadsheet private in your Google Drive
(not shared nor published on the web).
You have now finished setting up the Pattrn Editor.
Setting up the Pattrn app¶
Downloading the Pattrn app files¶
Open the Pattrn downloads page in your browser.
Click on the zip
download link for the most recent version. A Zip file
with the Pattrn source code will be downloaded to your default download folder.
Extract the Zip file just downloaded, and open the pattrn
folder.
Configuring the Pattrn app¶
Go to your Google Drive, and open the PATTRN_Master
spreadsheet.
Go to File > Publish to the web...
Copy the URL of the published PATTRN_Master
spreadsheet
In the pattrn
folder, go to the dist
folder, open the
config-gapps.json
file.
Warning: JSON files need straight double quotation marks to work; they
won’t work with curly ones. Make sure the text editing application does
NOT automatically replace straight quotation marks with curly ones. This
is the case with Apple’s TextEdit as used by defaut. In this case,
before editing the config.json file with TextEdit, it is recommended to
Go to TextEdit > Preferences
, and untick smart quotes
, smart dashes
and Text replacement
. Whatever your OS and your text editing
application, double-check that all quotation marks in the JSON file are
straight ones!
In the config-gapps.json
file, paste the URL of the published PATTRN_Master
spreadsheet (still in your cilpboard) in the public_spreadsheet
field.
You should obtain a line looking like:
"public_spreadsheet":"https://docs.google.com/spreadsheets/d/1ajKCXgSyTvUC3rYGaFVYp54588lYC8kPMBd4h9YTWhg/pubhtml",
with the part between double quotation marks after the colon corresponding to the actual URL of your
PATTRN_Master
spreadsheet.
Next, return to your Google Drive, and open the PATTRN_Admin
spreadsheet.
Copy the URL displayed in cell A6 (script_url_for_platform_config
).
Back to the config-gapps.json
file, paste it in the script_url
field.
You should obtain a line looking like:
"script_url":"https://script.google.com/macros/s/AKfycbygvVCS3pzWL9WaGbTQ486AyKY0eS8o0h-EQgIDeI6Hzb58i6hA/exec?par=",
with the part between double quotation marks after the colon corresponding to the actual URL of your
PATTRN_Editor_Script
, as displayed in cell A6 of your PATTRN_Admin
spreadsheet.
Save your config-gapps.json
file and rename it to config.json
,
keeping it in the same folder where the config-gapps.json
was.
Configuring the baselayers of the map¶
By editing the config.json
file, you can also add a series of
baselayers to the map in your Pattrn app. Below a few examples.
OpenStreetMap¶
"name" : "OpenStreetMap", "URL" : "http://{s}.tile.osm.org/{z}/{x}/{y}.png"
Stamen¶
"name" : "Stamen", "URL" : "http://{s}.tile.stamen.com/toner/{z}/{x}/{y}.png"
Satellite (via MapBox)¶
"name" : "Satellite", "URL" : "https://{s}.tiles.mapbox.com/v4/{mapid}/{z}/{x}/{y}.png?access_token=****"
with {mapid}
replaced by the Map ID you will have selected from the
MapBox Maps API page
(for example: mapbox.satellite
) and ****
replaced by your actual
“Default Access Token” that you will find in Studio > Account > API
access tokens
. You should end up with a URL looking like:
"URL"="https://{s}.tiles.mapbox.com/v4/mapbox.satellite/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoiZnJhbmFzZWJyZSIsImEiOiJjaWoycG05emswMDBid2JsenducjJzM3hiIn0.v9cylsLP5rOr87jh66UNTw"
with the parts immediately after v4/
and immediately after access_token=
replaced by your personal ones.
Save and close the config.json
file.
[Optional] Add sharing buttons to your Pattrn app¶
In order to display sharing buttons from the “SHARE” link to be found in the Header of the Pattrn app, you will need to use an AddThis account (free).
Sign up for an AddThis account or log in to your existing one.
Go to “Tools” and select “Sharing Buttons”. Configure the Sharing Buttons as you see fit, then click Activate. Copy the code displayed in the “Basic Code” section.
In the dist
subfolder of the pattrn
folder, open the index.html
file with an text editor application (such as the Atom
editor).
On line 531 of the index.html
file, paste the Add This code you
copied. The complete script should look like this:
<!-- Optional - Addthis code to be added here (it will populate the 'Share' button) -->
<script
type="text/javascript"
src="http://s7.addthis.com/js/300/addthis_widget.js#pubid=ra-55af788b2148ea27"
async="async">
</script>
</body>
With the part in bold to be replaced with the actual code you will get from your AddThis dashboard.
Save and close the index.html
file.
Upload the Pattrn app files to your web server¶
Connect to your web server using your FTP application.
Enter the user name and password and server information provided by your web host. If you are uncertain about this information, contact your web host.
Upload all the contents of the dist
subfolder of your local
pattrn
folder to your web server (but not the dist
folder
itself).
Once uploaded, your Pattrn app will be accessible at the URL of your website.
Your Pattrn app is now all set up and ready to visualise your data.
Note: By default, if the Master spreadsheet has not been edited, the Pattrn app should load with one dummy event located in London. If it doesn’t load properly, please see the Troubleshooting section.
Pattrn settings¶
Open the PATTRN_Master
spreadsheet in your Google Drive, and go to the
Settings sheet (second tab at the bottom left).
By editing the content of the cells in this tab, you can:
- enter a Title for your Pattrn app
- enter a Subtitle for your Pattrn app
- choose a Contrast Colour that will customise the appearance of
- your Pattrn app. A link to an online colour picker will be displayed as a comment to this cell.
- edit the About section of your Pattrn app, which will inform
- he users about the project and scope of the app.
Note: In the About section, you can use html language for advanced formatting.
[Optional] Run your Pattrn app locally on your computer¶
If you want to test your Pattrn app before publishing it online, you can do so by running it locally on your computer.
Using the Terminal, navigate to the dist
subfolder of your
local pattrn
folder.
Run a Python server by entering the following command line:
python -m SimpleHTTPServer
If prompted, click “Allow” to let the Python application accept incoming network connections.
Open your browser at http://localhost:8000/
.
Your Pattrn app will load in your browser.
Note: you will still require an Internet connection, as the data will
still be loaded from the PATTRN_Master
spreadsheet online.