Create a new sheet in Google Sheet c#

Question

I am able to retrieve and update values in Google Sheets using the code below:

private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (cbYards.Text == "Select Yard")
            {
                MessageBox.Show(@"Please select a yard.");
                return;
            }
            UserCredential credential;

            using (var stream =
                new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                string credPath = System.Environment.GetFolderPath(
                    System.Environment.SpecialFolder.Personal);
                credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            IList<IList<Object>> list = new List<IList<Object>>() { };
            for (var i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                var formula = "=IFERROR(VLOOKUP(B"+(i+2)+",Names!$A$2:$B,2,FALSE),\"No Record\")";
                List<object> lists = new List<object>() { formula, dataGridView1.Rows[i].Cells[0].Value.ToString(), 
                    dataGridView1.Rows[i].Cells[1].Value.ToString() };
                list.Add(lists);
            }

            var range = cbYards.Text+"!A2:C";
            ValueRange VRange = new ValueRange();
            VRange.Range = range;
            VRange.Values = list;

            //ValueRange response = request.Execute();
            ValueRange valueRange = new ValueRange();
            valueRange.MajorDimension = "COLUMNS"; 

            SpreadsheetsResource.ValuesResource.UpdateRequest upd = service.Spreadsheets.Values.Update(VRange, spreadsheetId, range);
            upd.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
            UpdateValuesResponse response = upd.Execute();
        }

My question is how can I create a new Sheet in the Google Sheet that I am currently using. I thought that all I need to do is replace the

SpreadsheetsResource.ValuesResource.UpdateRequest upd = service.Spreadsheets.Values.Update(VRange, spreadsheetId, range);

to

SpreadsheetsResource.ValuesResource.UpdateRequest upd = service.Spreadsheets.create();

but apparently it is wrong...

I can't understand how to implement the instructions in the documentation at Method: spreadsheets.create

What does the JSon have do with how I will be coding in C#? Really appreciate the help.

UPDATE I found this here but it is not complete since it is still giving me an error of

Additional information: Object reference not set to an instance of an object.

This is the updated code:

private void button1_Click(object sender, EventArgs e)
        {
            UserCredential credential;

            using (var stream =
                new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                string credPath = System.Environment.GetFolderPath(
                    System.Environment.SpecialFolder.Personal);
                credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            string sheetName = string.Format("{0} - {1}-{2}", cbYards.Text, fromDate.Value.ToShortDateString(), toDate.Value.ToShortDateString());
            var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
            myNewSheet.Properties = new SpreadsheetProperties();
            myNewSheet.Properties.Title = sheetName;
            var newSheet = service.Spreadsheets.Create(myNewSheet).Execute();
        }

score 9 · Answer 1 · edited Jul 10 '17 at 19:28

9

There is a method in the client library for create you just need to pass it the new sheet object. Don't forget to instantiate the Properties before assigning the title (line 2 below)

var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
myNewSheet.Properties = new SpreadsheetProperties();
myNewSheet.Properties.Title = "Daimtos awsom sheet";
var awsomNewSheet= service.Spreadsheets.Create(myNewSheet).Execute();

Note: I agree with you that documentation page is useless.

edited Jul 10 '17 at 19:28

Brian Webster

30,033
48
152
225

answered Jan 03 '17 at 08:40

Linda Lawton - DaImTo

106,405
32
180
449

1

I think it is still incomplete DalmTo because, although it doesn't give me any errors, it doesn't add the new sheet to the spreadsheet. Please see the edits I have made in my question. – Ibanez1408 Jan 04 '17 at 00:43
I don't think i completely understand what you are trying to do. Have you tried https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.sheets/copyTo – Linda Lawton - DaImTo Jan 04 '17 at 08:32
I _think_ that the OP is asking how to create a new sheet in an existing workbook. It looks like your solution creates a brand new workbook (not helped that both are called "sheet" in the Google domain terminology) When I run your code, `awsomNewSheet` has a brand new sheet ID – user1007074 Dec 28 '22 at 16:28
@user1007074 i think you are commenting on a five year old question. – Linda Lawton - DaImTo Dec 28 '22 at 16:46
Correct. Is there a time limit for suggestions for improvements? – user1007074 Jan 02 '23 at 14:28

score 3 · Answer 2 · answered Aug 08 '19 at 10:30

The following piece of code is working on my side:

var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = sheetName;
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List<Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request { AddSheet = addSheetRequest });

var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SpreadsheetId);
batchUpdateRequest.Execute();

score 2 · Answer 3 · answered Sep 01 '18 at 11:51

The reason why you get a null reference is because you can't have a spreadsheet without a sheet in it.

var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
myNewSheet.Properties = new SpreadsheetProperties();
myNewSheet.Properties.Title = sheetName;

var sheet = new Sheet();
sheet.Properties = new SheetProperties();
sheet.Properties.Title = "Sheet1";
myNewSheet.Properties.Sheets = new List<Sheet>() { sheet };

var newSheet = service.Spreadsheets.Create(myNewSheet).Execute();

myNewSheet.Properties.Sheets = new List() { sheet }; should be myNewSheet.Sheets = new List() { sheet }; — taylorswiftfan, Oct 24 '18 at 19:39

score 0 · Answer 4 · answered Feb 03 '17 at 10:13

Your updated solution works perfectly. But I had issues with "Request had insufficient authentication scopes" because stored credentials allowed Read only access. I had solved the problem by deleting user credentials (stored previously in /Users/yourUserName/.credentials/sheets.googleapis.com-projectName/*) and executing the application again to get new credentials

Create a new sheet in Google Sheet c#

4 Answers4