Google Spreadsheets

How to import Google Spreadsheet into Resolver One

We need to:

• download the .Net client library (the code was tested with the 1.1.2 version)
• grab the following DLLs from the lib/Release directory:
  • Google.GData.Spreadsheets.dll
  • Google.GData.Client.dll
  • Google.GData.Extensions.dll
• save them in the same directory where you save your rsl file.
• paste the following code to the pre-formulae section
• change the USER, PASSWORD and SPREADSHEET values.

That will import all of the worksheets for a given google spreadsheet into Resolver One.

import clr
clr.AddReference("Google.GData.Spreadsheets")
from Google.GData.Spreadsheets import (
    GDataSpreadsheetsNameTable, SpreadsheetsService,
    SpreadsheetQuery, WorksheetQuery, CellQuery,
)

USER, PASSWORD = "GOOGLE_USER", "GOOGLE_PASSWORD"
SPREADSHEET = 'SPREADSHEET_NAME'

service = SpreadsheetsService("Resolver")
service.setUserCredentials(USER, PASSWORD)


def importFromGoogle(spreadsheetName):
    spreadsheets = service.Query(SpreadsheetQuery()).Entries
    googleSpreadsheet = [wb
        for wb in spreadsheets
        if wb.Title.Text == spreadsheetName][0]
    link = googleSpreadsheet.Links.FindService(
        GDataSpreadsheetsNameTable.WorksheetRel, None)
    worksheets = service.Query(
        WorksheetQuery(link.HRef.ToString())).Entries
    for googleWorksheet in worksheets:
        cellFeedLink = googleWorksheet.Links.FindService(
            GDataSpreadsheetsNameTable.CellRel, None)
        cellQuery = CellQuery(cellFeedLink.HRef.ToString())
        cellFeed = service.Query(cellQuery)

        resultWorksheet = "Google - %s.%s" % (
            googleSpreadsheet.Title.Text, googleWorksheet.Title.Text)
        resolverWorksheet = workbook.AddWorksheet(resultWorksheet)
        for cellEntry in cellFeed.Entries:
            src = cellEntry.Cell
            dst = resolverWorksheet.Cells[int(src.Column), int(src.Row)]
            dst.Value = src.Value


#sometimes the authentication fails so we retry
retries = 3
while retries:
    retries -= 1
    try:
        importFromGoogle(SPREADSHEET)
        break
    except Exception: pass