Batch insert into Google worksheet with c# and a service account with cert

program.cs

using Google.Apis.Auth.OAuth2;
using Google.Apis.Plus.v1;
using Google.Apis.Plus.v1.Data;
using Google.Apis.Services;
using Google.GData.Client;
using Google.GData.Spreadsheets;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;

namespace Google.Apis.Samples.PlusServiceAccount
{
    public class Program
    {
        private static String ACTIVITY_ID = "z12gtjhq3qn2xxl2o224exwiqruvtda0i";

        public static void Main(string[] args)
        {
            String serviceAccountEmail = "use your service acct email";
//download your p12 file from google dev console, add it to your project as a content
            var certificate = new X509Certificate2(@"*.p12", "notasecret", X509KeyStorageFlags.Exportable);

            ServiceAccountCredential credential = new ServiceAccountCredential(
               new ServiceAccountCredential.Initializer(serviceAccountEmail)
               {
                   Scopes = new[] { "https://spreadsheets.google.com/feeds", "https://docs.google.com/feeds" }
               }.FromCertificate(certificate));

            credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None).Wait();

            SpreadsheetsService service = new SpreadsheetsService("ss3");
            var requestFactory = new GDataRequestFactory("ss3");
            requestFactory.CustomHeaders.Add(string.Format("Authorization: Bearer {0}", credential.Token.AccessToken));

            service.RequestFactory = requestFactory;

            string worksheetName = "4/25/2016";

            SpreadsheetQuery query = new SpreadsheetQuery();

            query.Title = "TargetBPD Ingestion Log";
            // query spreadsheets.
            SpreadsheetFeed feed = service.Query(query);
            if (feed.Entries.Count == 0) return;
            //assume only one for now
            SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];

            WorksheetFeed worksheets = spreadsheet.Worksheets;

            bool addWorksheet = true;
            WorksheetEntry worksheet = new WorksheetEntry();

            worksheet = (WorksheetEntry)(from x in worksheets.Entries where x.Title.Text == worksheetName select x).FirstOrDefault();
            if (worksheet != null)
            {
                //we could always delete and recreate
                //entry.Delete();

                addWorksheet = false;
            }

            if (addWorksheet)
            {
                WorksheetEntry newWorksheet = new WorksheetEntry();
                newWorksheet.Title.Text = worksheetName;
                newWorksheet.Cols = 10;
                newWorksheet.Rows = 1200;
                //add worksheet
                service.Insert(worksheets, newWorksheet);

                //refresh the sheet
                feed = service.Query(query);

                if (feed.Entries.Count == 0) return;

                spreadsheet = (SpreadsheetEntry)feed.Entries[0];
                worksheets = spreadsheet.Worksheets;
                worksheet = (WorksheetEntry)(from x in worksheets.Entries where x.Title.Text == worksheetName select x).FirstOrDefault();
            }

            //AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
            //// Fetch the list feed of the worksheet.
            //ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
            //ListFeed listFeed = service.Query(listQuery);
            //ListEntry row = new ListEntry();
            //row.Elements.Add(new ListEntry.Custom() { LocalName = "firstname", Value = "Joe" });
            ////row.Elements.Add(new ListEntry.Custom() { LocalName = "lastname", Value = "Smith" });
            ////row.Elements.Add(new ListEntry.Custom() { LocalName = "age", Value = "26" });
            ////row.Elements.Add(new ListEntry.Custom() { LocalName = "height", Value = "176" });
            //// Send the new row to the API for insertion.
            //service.Insert(listFeed, row);

