Is there a way to connect directly form QGIS to a Google spreadsheet?
The purpose for pulling data from a Google spreadsheet is diverse. This could be to pull coordinate values from a questionnaire or content for atlas making.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
There is a way to connect the sheet via the Google sheets API as Cezar mentioned. Instructions can be found here. After connecting to the API, you have to create a python (pyQGIS) script that pulls from the google sheet and creates points for the layer, a tutorial can be found here. I created an example (with sensitive info removed) which I have attached below. This code is not yet complete. Once I clean it up I'll try to publish it as a toolbar. In the meantime...just add your sheet id and sheetname/range.
#load the required packages/libraries/etc from __future__ import print_function import pickle import os.path from googleapiclient.discovery import build from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request from qgis.PyQt.QtCore import QVariant # If modifying these scopes, delete the file token.pickle. SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] # The ID and range of a sample spreadsheet. SAMPLE_SPREADSHEET_ID = 'SHEETID' SAMPLE_RANGE_NAME = 'DATA_SHEET!Range:' #adapted from quickstart def getDataFromTable(): """Shows basic usage of the Sheets API. Prints values from a sample spreadsheet. """ creds = None # The file token.pickle stores the user's access and refresh tokens, and is # created automatically when the authorization flow completes for the first # time. if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) # If there are no (valid) credentials available, let the user log in. if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( 'credentials.json', SCOPES) creds = flow.run_local_server() # Save the credentials for the next run with open('token.pickle', 'wb') as token: pickle.dump(creds, token) service = build('sheets', 'v4', credentials=creds) # Call the Sheets API sheet = service.spreadsheets() result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME).execute() values = result.get('values', ) return values data = getDataFromTable() headers = data.pop(0) #now we can start the qgis-y stuff vl = QgsVectorLayer("Point", "temp", "memory") pr = vl.dataProvider() for attrb in headers: pr.addAttributes([QgsField(attrb, QVariant.String)]) vl.updateFields() for datum in data: print(datum) f = QgsFeature() f.setGeometry(QgsGeometry.fromPointXY(QgsPointXY(float(datum),float(datum)))) f.setAttributes(datum) pr.addFeature(f) vl.updateExtents() QgsProject.instance().addMapLayer(vl)
We have been pulling Google Sheets into QGIS via PostgreSQL Foreign Data Wrappers. We then build a materialized view that connects records to geometry (via School Numbers, for example) and use the materialized view as a standard spatial table.
From there we have also published this 'google sheet materialized view' to a web application via node.js and add a button to refresh the map if the google sheet data has been changed. It works really well.
(Note: The gsspreadsheet extension to the multicorn FDW is questionably maintained, and there is the risk that v4 of the GSheets API may break it... we don't know yet, and are planning for the possibility that we might have to implement Method 2 (below) if it does indeed break. )
To connect to a Google Sheet via a PostgreSQL FDW, use the Multicorn FDW:
Then install the gspreadsheet_fdw extension for Multicorn:
In PostgreSQL, create the multicorn FDW:
CREATE SERVER multicorn_gspreadsheet FOREIGN DATA WRAPPER multicorn OPTIONS (wrapper 'gspreadsheet_fdw.GspreadsheetFdw');
Then create the FDW table connecting to the Google Sheet using the gspreadsheet extension of multicorn:
CREATE FOREIGN TABLE speced_fdw.centerprogram_current_gsheet ( column1 integer NULL, column2 varchar NULL, column3 varchar NULL ) SERVER multicorn_gspreadsheet OPTIONS (keyfile '/usr/pgsql-9.5/share/credential.json', gskey 'example_d33lk2kdislids');
You now have a foreign table pointing directly to your Google Sheet, which you can built a materialized view from.
The other option is to use the FILE_FDW right out of PostgreSQL and connect to the CSV export of the GSheet using WGET.
First, create the server:
CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw OPTIONS ()
Then create the FDW table:
CREATE FOREIGN TABLE public.test_file_fdw ( "name" varchar NOT NULL, "date" varchar NULL, "address" varchar NULL ) SERVER fdw_files OPTIONS (program 'wget -q -O - "https://docs.google.com/spreadsheets/d/2343randomurlcharacters_r0/export?gid=969976&format=csv"', format 'csv', header 'true');
The above options for wget are listed here: https://www.gnu.org/software/wget/manual/html_node/HTTP-Options.html
The most basic option is to export the table as a CSV file and import it in QGIS. You there have the option to watch the file for updates.
If exporting CSVs isn't an option, you can use a desktop spreadsheet and connect it to a database, or use a database in the first place. A good example from actual practice can be found in an older answer.
You should also look at the Google Sheets API to see if you can use it in some way.