1. Imports System
   2. Imports System.Collections
   3. Imports System.Text
   4. Imports Google.GData.Client
   5. Imports Google.GData.Extensions
   6. Imports Google.GData.Spreadsheets
   7.
   8. Namespace Spreadsheets
   9.     ''' <summary>
  10.     ''' SpreadsheetDemo is a simple console-based application
  11.     ''' to demonstrate the operations supported by the Google
  12.     ''' Spreadsheets Data API. It requires authentication in
  13.     ''' the form of your Google Docs & Spreadsheets username
  14.     ''' and password, and performs a number of operations on
  15.     ''' a worksheet of your choice.
  16.     ''' </summary>
  17.     Class SpreadsheetDemo
  18.         Private Shared userName As String, password As String
  19.         Private Shared allWorksheets As New ArrayList()
  20.
  21.         ''' <summary>
  22.         ''' Prints a list of all worksheets in the specified spreadsheet.
  23.         ''' </summary>
  24.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
  25.         ''' <param name="entry">the spreadsheet whose worksheets are to be retrieved</param>
  26.         Private Shared Sub PrintAllWorksheets(ByVal service As SpreadsheetsService, ByVal entry As SpreadsheetEntry)
  27.             Dim link As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, Nothing)
  28.
  29.             Dim query As New WorksheetQuery(link.HRef.ToString())
  30.             Dim feed As WorksheetFeed = service.Query(query)
  31.
  32.             Console.WriteLine("Worksheets in " & entry.Title.Text & ":")
  33.             For Each worksheet As WorksheetEntry In feed.Entries
  34.                 allWorksheets.Add(worksheet)
  35.                 Console.WriteLine(" " & worksheet.Title.Text)
  36.             Next
  37.             Console.WriteLine()
  38.         End Sub
  39.
  40.         ''' <summary>
  41.         ''' Prints a list of all the user's spreadsheets, and the
  42.         ''' list of worksheets that each spreadsheet contains.
  43.         ''' </summary>
  44.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
  45.         Private Shared Sub PrintAllSpreadsheetsAndWorksheets(ByVal service As SpreadsheetsService)
  46.             Dim query As New SpreadsheetQuery()
  47.             Dim feed As SpreadsheetFeed = service.Query(query)
  48.
  49.             Console.WriteLine("Your spreadsheets:")
  50.             For Each entry As SpreadsheetEntry In feed.Entries
  51.                 Console.WriteLine("Spreadsheet: {0}", entry.Title.Text)
  52.                 PrintAllWorksheets(service, entry)
  53.             Next
  54.         End Sub
  55.
  56.         ''' <summary>
  57.         ''' Retrieves and prints a list feed of the specified worksheet.
  58.         ''' </summary>
  59.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
  60.         ''' <param name="entry">the worksheet to retrieve</param>
  61.         ''' <param name="reverseRows">true if the rows in the worksheet should
  62.         ''' be reversed when returned from the server</param>
  63.         Private Shared Sub RetrieveListFeed(ByVal service As SpreadsheetsService, ByVal entry As WorksheetEntry, ByVal reverseRows As Boolean)
  64.             Dim listFeedLink As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, Nothing)
  65.
  66.             Console.WriteLine()
  67.             Console.WriteLine("This worksheet's list feed URL is:")
  68.             Console.WriteLine(listFeedLink.HRef)
  69.
  70.             Dim query As New ListQuery(listFeedLink.HRef.ToString())
  71.             If reverseRows Then
  72.                 query.OrderByPosition = True
  73.                 query.Reverse = True
  74.             End If
  75.             Dim feed As ListFeed = service.Query(query)
  76.
  77.             Console.WriteLine()
  78.             Console.WriteLine("Worksheet has {0} rows:", feed.Entries.Count)
  79.             For Each worksheetRow As ListEntry In feed.Entries
  80.                 Dim elements As ListEntry.CustomElementCollection = worksheetRow.Elements
  81.                 For Each element As ListEntry.Custom In elements
  82.                     Console.Write(element.Value & vbTab)
  83.                 Next
  84.                 Console.WriteLine()
  85.             Next
  86.         End Sub
  87.
  88.         ''' <summary>
  89.         ''' Executes a structured query against the list feed of
  90.         ''' the specified worksheet.
  91.         ''' </summary>
  92.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
  93.         ''' <param name="entry">the worksheet to query</param>
  94.         ''' <param name="queryText">the structured query</param>
  95.         Private Shared Sub StructuredQuery(ByVal service As SpreadsheetsService, ByVal entry As WorksheetEntry, ByVal queryText As String)
  96.             Dim listFeedLink As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, Nothing)
  97.
  98.             Dim query As New ListQuery(listFeedLink.HRef.ToString())
  99.             query.SpreadsheetQuery = queryText
 100.             Dim feed As ListFeed = service.Query(query)
 101.
 102.             Console.WriteLine()
 103.             Console.WriteLine("{0} rows matched your query:", feed.Entries.Count)
 104.             For Each worksheetRow As ListEntry In feed.Entries
 105.                 Dim elements As ListEntry.CustomElementCollection = worksheetRow.Elements
 106.                 For Each element As ListEntry.Custom In elements
 107.                     Console.Write(element.Value & vbTab)
 108.                 Next
 109.                 Console.WriteLine()
 110.             Next
 111.         End Sub
 112.
 113.         ''' <summary>
 114.         ''' Inserts a new row in the specified worksheet.
 115.         ''' </summary>
 116.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
 117.         ''' <param name="entry">the worksheet into which the row will be inserted</param>
 118.         ''' <returns>the inserted ListEntry object, representing the new row</returns>
 119.         Private Shared Function InsertRow(ByVal service As SpreadsheetsService, ByVal entry As WorksheetEntry) As ListEntry
 120.             Dim listFeedLink As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, Nothing)
 121.
 122.             Dim query As New ListQuery(listFeedLink.HRef.ToString())
 123.             Dim feed As ListFeed = service.Query(query)
 124.
 125.             Dim firstRow As ListEntry = TryCast(feed.Entries(0), ListEntry)
 126.             Dim newRow As New ListEntry()
 127.
 128.             Console.WriteLine()
 129.             Console.WriteLine("Inserting a new row...")
 130.             For Each element As ListEntry.Custom In firstRow.Elements
 131.                 Console.Write("Enter the value of column ""{0}"": ", element.LocalName)
 132.                 Dim elementValue As [String] = Console.ReadLine()
 133.
 134.                 Dim curElement As New ListEntry.Custom()
 135.                 curElement.LocalName = element.LocalName
 136.                 curElement.Value = elementValue
 137.
 138.                 newRow.Elements.Add(curElement)
 139.             Next
 140.
 141.             Dim insertedRow As ListEntry = feed.Insert(newRow)
 142.             Console.WriteLine("Successfully inserted new row: ""{0}""", insertedRow.Content.Content)
 143.
 144.             Return insertedRow
 145.         End Function
 146.
 147.         ''' <summary>
 148.         ''' Updates the value of a cell in a single worksheet row.
 149.         ''' </summary>
 150.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
 151.         ''' <param name="entry">the ListEntry representing the row to update</param>
 152.         ''' <returns>the updated ListEntry object</returns>
 153.         Private Shared Function UpdateRow(ByVal service As SpreadsheetsService, ByVal entry As ListEntry) As ListEntry
 154.             Dim firstColumn As ListEntry.Custom = entry.Elements(0)
 155.
 156.             Console.WriteLine()
 157.             Console.Write("Enter a new value for ""{0}"" (currently ""{1}""): ", firstColumn.LocalName, firstColumn.Value)
 158.             Dim newValue As [String] = Console.ReadLine()
 159.
 160.             firstColumn.Value = newValue
 161.
 162.             Dim updatedRow As ListEntry = TryCast(entry.Update(), ListEntry)
 163.
 164.             Console.WriteLine("Successfully updated ""{0}"": ""{1}""", updatedRow.Elements(0).LocalName, updatedRow.Elements(0).Value)
 165.
 166.             Return updatedRow
 167.         End Function
 168.
 169.         ''' <summary>
 170.         ''' Demonstrates retrieving and printing the cell feed for a
 171.         ''' worksheet.
 172.         ''' </summary>
 173.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
 174.         ''' <param name="entry">the worksheet whose cell feed is to be retrieved</param>
 175.         Private Shared Sub RetrieveCellFeed(ByVal service As SpreadsheetsService, ByVal entry As WorksheetEntry)
 176.             Dim cellFeedLink As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, Nothing)
 177.
 178.             Console.WriteLine()
 179.             Console.WriteLine("This worksheet's cells feed URL is:")
 180.             Console.WriteLine(cellFeedLink.HRef)
 181.
 182.             Dim query As New CellQuery(cellFeedLink.HRef.ToString())
 183.             Dim feed As CellFeed = service.Query(query)
 184.
 185.             Console.WriteLine()
 186.             Console.WriteLine("Cells in this worksheet:")
 187.             For Each curCell As CellEntry In feed.Entries
 188.                 Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value)
 189.             Next
 190.         End Sub
 191.
 192.         ''' <summary>
 193.         ''' Performs a cell range query on the specified worksheet to
 194.         ''' retrieve only the cells in the first column.
 195.         ''' </summary>
 196.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
 197.         ''' <param name="entry">the worksheet to retrieve</param>
 198.         Private Shared Sub CellRangeQuery(ByVal service As SpreadsheetsService, ByVal entry As WorksheetEntry)
 199.             Dim cellFeedLink As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, Nothing)
 200.
 201.             Dim query As New CellQuery(cellFeedLink.HRef.ToString())
 202.             query.MinimumColumn = 1
 203.             query.MaximumColumn = 1
 204.             query.MinimumRow = 2
 205.
 206.             Dim feed As CellFeed = service.Query(query)
 207.             Console.WriteLine()
 208.             Console.WriteLine("Cells in column 1:")
 209.             For Each curCell As CellEntry In feed.Entries
 210.                 Console.WriteLine("Row {0}: {1}", curCell.Cell.Row, curCell.Cell.Value)
 211.             Next
 212.         End Sub
 213.
 214.         ''' <summary>
 215.         ''' Updates a single cell in the specified worksheet.
 216.         ''' </summary>
 217.         ''' <param name="service">an authenticated SpreadsheetsService object</param>
 218.         ''' <param name="entry">the worksheet to update</param>
 219.         Private Shared Sub UpdateCell(ByVal service As SpreadsheetsService, ByVal entry As WorksheetEntry)
 220.             Dim cellFeedLink As AtomLink = entry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, Nothing)
 221.             Dim query As New CellQuery(cellFeedLink.HRef.ToString())
 222.             Console.WriteLine()
 223.
 224.             Console.Write("Row of cell to update? ")
 225.             Dim row As String = Console.ReadLine()
 226.
 227.             Console.Write("Column of cell to update? ")
 228.             Dim column As String = Console.ReadLine()
 229.
 230.             query.MinimumRow = InlineAssignHelper(query.MaximumRow, UInteger.Parse(row))
 231.             query.MinimumColumn = InlineAssignHelper(query.MaximumColumn, UInteger.Parse(column))
 232.
 233.             Dim feed As CellFeed = service.Query(query)
 234.             Dim cell As CellEntry = TryCast(feed.Entries(0), CellEntry)
 235.
 236.             Console.WriteLine()
 237.             Console.WriteLine("Current cell value: {0}", cell.Cell.Value)
 238.             Console.Write("Enter a new value: ")
 239.             Dim newValue As String = Console.ReadLine()
 240.
 241.             cell.Cell.InputValue = newValue
 242.             Dim updatedCell As AtomEntry = cell.Update()
 243.
 244.             Console.WriteLine("Successfully updated cell: {0}", updatedCell.Content.Content)
 245.         End Sub
 246.
 247.         ''' <summary>
 248.         ''' Creates a new SpreadsheetsService with the user's specified
 249.         ''' authentication credentials and runs all of the Spreadsheets
 250.         ''' operations above.
 251.         ''' </summary>
 252.         Private Shared Sub RunSample()
 253.             Dim service As New SpreadsheetsService("exampleCo-exampleApp-1")
 254.             service.setUserCredentials(userName, password)
 255.
 256.             ' Demonstrate printing all spreadsheets and worksheets.
 257.             PrintAllSpreadsheetsAndWorksheets(service)
 258.
 259.             ' Demonstrate retrieving the list feed for a single worksheet,
 260.             ' with the rows (ordered by position) reversed.
 261.             Dim userChoice As Integer = GetUserWorksheetChoice()
 262.             Dim entry As WorksheetEntry = TryCast(allWorksheets(userChoice), WorksheetEntry)
 263.
 264.             RetrieveListFeed(service, entry, True)
 265.
 266.             ' Demonstrate sending a structured query.
 267.             Console.Write("Enter a structured query to execute: ")
 268.             Dim queryText As String = Console.ReadLine()
 269.             StructuredQuery(service, entry, queryText)
 270.
 271.             ' Demonstrate inserting a new row in the worksheet.
 272.             Dim insertedEntry As ListEntry = InsertRow(service, entry)
 273.
 274.             ' Demonstrate updating the inserted row.
 275.             UpdateRow(service, insertedEntry)
 276.
 277.             ' Demonstrate deleting the entry.
 278.             insertedEntry.Delete()
 279.
 280.             ' Demonstrate retrieving a cell feed for a worksheet.
 281.             RetrieveCellFeed(service, entry)
 282.
 283.             ' Demonstrate a cell range query.
 284.             CellRangeQuery(service, entry)
 285.
 286.             ' Demonstrate updating a single cell.
 287.             UpdateCell(service, entry)
 288.         End Sub
 289.
 290.         ''' <summary>
 291.         ''' Prompts the user for a number representing one of their
 292.         ''' worksheets; this worksheet will then be used to demonstrate
 293.         ''' the various Spreadsheets operations above.
 294.         ''' </summary>
 295.         ''' <returns>the number of the worksheet chosen by the user</returns>
 296.         Private Shared Function GetUserWorksheetChoice() As Integer
 297.             Console.WriteLine("Select the worksheet on which to demonstrate")
 298.             Console.WriteLine("add/edit/delete operations by entering its number:")
 299.             Console.WriteLine()
 300.             For i As Integer = 0 To allWorksheets.Count - 1
 301.                 Dim entry As WorksheetEntry = TryCast(allWorksheets(i), WorksheetEntry)
 302.                 Console.WriteLine("{0}: {1}", i + 1, entry.Title.Text)
 303.             Next
 304.
 305.             Console.WriteLine()
 306.             Console.Write("Your choice: ")
 307.             Dim userResponse As [String] = Console.ReadLine()
 308.
 309.             Return Integer.Parse(userResponse) - 1
 310.         End Function
 311.
 312.         ''' <summary>
 313.         ''' Program entry point.
 314.         ''' </summary>
 315.         ''' <param name="args">the username and password used to log
 316.         ''' in to Google Docs & Spreadsheets. For example:
 317.         '''
 318.         ''' SpreadsheetDemo jdoe@gmail.com mypassword
 319.         ''' </param>
 320.         Private Shared Sub Main(ByVal args As String())
 321.             If args.Length < 2 Then
 322.                 Console.[Error].WriteLine("Syntax: SpreadsheetDemo <username> <password>")
 323.                 Exit Sub
 324.             Else
 325.                 userName = args(0)
 326.                 password = args(1)
 327.
 328.                 RunSample()
 329.             End If
 330.         End Sub
 331.         Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T
 332.             target = value
 333.             Return value
 334.         End Function
 335.     End Class
 336. End Namespace