0

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!

nirshh5
  • 43
  • 1
  • 8
  • You can refer to this [Sheets API Javascript Quickstart](https://developers.google.com/sheets/api/quickstart/js?hl=en) to learn more how to setup and use sheets api – Ron M Aug 20 '21 at 15:14

2 Answers2

1

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
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Hi @Mike Steelson, you need to share your Google Sheet public on the web to use your workaround right? (I'm just curious :)) – Ron M Aug 20 '21 at 15:24
  • https://www.staelens.fr/endpointJson.html available one week (sorry but my server will not be able to give a wide bandwidth), nevertheless, the code can be copied in a text file and transfer to any server you wish – Mike Steelson Aug 20 '21 at 15:32
  • test here https://codepen.io/mikesteelson/pen/vYZBKXM – Mike Steelson Aug 21 '21 at 12:15
0

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 .

Suraj S.
  • 81
  • 9