Does your company use SSO? The authentication needs to go straight to your Jira server. If your company is using some form of SSO that will definitely cause the problem you are describing.

You are able to make REST calls directly from your browser because you are doing that from within your network. The Google Sheet is making calls from outside your network which is what leads me to believe it is some kind of SSO/proxy issue.

Unfortunately there is no software workaround for that issue. My only suggestion would be to talk with your networking folks to see if they can provide some kind of workaround.