CODEWITHSUNDEEP
c#google-sheetsgoogle-sheets-api
Paolo
Paolo

Reputation: 1

Send data google sheets with c#

Read the data from the google sheet, it works fine but I don't get to send data to said sheet

HttpClient client = new HttpClient();
string url = "https://docs.google.com/.....";
var response = await client.GetAsync(string.Format(url));
string result = await response.Content.ReadAsStringAsync();
string cadena = stringBetween(result, "\n\n", "\"");
cadena = Regex.Replace(cadena, @"\n", ",");
string[] words = cadena.Split(',');
int x = 4;
List<User> listOfUsers = new List<User>();
for(x=4;x<28;x=x+4)
{
   listOfUsers.Add(new User() { Nombre = words[x], Correo = words[x + 1], Telefono = words[x + 2], Comentario = words[x + 3] });
};

Upvotes: 0

Views: 3235

Answers (1)

Whit Wu
Whit Wu

Reputation: 51

Your code is sending data to listOfUsers, which is a collection not tied to the spreadsheet in any way. Here is a good article from C-Sharp Corner showcasing how to create/update a Google Sheets document:

https://www.c-sharpcorner.com/article/create-and-update-google-spreadsheet-via-google-api-net-library/

I would encourage you to read the full article so you can understand what nuget packages you need to communicate with Google sheets. With that said, the most relevant part of it is towards the bottom, when the author writes a method to update an existing sheet:

private static void UpdatGoogleSheetinBatch(IList<IList<Object>> values, string spreadsheetId, string newRange, SheetsService service)
{
    SpreadsheetsResource.ValuesResource.AppendRequest request =
        service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange);

    request.InsertDataOption = 
        SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;

    request.ValueInputOption = 
        SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;

    var response = request.Execute();
}

Notice how the method is taking a list of lists that contain values as an argument. It is then appended to the spreadsheet in question, and the insert option is then configured to add the new data as rows. The ValueInputOption is then set as RAW, meaning all values will be inserted without being parsed, and then the sheet is finally updated on the last line.

You will want to take note of how the author is generating their values, as they have a List containing as list of objects, whereas you have a list of users.

private static IList<IList<Object>> GenerateData()
{
    List<IList<Object>> objNewRecords = new List<IList<Object>>();
    int maxrows = 5;
    for (var i = 1; i <= maxrows; i++)
    {
        IList<Object> obj = new List<Object>();
        obj.Add("Data row value - " + i + "A");
        obj.Add("Data row value - " + i + "B");
        obj.Add("Data row value - " + i + "C");
        objNewRecords.Add(obj);
    }
    return objNewRecords;
}

For what you are trying to do, I would modify it to do something like this

private static IList<IList<Object>> GenerateData(string[] words)
{
    List<IList<Object>> objNewRecords = new List<IList<Object>>();

    for (int x = 4; x < 28; x =x + 4)
    {
        IList<Object> obj = new List<Object>();
        //nombre
        obj.Add(words[x]);

        // Correo
        obj.Add(words[x+1]);

        // Telefono
        obj.Add(words[x+2]);

        // Comentario
        obj.Add(words[x+3]);

        objNewRecords.Add(obj);
    };

    return objNewRecords;
}

Upvotes: 2

Related Questions