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 |
Partager