Rob Kraft's Software Development Blog

Software Development Insights

How to Write Multiple Rows To A Google Sheet From C# .Net

Posted by robkraft on March 26, 2020

Google provides good documentation to help you get started reading from and writing to Google sheets using their .Net SDK here: https://developers.google.com/sheets/api/guides/values

But I didn’t find an example there or elsewhere about how to add or update multiple rows in a single API call to Google Sheets.  I figured out how to do it and am sharing here.

private static void InsertToSheet(UserCredential credential, List people)
{
	SheetsService service = new SheetsService(new BaseClientService.Initializer()
	{
		HttpClientInitializer = credential,
		ApplicationName = ApplicationName,
	});
	string sheet = "People";
	String spreadsheetID = "1sRHWW2fynHZoQl__yourspreadsheetid__wwxfDuMUM";
	var range = $"{sheet}!A2:C51";
	var valueRange = new ValueRange();
	valueRange.Values = new List<IList>();
foreach (var person in people)
	{
		var objectListthis = new List() { person.FirstName, person.LastName, person.BirthDate };
		valueRange.Values.Add(objectListthis);
	}
	var appendRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetID, range);
	appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
	var appendResponse = appendRequest.Execute();
}
public class Person
{
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public string BirthDate { get; set; }
}

The trick is to define valueRange.Values as a List, then add each or your rows to that list one at a time as shown.  When specifying the range I started with row 2 (A2:C51) because my first row has headers, and I go through row 51 because I am always inserting exactly 50 rows of data.  Also notice that in my example I am overwriting rows doing an “Update” instead of adding new rows via an “Append”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: