I'm looking to transfer data from a number of Google Sheets to a website on Webflow. The data in the Sheets get updated over time (some automatically and some manually), and our website needs to dynamically update with it. We had this working up until a few weeks ago. We used Sheets’s “Publish to Web” functionality, then simply scraped from that page with an xmlhttp request.
Then, Google changed their system. The old links we used have been replaced with a page that says:
The Sheets v3 API has been turned down. Further information can be found at: https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api
With v4, You can still publish to web, but it outputs a different link and this one causes CORS issues when we try to request it with an xmlhttp call. We keep getting this error:
Access to XMLHttpRequest at 'https://docs.google.com/spreadsheets/u/2/d/e/2PACX-1vTkGAWMFigGt-_40bYYtR9OuDh5t0gO0B5tAUhiUg2-XuSXfIazjgbvoqBxvQxxIPr62OfmdWQaWtPt/pubhtml' from origin 'null' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: Redirect is not allowed for a preflight request.
I can’t figure out what to do with this, but in the process of researching I’ve found that it seems Google does not want people to be grabbing information from this publish page in the first place- it’s meant to be a link to send to people.
We’re not exactly sure what to do at this point and could use some help. Are there any work-arounds we’re unaware of, or is there a different system we could be using entirely?
The code:
let fonds_xmlhttp = new XMLHttpRequest();
fonds_xmlhttp.onload = function () {
if (this.readyState == 4 && this.status == 200){
let fonds_data = JSON.parse(this.responseText);
// do stuff with fonds_data
} else {
console.log("status: " + this.status);
}
};
fonds_xmlhttp.open("GET",
"https://docs.google.com/spreadsheets/u/2/d/e/2PACX-1vTkGAWMFigGt-_40bYYtR9OuDh5t0gO0B5tAUhiUg2-XuSXfIazjgbvoqBxvQxxIPr62OfmdWQaWtPt/pubhtml",
true);
EDIT:
This is the flow I'm now using... I'm just trying to open this html page in chrome, now using the script that @Tanaike provided:
<meta name="viewport" content="width=device-width, initial-scale=1">
<body>
<div>
<table class="fonds-tabelle">
<thead>
<tr>
<th> </th>
<th>2021</th>
<th>2020</th>
<th>2019</th>
<th>2018</th>
<th>2017</th>
<th>2016</th>
<th>Ø 3J</th>
<th>Ø 5J</th>
</tr>
</thead>
<tbody id="fonds-daten">
</tbody>
</table>
</div>
</body>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>
<script type="text/javascript">
let fonds_xmlhttp = new XMLHttpRequest();
fonds_xmlhttp.onload = function () {
if (this.readyState == 4 && this.status == 200){
let [header, ...values] = Papa.parse(this.responseText).data;
values = values.map(r => r.map(c => {
const t = c.trim();
return isNaN(t) ? t : Number(t);
}));
const fonds_data = values.map(r => r.reduce((o, c, j) => Object.assign(o, {[header[j] && header[j].trim()]: c}), {}));
console.log(fonds_data);
/* Then iterate over fonds_data and fill the tbody fonds-daten with it */
} else {
console.log("status: " + this.status);
}
};
const url = "https://docs.google.com/spreadsheets/u/2/d/e/2PACX-1vTkGAWMFigGt-_40bYYtR9OuDh5t0gO0B5tAUhiUg2-XuSXfIazjgbvoqBxvQxxIPr62OfmdWQaWtPt/pub?output=csv";
fonds_xmlhttp.open("GET", url, true);
fonds_xmlhttp.send();
</script>
I still get the following CORS error when I open this in Chrome:
Access to XMLHttpRequest at 'https://docs.google.com/spreadsheets/u/2/d/e/2PACX-1vTkGAWMFigGt-_40bYYtR9OuDh5t0gO0B5tAUhiUg2-XuSXfIazjgbvoqBxvQxxIPr62OfmdWQaWtPt/pub?output=csv' from origin 'null' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.
