How to add a sheet in google sheets API v4 in C#?

问题:

I've been using Google Sheets API, and following The Google Guide. However there is no example, even beyond the second page of google, to add a worksheet and write to a new sheet in .NET. There is plenty for js, but I have no idea on how to 1) add a sheet or 2) write to a new sheet.

How can I do this? Right now I'm able to read like in the example with out any problems, and I've only found one other reference to v4 C#. I tried going back to v3, but all documents strongly suggest using v4.

Has anyone been able to do this? Here's all I've been able to do so far:

        // Create Google Sheets API service.
        var service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = ApplicationName,
        });

        // Define request parameters.
        // Add new Sheet
        string sheetName = string.Format("{0} {1}", DateTime.Now.Month, DateTime.Now.Day);
        AddSheetRequest addSheetRequest = new AddSheetRequest();
        addSheetRequest.Properties.Title = sheetName;

        // How do I tell this to update??

回答1:

To save someone in the future a headache to end all headaches. I figured out how to add a sheet after hours of trial and error. Still working on how to update values.

Here's how I did it:

        // Create Google Sheets API service.
        var service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = ApplicationName,
        });

        // Add new Sheet
        string sheetName = string.Format("{0} {1}", DateTime.Now.Month, DateTime.Now.Day);
        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();

回答2:

I have the code to add a sheet as well, but it seems like you figured it out. Here is some code to add content to the sheet. It differs from what others have posted by a little bit, but it should help whoever sees this post.

In this code, it would insert the values of colNames into the row starting at (0,0). So if sheet uses this notation (row, col), then
(0,0)=timestamp
(0,1)=videoid
(0,2)=videoname
(0,3)=firstname
(0,4)=lastname
(0,5)=email

So the whole first row would be filled with the colNames values

        var reqs = new BatchUpdateSpreadsheetRequest();
        reqs.Requests = new List<Request>();
        string[] colNames = new [] { "timestamp", "videoid", "videoname", "firstname", "lastname", "email" };

        // Create starting coordinate where data would be written to

        GridCoordinate gc = new GridCoordinate();
        gc.ColumnIndex = 0;
        gc.RowIndex = 0;
        gc.SheetId = SHEETID; // Your specific sheet ID here

        rq = new Request();
        rq.UpdateCells = new UpdateCellsRequest();
        rq.UpdateCells.Start = gc;
        rq.UpdateCells.Fields = "*"; // needed by API, throws error if null

        // Assigning data to cells
        RowData rd = new RowData();
        List<CellData> lcd = new List<CellData>();
        foreach (String column in colNames)
        {
            ExtendedValue ev = new ExtendedValue();
            ev.StringValue = column;

            CellData cd = new CellData();
            cd.UserEnteredValue = ev;
            lcd.Add(cd);
        }
        rd.Values = lcd;

        // Put cell data into a row
        List<RowData> lrd = new List<RowData>();
        lrd.Add(rd);
        rq.UpdateCells.Rows = lrd;

        // It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet
        reqs.Requests.Add(rq);

        // Execute request
        response = sheetsService.Spreadsheets.BatchUpdate(reqs, Spreadsheet.SpreadsheetId).Execute(); // Replace Spreadsheet.SpreadsheetId with your recently created spreadsheet ID

回答3:

I was looking for the Java version of this and I managed to get a working version based on sparky's answer. This should work:

            //Set sheet name
            //Can be any string, I chose to set it to the account name
            String sheetName = mCredential.getSelectedAccountName();

            //Create a new AddSheetRequest
            AddSheetRequest addSheetRequest = new AddSheetRequest();
            SheetProperties sheetProperties = new SheetProperties();

            //Add the sheetName to the sheetProperties
            addSheetRequest.setProperties(sheetProperties);
            addSheetRequest.setProperties(sheetProperties.setTitle(sheetName));

            //Create batchUpdateSpreadsheetRequest
            BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();

            //Create requestList and set it on the batchUpdateSpreadsheetRequest
            List<Request> requestsList = new ArrayList<Request>();
            batchUpdateSpreadsheetRequest.setRequests(requestsList);

            //Create a new request with containing the addSheetRequest and add it to the requestList
            Request request = new Request();
            request.setAddSheet(addSheetRequest);
            requestsList.add(request);

            //Add the requestList to the batchUpdateSpreadsheetRequest
            batchUpdateSpreadsheetRequest.setRequests(requestsList);

            //Call the sheets API to execute the batchUpdate
            mService.spreadsheets().batchUpdate(spreadsheetId,batchUpdateSpreadsheetRequest).execute();

回答4:

FYI, while the majority of the documentation is just using the raw JSON, it should map 1:1 to the constructs in each language.

For example, to update values you'd likely do something like:

var valueRange = new ValueRange(); valueRange.values = { { 1, 2, 3 }, { 4, 5, 6 } }; var range = "A1"; var update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range); var result = update.execute();

The above is pseudo-code based on your code example and the reference docs @ https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/update.

回答5:

Further extending Sam's answer. The following gets me to update a cell.

            ValueRange VRx = new ValueRange();
            IList<IList<object>> xx = new List<IList<object>>();
            xx.Add(new List<object> { "test" });
            VRx.Values = xx;
            SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(VRx, spreadsheetId, "back!A19");
            update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
            UpdateValuesResponse result = update.Execute(); 

Hope this helps!