.Net How to create a Google Spreadsheet in Google Drive?

user_1377587 0
Viewed 265

Completely new to Google SpreadSheet API (v4). I am using .Net (5.0) to create a new spreadsheet, which I assumed would be saved in my Google Drive / account.

Cobbled together the following from what examples / docs I could find, but I do not understand where the spreadsheet is being saved (stored) - as I cannot locate it within my account.

Below is the code I am using -- which executes without error and returns a spreadsheet ID and URL -- but the spreadsheet is nowhere to be found in my Google account !!

     String sheet_name;

     Google.Apis.Sheets.v4.SpreadsheetsResource.CreateRequest create_request;

     Google.Apis.Sheets.v4.Data.Spreadsheet spreadsheet_body;
     Google.Apis.Sheets.v4.Data.Spreadsheet spreadsheet;

     Google.Apis.Sheets.v4.Data.Sheet sheet;


     // Create spreadsheet object and set properties ...

     spreadsheet_body = new Google.Apis.Sheets.v4.Data.Spreadsheet();

     spreadsheet_body.Properties = new Google.Apis.Sheets.v4.Data.SpreadsheetProperties();

     spreadsheet_body.Properties.Title = "TEST1";


     // Create list of sheets to include in this spreadsheet ...

     spreadsheet_body.Sheets = new List<Google.Apis.Sheets.v4.Data.Sheet>();


     // Create a sheet object and set properties ...

     sheet_name = "Sheet 1";

     sheet = new Google.Apis.Sheets.v4.Data.Sheet();

     sheet.Properties = new Google.Apis.Sheets.v4.Data.SheetProperties();

     sheet.Properties.Title = sheet_name;

     sheet.Properties.SheetId = 1;

     sheet.Properties.SheetType = "GRID";


     // Add sheet to this spreadsheet ...

     spreadsheet_body.Sheets.Add( sheet );


     // Try to create the spreadsheet ...

     try
     {

        create_request = sheet_service.Spreadsheets.Create( spreadsheet_body );

        spreadsheet = create_request.Execute();

        // Spreadsheet ID returned -- but where is the spreadsheet saved??? ...

        Console.WriteLine( $"spreadsheet.SpreadsheetId: {spreadsheet.SpreadsheetId}" );

        Console.WriteLine( $"spreadsheet.SpreadsheetUrl: {spreadsheet.SpreadsheetUrl}" );

     }
     catch ( Exception ex )
     {

        Console.WriteLine( ex.ToString() );

     }

  }

When I cut and paste the returned URL into a browser -- Google displays the following:

   You need access
   Ask for access, or switch to an account with access.

... with a button entitled "Request Access" -- when clicked on, the system displays:

   Request sent
   You'll get an email letting you know if the file is shared with you

... but I never receive any email

I am using a service account / credentials when making the requests to the API, could this be the problem?

If anyone has a working example of creating a Google spreadsheet within their own Google account via the API, please share.

Any help / insights would be very much appreciated.

c#-5.0 google-sheets-api .net
1 Answers

From I am using a service account / credentials when making the requests to the API, could this be the problem?, I thought that your issue might be due to this.

When new Spreadsheet is created with the method of "Method: spreadsheets.create" in Sheets API, the Spreadsheet is created to the root folder of Google drive of the service account. The Google Drive of service account is different from your Google Drive. By this, you cannot directly access to the Spreadsheet created by the service account using your browser. I think that this is the reason of your issue.

When you want to open the created Spreadsheet using your browser, how about the following patterns?

Pattern 1:

After the Spreadsheet was created with the service account, it shares the created Spreadsheet with your Google account.

In order to share the Spreadsheet with your Google account, you can achieve this using the method of "Permissions: create" in Drive API.

Pattern 2:

At first, create a folder in your Google Drive, and shares the created folder with the email of service account as the writer, and then, create new Spreadsheet to the folder.

In this case, I think that you can use the following 2 methods.

  1. Directly create new Spreadsheet to the specific folder using the method of "Files: create" in Drive API.

  2. Create new Spreadsheet using the method of "Method: spreadsheets.create" in Sheets API, and move the Spreadsheet to the specific folder (your shared folder) using the method of "Files: update" in Drive API.

Pattern 3:

Create new Spreadsheet using OAuth2 instead of the service account.

About the authorization process and sample script for OAuth2, you can see ".NET quickstart" of the official document. Ref About the request to API, you can use your script.

Pattern 4:

If you want to achieve your goal by a simple script, for example, how about using Web Apps as the wrapper API? In this case, the flow is as follows.

  1. Create Web Apps.
    • In this case, Google Apps Script is used. By this, Spreadsheet is created to your Google Drive.
  2. When you use Web Apps, you can access to the Web Apps by HTML request.
  3. At Web Apps side, when it is accessed from the client side, new Spreadsheet is created using Google Apps Script.

In this case, you can also achieve without using the access token. Of course, you can access to Web Apps with the access token. Ref1, Ref2

References:

user_71086530
Related