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 the unique_event_ID field of all new events. By default, it is set as 1001, so that all new events will have a four-digits ID like 1001, 1002, 1003... You can chose another format for your Event ID by entering a different number in the Last_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.