Google Sheets API v4, SocketException: An existing connection was forcibly closed by the remote host

Viewed 183

I have gone through many solutions. I also tried one solution here, but it did not work for me. So please don't mark this one as duplicate. I am working with Google Sheets API v4, and I got the following exceptions:

System.AggregateException: 'One or more errors occurred.'

Inner Exceptions:

HttpRequestException: An error occurred while sending the request.

WebException: The underlying connection was closed: An unexpected error occurred on a send.

IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

SocketException: An existing connection was forcibly closed by the remote host

when I try to get the result of HttpResponseMessage. Below are my codes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Net;
using System.Text;
using System.Net.Http;
using System.Configuration;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Net.Security;
using System.Net.Http.Headers;
using System.Security.Cryptography.X509Certificates;

namespace ConsoleApp3
{
    static class Program
    {
        static string _apiKey;
        static string _sheetID;

        static string _baseUrl;
        static HttpClient _client;

        static Program()
        {
            _apiKey = ConfigurationManager.AppSettings["apiKey"];
            _sheetID = ConfigurationManager.AppSettings["sheetID"];

            _client = new HttpClient();
            _client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls | SecurityProtocolType.Ssl3;


            _baseUrl = "https://sheets.googleapis.com/v4/spreadsheets/{0}/values/{1}!{2}";

        }

        static void Main(string[] args)
        {
            using(HttpRequestMessage __request = new HttpRequestMessage())
            {
                __request.Method = HttpMethod.Post;
                __request.RequestUri = new Uri(string.Format(_baseUrl, _sheetID, "Sheet1", "A1:D3"));

                Dictionary<string, object> __parameters = new Dictionary<string, object>();
                __parameters.Add("key", _apiKey);

                __request.Content = new StringContent(__parameters.ToJson(), Encoding.UTF8, "application/json");
                

                using (HttpResponseMessage ___response = _client.SendAsync(__request).Result) // Error Here
                {
                    
                }
            }
            Console.ReadKey();
        }
    }
}
1 Answers

It may be an issue with URL encoding of the ":" character in your request

use System.Web.HttpUtility.UrlEncode() to make sure any characters in your sheetId, sheet name or cell location are encoded correctly.

In your code this line;

__request.RequestUri = new Uri(string.Format(_baseUrl, HttpUtility.UrlEncode(_sheetID), "Sheet1", HttpUtility.UrlEncode("A1:D3")));
Related