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:
• .NET Sheets API Documentation
• Google Sheets API Guides (quickstarts, usage)
• install the Sheets API package: ```PM> Install-Package Google.Apis.Sheets.v4```

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.

Das Event

Die Spartakiade fand zum 5 Mal statt (ich war zum vierten Mal dabei, glaube ich). Sie wurde toll organisiert: Es gab genug und gut zu essen und trinken, das WLAN funktioniert einwandfrei – damit waren die Grundbedürfnisse von Entwicklern gedeckt :). Wir waren wieder bei Immobilien Scout untergebracht. Die Spartakiade war schnell ausgebucht und sehr gut besucht. Die Stimmung war engagiert, die Leute waren gut drauf. Es macht Spaß an dem Event teilzunehmen.

Der Workshop

Ich habe mir Asynchrone Programmierung bei Daniel Marbach ausgesucht, da ich in diesem Thema ein Novize bin und es an vielen Stellen Einzug hält, auch (oder insbesondere) bei Webanwendungen. Daniel argumentierte, dass asynchroner Code in Zukunft überall Einzug halten wird, fast jede Library wird mittlerweile asynchron geschrieben. Hier findet man den Quellcode zum Workshop: https://github.com/danielmarbach/async-dolls

In .NET arbeitet man mit der TPL (Task Parallel Library). Die Bausteine - die Tasks - werden durch einen Task Scheduler koordiniert. Ein Task kann unterschiedliche Operationen representieren: IO-bound oder CPU-bound operations. CPU-bound Tasks blockieren worker (je nach CPU-Chip hat man davon mehr oder weniger), während IO-bound Tasks concurrent (nebenläufig) arbeiten können. Wir haben uns im Workshop fast nur mit IO-bound Tasks auseinandergesetzt. In C#  werden die Keywords async und await als syntaktischer Zucker bereitgestellt, damit man automagisch mit Zustandsmaschinen arbeiten kann.

Hier ein paar lose weitere Erkenntnisse / Best Practices:

• IO-bound kann Netzwerkkommunikation sein (z.B. TCP), Datenbankabfragen, Filesystem-Anfragen (wobei File-Zugriffe in .NET fast alle synchron programmiert sind).
• sequential: await tasks, concurrent: await Task.WhenAll(tasks)
• Nie async void verwenden, sondern immer Task zurückgeben (sonst bekommt man die Exceptions des inneren Codes nicht zu Gesicht).
• In Backend und Libraries ConfigureAwait(false) verwenden, damit der Context nicht zwischengespeichert werden muss.
• Wenn man synchrone APIs in asynchronem Codepfad aufruft danach leeren Task erstellen:
• return Task.FromResult(0) oder return Task.CompletedTask() (ab .NET Framework 4.6)
• Task.Run().GetAwaiter().GetResult() entpackt Exceptions (sonst sind sie gewrappt in AggregateException)
• besser Task.Run nutzen statt Task.Factory.StartNew, da es dann keines Unwraps bedarf
• Semaphore (Limitierung der concurrency): in .NET SemaphoreSlim verwenden (asynchron implementiert)
• async / await ist nicht threadsicher (könnte von einem Thread angefangen werden und von anderem beendet werden)
• Daher nicht ThreadLocal verwenden, sondern AsyncLocal, falls Zustand gespeichert werden muss.
• Im IoC nicht “per Thread” nutzen.
• Man kann Fire and Forget schreiben, wenn man den Task nicht awaiten möchte: Fire().Ignore() wobei Ignore() einfach eine leere Task-Methode ist.

Wir programmierten in dem zweitägigen Workshop eine Message-Pump it einer Pipeline. Nach und nach implementierten wir die Einzelteile und gelangten zu tieferem Verständnis der asynchronen Programmierung. Hier findet man den Quellcode zum Workshop: https://github.com/danielmarbach/async-dolls. Es war ein fordernder Workshop, aber ich habe viele Erkentnisse gewonnen, die ich hoffetnlich lange genug im Kopf behalte, bis ich das nächste Mal mit asynchronen Methoden zu tun habe.

We had the problem that Microsoft servers rejected emails from our hMailServer. We got the following replies when we send a mail to @outlook.com or @live.com:

Remote server replied: 550 SC-001 (COL004-MC6F13) Unfortunately, messages from <our IP> weren't sent. Please contact your Internet service provider since part of their network is on our block list. You can also refer your provider to http://mail.live.com/mail/troubleshooting.aspx#errors.

After looking for the error code and possible causes, it seems like spam has been sent via our IP, but none that we know of. It could be that it did not originate from our server, but from a server with an IP address starting with the same digits as ours (a whole range belongs to our server hosting provider).

How did I fix it:

1. Checked if other services think that spam is being sent via our IP:
   1. https://www.spamhaus.org/lookup/
   2. http://mxtoolbox.com/blacklists.aspx
   3. https://www.senderscore.org/lookup.php?lookup=0
2. Contact Outlook.com to let emails from our server through: contact form. After replying to their mail, our emails were not rejected anymore after a few days.
3. Monitor emails going out from our server to Microsoft/Outlook: We signed up for Smart Network Data Service. There we added our IP, signed the contract, and will now be notified of spam and junk sent from our server.

Our emails now seem to land in the junk email folder, but at least they are pass through. We will look into the problem to persuade Microsoft to not mark our emails as junk.

Sometimes we have to change the IP in the DNS records to a site. As I did not want to enter IP multiple times I researched the simplest way to define DNS records. You only have to define the IP once. The other records (for instance subdomains hosted via the same IP) you can define as CNAME records. Use wildcard DNS records to minimize the list of records you have to define. With wildcards for subdomains you can route the user to your site even if the user misspelled the subdomain – let your application handle the mistake and still answer the request properly.

That way I reduced our DNS records to two: one A record with the IP, and one CNAME wildcard record pointing to the A record. If I need to change the IP again I will only have to do that at one point.

example.com    A     192.0.2.1
*.example.com  CNAME example.com

We use TeamCity on our servers to run unit test, performance tests and deployments. By now we rely heavily on the processes which we configured in TeamCity. In case we lose a server, we would like to have backup of all the configuration of TeamCity on that server.

To create daily backups we use TeamCitys REST API. There is an endpoint for Data Backup. I found a script written by Ivan Leonenko, which encapsulates the API call in a powershell script. If you want to then create a repeated Task Scheduler task, here is a step-by-step blog post on how to do that. Since we do all our backups with SyncBack, I created a repeated runner there which executes the powershell script. After that JungleDisk takes care of copying the backups to Amazon S3, where we would find our backup copy in case the server and its data is lost.