Google Spreadsheets

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.