How to Read data from Google Sheet in C# using HttpRequest without Speadsheet API

How to Read data from Google Sheet in C# using HttpRequest without Speadsheet API

Open the Google Sheet you want to read, then click on File > Publish to the web.

In the dialog that appears, choose the entire document or specific sheets if necessary.

Make sure the format is set to CSV if you want to handle data in CSV format. If you prefer JSON, you can select that option as well.

Click Publish and copy the generated link.

Open your Visual Studio, then create a new Windows Forms Application project.

Next, Design a simple form as shown below.

If you want to read data from html you should install HtmlAgilityPack from nuget.

HtmlAgilityPack is a popular .NET library used for parsing HTML and XML documents. It allows you to navigate and manipulate HTML content in a way similar to working with an HTML DOM.

or If you need to parse the CSV data, you can use a library like CSVHelper to make it easier to work with CSV files.

You will use HttpClient to send a request to the published URL and read the data.

async Task<string> GetContentAsync(string url)
{
    using (var httpClient = new HttpClient())
    {
        // Send HTTP request to the URL
        return await httpClient.GetStringAsync(url);
    }
}

then create a method convert html to datatable.

How you can use HtmlAgilityPack in C# to parse and extract data from HTML content, such as the HTML of a published Google Sheet.

async Task<DataTable> GetGoogleSheetAsDataTableAsync(string sheetUrl)
{
    DataTable dt = new DataTable();
    string htmlContent = await GetContentAsync(sheetUrl);
    var htmlDoc = new HtmlAgilityPack.HtmlDocument();
    htmlDoc.LoadHtml(htmlContent);
    var table = htmlDoc.DocumentNode.SelectSingleNode("//table");
    if (table == null)
        return dt;
    var headerRow = table.SelectSingleNode(".//thead/tr");
    if (headerRow != null)
    {
        foreach (var th in headerRow.SelectNodes("th"))
        {
            dt.Columns.Add(WebUtility.HtmlDecode(th.InnerText.Trim()));
        }
    }
    else
    {
        var firstRow = table.SelectSingleNode(".//tr");
        if (firstRow != null)
        {
            foreach (var td in firstRow.SelectNodes("td"))
            {
                dt.Columns.Add(td.InnerText.Trim());
            }
        }
    }
    var rows = table.SelectNodes(".//tr");
    foreach (var row in rows)
    {
        var dr = dt.NewRow();
        var cells = row.SelectNodes("td");
        if (cells != null)
        {
            for (int i = 0; i < cells.Count && i < dt.Columns.Count; i++)
            {
                dr[i] = WebUtility.HtmlDecode(cells[i].InnerText.Trim());
            }
            dt.Rows.Add(dr);
        }
    }
    return dt;
}

HtmlAgilityPack is a powerful tool for scraping and processing HTML content in C#. By combining it with HttpClient, you can fetch and analyze web pages programmatically.

Handle button click as shown below.

private async void button1_Click(object sender, EventArgs e)
{
    var url = @"https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/gviz/tq?tqx=out:html&tq&gid=1";
    var dataTable = await GetGoogleSheetAsDataTableAsync(url);
    dataGridView1.DataSource = dataTable;
}

If you prefer csv format, you can create a method read csv file as shown below.

async Task<DataTable> GetCsvAsDataTableAsync(string sheetUrl)
{
    DataTable dt = new DataTable();
    string csvContent = await GetContentAsync(sheetUrl);
    // Use CsvHelper to parse the CSV data
    using (var reader = new StreamReader(csvContent))
    using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)))
    {
        var records = csv.GetRecords<dynamic>();
        foreach (var record in records)
        {
            //add record to datatable
        }
    }
    return dt;
}

Ensure that the Google Sheet is set to be viewable by anyone with the link.

This approach bypasses the need for the Google Sheets API and is useful for quick access to data from publicly available sheets.