如何使用vb.net?在现有谷歌电子表格中插入新工作表

Question

我设法使用以下代码在谷歌驱动器上创建电子表格。现在我想在创建的电子表格中添加新表。

当我创建新的电子表格时，它包含名为Sheet1的默认工作表，但我想重命名该工作表或在电子表格中插入多个工作表

Public Function Create(ByVal FileName As String) As String
        Try

            Dim service = GetGoogleAPPDriveService()


            Dim fileMetadata = New Google.Apis.Drive.v3.Data.File()
            fileMetadata.Name = FileName
            fileMetadata.MimeType = "application/vnd.google-apps.spreadsheet"

            Dim request As FilesResource.CreateRequest = service.Files.Create(fileMetadata)

            request.SupportsTeamDrives = True
            request.Fields = "id"
            System.Net.ServicePointManager.ServerCertificateValidationCallback = New System.Net.Security.RemoteCertificateValidationCallback(AddressOf AcceptAllcertification)
            Dim file = request.Execute

            Return file.Id

        Catch ex As Exception
            Throw ex
        End Try
    End Function 

Private Function GetGoogleAPPDriveService() As DriveService
        Try
            Dim Scopes1 As String() = {DriveService.Scope.Drive, DriveService.Scope.DriveFile}
            Dim ClientId As String = ""
            Dim ClientSecret As String = ""
            Dim UserCredentials As UserCredential

            Dim stream = New FileStream("credentials.json", FileMode.Open, FileAccess.Read)
            ClientId = GoogleClientSecrets.Load(stream).Secrets.ClientId

            Dim stream1 = New FileStream("credentials.json", FileMode.Open, FileAccess.Read)
            ClientSecret = GoogleClientSecrets.Load(stream1).Secrets.ClientSecret

            Dim cs = New ClientSecrets()
            cs.ClientId = ClientId
            cs.ClientSecret = ClientSecret

            UserCredentials = GoogleWebAuthorizationBroker.AuthorizeAsync(cs, Scopes1, Environment.UserName,
                                                                         CancellationToken.None, New FileDataStore("MyAppsToken", True)).Result


            Dim service = New DriveService(New BaseClientService.Initializer() With {
   .HttpClientInitializer = UserCredentials,
   .ApplicationName = ApplicationName})

            Return service


        Catch ex As Exception
            Throw ex
        End Try
    End Function

Public Function AcceptAllcertification(ByVal sender As Object, ByVal Certification As X509Certificate, ByVal Chain As X509Chain, ByVal sslPolicyErrors As System.Net.Security.SslPolicyErrors) As Boolean
        Return True
    End Function

Answer 1

很难确定必须在中使用哪个请求。net从json文件。我成功地在现有电子表格中添加了表格。在这里，我把vb。net代码（如果需要）

Public Function Addsheet(ByVal FileId As String, ByVal sheetName As String) As String
        Try

            Dim serv = GetGoogleAPPSheetService() 

            Dim updatesheet = New BatchUpdateSpreadsheetRequest
            Dim Req_Request As Request = New Request
            Dim Req_AddSheet As New AddSheetRequest
            Dim prop As New SheetProperties

            prop.Title = sheetName

            Req_AddSheet.Properties = prop

            Req_Request.AddSheet = Req_AddSheet

            Dim IList_req As IList(Of Request)

            Dim List_req1 = New List(Of Request)

            List_req1.Add(Req_Request)

            IList_req = List_req1.ToList
            updatesheet.Requests = IList_req
            Dim req = serv.Spreadsheets.BatchUpdate(updatesheet, FileId)
            Dim response = req.Execute

        Catch ex As Exception
            Throw ex
        End Try

    End Function

Private Function GetGoogleAPPSheetService() As SheetsService
        Try
            Dim UserCredentials As UserCredential

            Using stream = New FileStream("credentials.json", FileMode.Open, FileAccess.Read)
                Dim credPath As String = "token.json" 'System.Environment.GetFolderPath(Environment.SpecialFolder.Personal)
                'credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json")
                UserCredentials = 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 = UserCredentials,
   .ApplicationName = ApplicationName})

            Return service

        Catch ex As Exception
            Throw ex
        End Try
    End Function

Answer 2

为此，您需要使用Sheets API。查看AddSheetRequest