1
\$\begingroup\$

I have data stored in a Google Sheet. The aim of this program controller is to read this data from an MVC controller, and populate a class (Model) with it.

I will skip most of the intermediate functions, to keep the focus on the Google API connection.

My main concern is with the connection, and use of the Google API (could be any API though).

  • I have doubts about creating a connection object in the controller this way (in particular since I have multiple controllers, that will all create a new API object)

  • I am new to coding, so any general advice on best coding practices will be welcome

Notes:

  • The APIKey is stored in the root folder of the project, and called APIKey.json
  • For reusability reasons (there is a desktop sister-app) sheetData and GoogleAPI classes are in a separate DLL.

Controller:

Function Data(sheetID As String) As ActionResult
    Dim API As New GoogleAPI(Web.HttpContext.Current.Request.PhysicalApplicationPath + "APIKey.json")

    Dim modelData = New sheetData(API, sheetID).Datum

    Return View(modelData)
End Function

sheetData class:

Public Class sheetData
    Public ReadOnly Property Datum As String

    Public Sub New(googleAPIConnection As GoogleAPI, sheetID As String)
       Const dataRange = "Sheet1!A1"
       Datum = googleAPIConnection.GetValuesFromGoogleSheet(sheetID, dataRange)(0)(0)
    End Sub
End Class

GoogleAPI class:

Public Class GoogleAPI
    Friend _credentials As GoogleCredential
    Public Sub New(apikeypath As String)
        Using stream As New FileStream(apikeypath, FileMode.Open, FileAccess.Read)
            _credentials = GoogleCredential.FromStream(stream).CreateScoped(SheetsService.Scope.Drive, SheetsService.Scope.Spreadsheets)
        End Using
    End Sub

    Public Function GetValuesFromGoogleSheet(sheetID As String, requestedRange As String) As IList(Of IList(Of Object))
        Dim service = New SheetsService(New BaseClientService.Initializer() With {
               .HttpClientInitializer = _credentials,
               .ApplicationName = "appName"
                                       })
        Dim request As SpreadsheetsResource.ValuesResource.GetRequest =
            service.Spreadsheets.Values.Get(sheetID, requestedRange)
        Dim requestResponse As ValueRange = request.Execute
        Return requestResponse.Values
    End Function
End Class
\$\endgroup\$

Your Answer

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

Browse other questions tagged or ask your own question.