            AtomLink cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);

            // Fetch the list feed of the worksheet.
            CellQuery cellQuery = new CellQuery(cellFeedLink.HRef.ToString());
            CellFeed cellFeed = service.Query(cellQuery);

            // Build list of cell addresses to be filled in
            List<CellAddress> cellAddrs = new List<CellAddress>();
            for (uint row = 1; row <= MAX_ROWS; ++row)
            {
                for (uint col = 1; col <= MAX_COLS; ++col)
                {
                    cellAddrs.Add(new CellAddress(row, col));
                }
            }

            // Prepare the update
            // GetCellEntryMap is what makes the update fast.
            Dictionary<String, CellEntry> cellEntries = GetCellEntryMap(service, cellFeed, cellAddrs);

            CellFeed batchRequest = new CellFeed(cellQuery.Uri, service);
            foreach (CellAddress cellAddr in cellAddrs)
            {
                CellEntry batchEntry = cellEntries[cellAddr.IdString];
                batchEntry.InputValue = cellAddr.IdString;
                batchEntry.BatchData = new GDataBatchEntryData(cellAddr.IdString, GDataBatchOperationType.update);
                batchRequest.Entries.Add(batchEntry);
            }

            // Submit the update
            CellFeed batchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));

            // Check the results
            bool isSuccess = true;
            foreach (CellEntry entry in batchResponse.Entries)
            {
                string batchId = entry.BatchData.Id;
                if (entry.BatchData.Status.Code != 200)
                {
                    isSuccess = false;
                    GDataBatchStatus status = entry.BatchData.Status;
                    Console.WriteLine("{0} failed ({1})", batchId, status.Reason);
                }
            }

            Console.WriteLine(isSuccess ? "Batch operations successful." : "Batch operations failed");
        }

        private const int MAX_ROWS = 75;

        // The number of columns to fill in the destination workbook
        private const int MAX_COLS = 5;

        /**
         * A basic struct to store cell row/column information and the associated RnCn
         * identifier.
         */

        private class CellAddress
        {
            public uint Row;
            public uint Col;
            public string IdString;

            /**
             * Constructs a CellAddress representing the specified {@code row} and
             * {@code col}. The IdString will be set in 'RnCn' notation.
             */

            public CellAddress(uint row, uint col)
            {
                this.Row = row;
                this.Col = col;
                this.IdString = string.Format("R{0}C{1}", row, col);
            }
        }

        /**
       * Connects to the specified {@link SpreadsheetsService} and uses a batch
       * request to retrieve a {@link CellEntry} for each cell enumerated in {@code
       * cellAddrs}. Each cell entry is placed into a map keyed by its RnCn
       * identifier.
       *
       * @param service the spreadsheet service to use.
       * @param cellFeed the cell feed to use.
       * @param cellAddrs list of cell addresses to be retrieved.
       * @return a dictionary consisting of one {@link CellEntry} for each address in {@code
       *         cellAddrs}
       */

        private static Dictionary<String, CellEntry> GetCellEntryMap(
            SpreadsheetsService service, CellFeed cellFeed, List<CellAddress> cellAddrs)
        {
            CellFeed batchRequest = new CellFeed(new Uri(cellFeed.Self), service);
            foreach (CellAddress cellId in cellAddrs)
            {
                CellEntry batchEntry = new CellEntry(cellId.Row, cellId.Col, cellId.IdString);
                batchEntry.Id = new AtomId(string.Format("{0}/{1}", cellFeed.Self, cellId.IdString));
                batchEntry.BatchData = new GDataBatchEntryData(cellId.IdString, GDataBatchOperationType.query);
                batchRequest.Entries.Add(batchEntry);
            }

            CellFeed queryBatchResponse = (CellFeed)service.Batch(batchRequest, new Uri(cellFeed.Batch));

            Dictionary<String, CellEntry> cellEntryMap = new Dictionary<String, CellEntry>();
            foreach (CellEntry entry in queryBatchResponse.Entries)
            {
                cellEntryMap.Add(entry.BatchData.Id, entry);
                Console.WriteLine("batch {0} (CellEntry: id={1} editLink={2} inputValue={3})",
                    entry.BatchData.Id, entry.Id, entry.EditUri,
                    entry.InputValue);
            }

            return cellEntryMap;
        }
    }
}