0 Comments

Our goal was to append a row to an existing Google Spreadsheet. That spreadsheet is not publicly shared.

The standard scenario seems to be that a user who has access rights interacts with your program and authorizes it to make changes to the spreadsheet. That is not what we needed. In our case an arbitrary user interacts with the website and the server then sends some data to the spreadsheet. The spreadsheet owner is the portal owner, not the interacting user.

There is an easy way to accomplish that: via OAuth for Service Accounts. There is also a Google API client library in .NET for simple communication.

Here is an overview over the steps:

  1. Go to Google APIs and create an API project. Enable Sheets API.
  2. Create Credentials: create Service account key (AppEngine Default Service Account) and save the JSON file with the credentials in your project
  3. Share spreadsheet with Google-API-User, that is the client_email in the downloaded credential JSON file.
  4. Write your code and use the downloaded credentials:

Here is our example code:

using System;
using System.Collections.Generic;
using System.IO;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Services;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;

namespace SheetApiTest
{
    public class AppendWithGoogleCredentials
    {
        static string[] Scopes = { SheetsService.Scope.Spreadsheets };
        static string ApplicationName = "Google Sheets API .NET Quickstart";
        
        public void AppendData()
        {
            // downloaded json file with private key
            var credential = GoogleCredential.FromStream(new FileStream("sheets-test.json", FileMode.Open)).CreateScoped(Scopes);
        
            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });
        
            var spreadsheetId = "11AwV7d1pEPq4x-rx9WeZHNwGJa0ehrCSKyWyfRhh760";
            // data to append - must be a value range:
            var valueRange = new ValueRange { Values = new List<IList<object>> { new List<object>() } };
            
            // add data for each column
            valueRange.Values[0].Add(DateTime.Now.ToLongTimeString());
            valueRange.Values[0].Add("b");
            valueRange.Values[0].Add("c");
            
            // in append request, the range only requires the name of the table
            var rangeToWrite = "Sheet1";
            // append request: into first free line
            var appendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, rangeToWrite);
            appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
            var appendReponse = appendRequest.Execute();
        }
    }
}

Have success moving your data!

Anton.