6

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.

7

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[12]),float(datum[11]))))
    f.setAttributes(datum)
    pr.addFeature(f)
    vl.updateExtents()
    QgsProject.instance().addMapLayer(vl)
| improve this answer | |
  • Hello Tyler, I'm intrigued if you had any updates on this? – BJW Mar 7 at 8:31
4

Just to leave another option here, but 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) and use the materialized view in QGIS.

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.

To connect to a Google Sheet via a PostgreSQL FDW, we use the Multicorn

https://github.com/Kozea/Multicorn

Then install the gspreadsheet_fdw extension for Multicorn:

https://github.com/lincolnturner/gspreadsheet_fdw

The instructions in the gspreadsheet_fdw show how to create the FDW table from your Google Sheet. From there, you can build views/materialized views to connect spatial data, and from there consume it anywhere you want - either QGIS, or connect through Node.Js, or even publish to Geoserver.

| improve this answer | |
  • 1
    This sounds really useful and interesting, I'd love to know more and think it would be a great addition to this answer! – GISKid Feb 21 at 19:06
  • 1
    @GISKid ok cool let me add some more details. Thanks for the comment! – DPSSpatial Feb 21 at 19:50
  • 1
    @GISKid sorry for the delay, but posted a few links to the FDW's we're using. Let me know if you need any more info for now... – DPSSpatial Feb 27 at 17:22
3

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.