Lire un Google SHEET (google.apis.sheet)

Version imprimable

Bonjour,

Voici 3 semaines que je cherche partout comment lire un Google sheet sous VB.NET, et je me tourne vers vous. (j'ai créé beaucoup perso d'appli grâce a vous, sans poser aucune question, mais la, je sèche)

Pour débuter dans le domaine des API, je veux juste afficher dans un Texbox, la valeur de la cellule A2 de mon sheet qui se nomme "sheetapi"

voici le code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Imports System.IO
Imports System.Threading
Imports Google.Apis.Auth.OAuth2
Imports Google.Apis.Services
Imports Google.Apis.Sheets.v4
Imports Google.Apis.Sheets.v4.Data
Imports Google.Apis.Util.Store
 
Public Class Form1
    Shared Scopes As String() = {SheetsService.Scope.SpreadsheetsReadonly}
    Shared ApplicationName As String = "Google Sheets API .NET Quickstart"
    Public service As SheetsService = New SheetsService
 
 
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
 
        Dim credential As UserCredential
        Using stream = New FileStream("client_secret.json", FileMode.Open, FileAccess.Read)
            Dim credPath As String = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal)
            credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json")
 
            credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.FromStream(stream).Secrets, Scopes, "user", CancellationToken.None, New FileDataStore(credPath, True)).Result
            Console.WriteLine(Convert.ToString("Credential file saved to: ") & credPath)
        End Using
 
        Dim service = New SheetsService(New BaseClientService.Initializer() With {.HttpClientInitializer = credential, .ApplicationName = ApplicationName})
        Dim spreadsheetId As [String] = "sheetapi"
 
        Dim range As [String] = "A2"
 
        Dim request As SpreadsheetsResource.ValuesResource.GetRequest = service.Spreadsheets.Values.[Get](spreadsheetId, range)
        Dim response As ValueRange = request.Execute
        Dim values As IList(Of IList(Of [Object])) = response.Values
        TextBox1.Text = response.Values
 
 
    End Sub
 
 
End Class

A la ligne 33 Dim response As ValueRange = request.Execute j'ai l'erreur

Citation:

Google.GoogleApiException*: 'The service sheets has thrown an exception. HttpStatusCode is NotFound. Requested entity was not found.'

Si quelque a une idée ... MERCI

12/08/2023, 17h52
binarygirl

Manifestement, c'est une erreur 404.
Donc, vérifiez la valeur que vous récupérez et transmettez à GetRequest... Il ne faut pas jouer à la devinette, utilisez print pour afficher toutes les variables et assurez-vous que les valeurs tiennent la route.

Sinon, il serait peut-être utile de partir d'un code existant comme celui-ci et l'adapter à votre besoin.
12/08/2023, 18h05
franalie

Super

SUPER !!!

Merci pour le lien de l'exemple, je pense que je vais en apprendre beaucoup.

Je vous tiens informé de mes avancés.

MERCI

j'avance doucement, mais ...

J'ai creer un projet avec le code pris sur lien.

voici comment j'ai adapter le code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Imports System.IO
Imports System.Threading
Imports Auth0.ManagementApi.Models
Imports Google.Apis.Auth.OAuth2
Imports Google.Apis.Services
Imports Google.Apis.Sheets.v4
Imports Google.Apis.Sheets.v4.Data
Imports Google.Apis.Util.Store
Imports Microsoft.SqlServer
Imports Microsoft.VisualBasic.ApplicationServices
 
Public Class Form1
    Private newRow As Integer
 
    Public Sub insertToSheet()
        Dim credential As UserCredential
        Static Dim Scopes As String() = {SheetsService.Scope.Spreadsheets} 'If changing the scope then delete  App_Data\MyGoogleStorage\.credentials\sheets.googleapis.com-dotnet-quickstart.json
        Dim ApplicationName As String = "Google Sheets API .NET Quickstart" 'Tutorial name for .net api
        'Dim location = Server.MapPath("client_secret.json") 'Designate file with sheets api key and use it to setup authentication
        Using stream = New FileStream("client_secret.json", FileMode.Open, FileAccess.Read) 'Read file and setup credentials for sheets api
 
            Dim credPath As String = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal)
            credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json")
 
            credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.FromStream(stream).Secrets, Scopes, "user", CancellationToken.None, New FileDataStore(credPath, True)).Result
            Console.WriteLine(Convert.ToString("Credential file saved to: ") & credPath)
 
 
            'Dim credPath As String = System.Web.HttpContext.Current.Server.MapPath("/App_Data/MyGoogleStorage")
            'credPath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("/App_Data/MyGoogleStorage"), ".credentials/sheets.googleapis.com-dotnet-quickstart.json")
            ' Dim credential As UserCredential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None, New FileDataStore(credPath, True)).Result
        End Using
 
        Dim service = New SheetsService(New BaseClientService.Initializer() With {.HttpClientInitializer = credential, .ApplicationName = ApplicationName}) 'Create Google Sheets API service for connecting to the API.
        Dim spreadsheetId As String = "1MilquBL1ut6Rd3XNUboXIB5U7ePvpQO6B-AduL787XM"
        Dim oblist = New List(Of Object)() From {"Data to write to cell"}
        Dim valueRange As New ValueRange()
        valueRange.MajorDimension = "COLUMNS"
        valueRange.Values = New List(Of IList(Of Object))() From {oblist}
        Dim subSheetID As String = "313470273"
        Dim range As String = subSheetID & "!A5"
        Dim writeCellRowRequest As SpreadsheetsResource.ValuesResource.AppendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, range)
        If newRow = 1 Then 'For adding a new row if needed, sheets can only add a new row if the row it is adding at is an empty row, if not empty then it will add the new row to the next available empty row 
            writeCellRowRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS
        End If 'If not adding new row then will overwrite cell
        writeCellRowRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW
        writeCellRowRequest.Execute()
 
 
 
    End Sub
 
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
 
 
        insertToSheet()
 
 
    End Sub
End Class

à la ligne 47 writeCellRowRequest.Execute() j'ai l'erreur (correspond a une 403)

Citation:

Google.GoogleApiException*: 'The service sheets has thrown an exception. HttpStatusCode is Forbidden. Request had insufficient authentication scopes.'

MERCI

13/08/2023, 01h18
franalie

ca marche !!!!

Donc voici les choses qui m'ont bloqué (pour ceux a qui cela va arriver !!!)

La première erreur (404) était du a la ligne 27 Dim spreadsheetId As [String] = "sheetapi" il faut inscrire l'identification du sheet que l'on trouve dans la barre d'adresse quand on est sur le sheet
https://docs.google.com/spreadsheets...7XM/edit#gid=0
ce qui donneDim spreadsheetId As String = "1MilquBL1ut6Rd3XNUboXIB5U7ePvpQO6B-AduL787XM".
Pour la deuxième erreur, j'ai effacé le fichier présent dans le répertoire .credentials\ .............

ET TOUT FONCTIONNE.

MERCI