Google Spreadsheets

Thanks to the Google Spreadsheets Data API we can easily import a Google Spreadsheet into Resolver One.

Importing a Google Spreadsheet into Resolver One

  1. Download the .NET client library (the code was tested with the 1.1.2 version).

  2. Grab the following DLLs from the lib/Release directory:

    • Google.GData.Spreadsheets.dll
    • Google.GData.Client.dll
    • Google.GData.Extensions.dll
  3. Save the DLLS in the same directory as your .rsl file.

  4. Paste the following code into the pre-formula user code (change the values of USER, PASSWORD and SPREADSHEET):

    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
    

    The resulting user code should import all of the worksheets for a given Google spreadsheet into Resolver One.

Comments