CODEWITHSUNDEEP
javascriptapigoogle-sheetstabletop.js
nirshh5
nirshh5

Reputation: 43

getting data from google sheet stopped working due to CORS policy

js to get data from google sheet for my website and it worked perfectly for 2 months but now stopped working. I'm getting this error:

index.html:1 Access to XMLHttpRequest at 'https://spreadsheets.google.com/feeds/worksheets/1vX54pYMqq_U6QRTn86zsh9wf8uel9Oiku6NZ3bPPBTg/public/basic?alt=json' from origin 'null' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

I'm using this script :

<script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script>

<script type='text/javascript'>    
  var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1vX54pYMqq_U6QRTn86zsh9wf8uel9Oiku6NZ3bPPBTg/edit?usp=sharing';

  function init() {
    Tabletop.init( {
      key: publicSpreadsheetUrl,
      callback: showInfo,
      simpleSheet: true 
    } )
  }

  function showInfo(data, tabletop) {
    alert('Successfully processed!')
    console.log(data);
  }

  window.addEventListener('DOMContentLoaded', init)
</script>

I am not really familiar with backend development so that is why I'm using this as a free and easy cms - I looked for similar questions about it but I cant understand how to fix this since I don't really know how to work with APIs. I would really appreaciate any help - thanks!

Upvotes: 0

Views: 2044

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

It seems that it's shutting down. The deadline is Aug 2, 2021. Need to migrate to Sheets v4 API. Try this function instead, you will get a json from the spreadsheet:

<html><body>
<script>

function reqListener () {
  var jsonString = this.responseText.match(/(?<="table":).*(?=}\);)/g)[0];
  var json = JSON.parse(jsonString);
  document.getElementById("json").innerHTML = jsonString;
  return json
}

var id = '1vX54pYMqq_U6QRTn86zsh9wf8uel9Oiku6NZ3bPPBTg';
var gid = '0';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;
var oReq = new XMLHttpRequest();
oReq.onload = reqListener;
oReq.open("get", url, true);
oReq.send();
 
</script>
<div id="json">&nbsp;</div></body></html>
  • note that first row is dedicated to labels, then rows[0] corresponds to row#2 and c[0] corresponds to column A
  • if you want to retrieve B1 (column 2 row 1) : json.cols[1].label
  • if you want to retrieve B2 (column 2 row 2) : json.rows[0].c[1].v

Upvotes: 1

Suraj S.
Suraj S.

Reputation: 91

its a CORS policy issue means your trying to access https url from http or vise versa .

you can solve this by allowing origin from iis or hosting server .or ust change ur domain protocol http to https .

Upvotes: 0

Related Questions