How do you upload a csv file to google sheets using the google sheets api with c#?

Question

I have a method that pulls a csv from a website using restsharp.

 class ScheduledBilling
{

    public string Report()
    {
        var client = new RestClient("http://example.com");
        client.CookieContainer = new System.Net.CookieContainer();
        client.Authenticator = new SimpleAuthenticator("username", "xxx", "password", "xxx");
        var request = new RestRequest("/login", Method.POST);


        IRestResponse response = client.Execute(request);


        var ScheduledBilling = client.DownloadData(new RestRequest("/file));
        var csv = System.Text.Encoding.Default.GetString(ScheduledBilling);

        return(csv);

    }

}

In main, I've been using a mix of tutorials and the quickstart so that I can enter information into a google sheet.

//ScheduledCRMBilling.Report();
            ScheduledCRMBilling obj = new ScheduledCRMBilling();
            string csv = obj.Report();

        String spreadsheetId2 = "xxx";
        String range1 = "Scheduled Billing";

        ValueRange valueRange1 = new ValueRange();   

        valueRange1.MajorDimension = "ROWS";//"ROWS";//COLUMNS

        var oblist1 = new List<object>() { csv };

        SpreadsheetsResource.ValuesResource.UpdateRequest update1 = service.Spreadsheets.Values.Update(valueRange1, spreadsheetId2, range1);

        update1.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;

        UpdateValuesResponse result1 = update1.Execute();

        Console.WriteLine("done!");

I've set the range to be the entire sheet (Scheduled Billing). What happens is that the first cell is filled with all the information in the csv, and not as if you would import a csv into google sheets. How should I proceed? I feel as if I should pass the csv variable as a list of strings, which would place a string in each cell. But then I don't know how it would know when it's time for a new line.

Answer 1

As mentioned in the answer I linked on the OPs question the Sheets API does not provide functionality to upload a sheet, only to create one from scratch.

Had to end up using the Drive API as this allows for uploading of files. Especially relevant is this section about uploading google docs types as they will get converted to the appropriate type. So your CSV file will be turned into a Google Sheet.

If your spreadsheet is in memory aka stored in a variable, you can replace this line:

using (var stream = new System.IO.FileStream("files/report.csv",
                    System.IO.FileMode.Open))

with the following:

using (var stream = new System.IO.MemoryStream(Encoding.ASCII.GetBytes(csv)))

Before call the upload functionality you will have to create the credentials for your application using this guide, you will also have to change the scope to Drive. Below is a small application that I put together to demonstrate the whole upload flow - assuming you have created the OAuth token for the Drive API and downloaded the secret into your project.

using Google.Apis.Auth.OAuth2;
using Google.Apis.Drive.v3;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.IO;
using System.Text;
using System.Threading;

namespace ConsoleApp3
{
    internal class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        private static string[] Scopes = { DriveService.Scope.Drive };

        private static string ApplicationName = "CsvTest";

        private static void Main(string[] args)
        {
            UserCredential credential;

            using (var stream =
                new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                string credPath = System.Environment.GetFolderPath(
                    System.Environment.SpecialFolder.Personal);
                credPath = Path.Combine(credPath, ".credentials/drive.googleapis.com1.json");

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

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

            var fileMetadata = new Google.Apis.Drive.v3.Data.File()
            {
                Name = "My Report",
                MimeType = "application/vnd.google-apps.spreadsheet"
            };

            var csv = "Heading1,Heading2,Heading3\r\nEntry1,Entry2,Entry3";

            FilesResource.CreateMediaUpload request;
            using (var stream = new System.IO.MemoryStream(Encoding.ASCII.GetBytes(csv)))
            {
                request = service.Files.Create(
                    fileMetadata, stream, "text/csv");
                request.Fields = "id";
                request.Upload();
            }
            var file = request.ResponseBody;

            Console.WriteLine("File ID: " + file.Id);

            Console.Read();
        }
    }
}

EDIT

You can change Name and Application to whatever you like they're just friendly names.

EDIT

Happy coding.