27:null 28:["$","title",null,{"children":"Using Google Sheets as a Proxy Server Workaround for Cross-Origin Resource Sharing (CORS) Restrictions | Observable Ambassadors | Observable"}] 2d:T4d4,If you're coming up against [CORS restrictions](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS) [fetching data](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API/Using_Fetch) into a published notebook, [@tomlarkworthy](https://observablehq.com/@tomlarkworthy) makes available [***fetchp***](https://observablehq.com/@tomlarkworthy/fetchp); a drop in replacement for [fetch](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API) that will work in published notebooks. But what if your notebook is private? One option is to use a proxy (egs: [sofetch](https://observablehq.com/@alecglassford/so-fetch), [fetch js](https://observablehq.com/@randomfractals/fetch) ); assuming that proxy's availability is stable. If the data being accessed is [csv or tsv](https://www.google.com/sheets/about/) formatted, this notebook demonstrates an alternative that utilizes [Google Sheets](https://www.google.com/sheets/about/) as a reliable proxy server for the otherwise CORS-restricted request. This strategy utilizes Google Sheets': * [***IMPORTDATA***](https://support.google.com/docs/answer/3093335?hl=en) function; * [***Publish Files as Web Pages***](https://support.google.com/a/users/answer/9308870?hl=en) feature.2e:T435,To confirm the fetch request was blocked by the CORS policy, use your browser's [developer tool](https://developer.mozilla.org/en-US/docs/Learn/Common_questions/What_are_browser_developer_tools) and review the error message:

As detailed in [Cross-Origin Resource Sharing (CORS)](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS):

The CORS mechanism supports secure cross-origin requests and data transfers between browsers and servers. Modern browsers use CORS in APIs such as XMLHttpRequest or [Fetch](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API) to mitigate the risks of cross-origin HTTP requests.

CORS Process Workflow: [Cross-Origin Resource Sharing (CORS)](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS)

2f:T5eb,### [***IMPORTDATA***](https://support.google.com/docs/answer/3093335?hl=en) Critical to the technique is assigning the following formula in the sheet's **A1** cell: > ***=IMPORTDATA("https://drive.google.com/uc?id=1zO8ekHWx9U7mrbx_0Hoxxu6od7uxJqWw&export=download)*** The function's url parameter is the same data access url that was not accessible via the direct/non-proxied fetch. The spreadsheet function retrieves the data and keeps it updated with a [1 hour refresh rate](https://support.google.com/docs/answer/58515#zippy=%2Cchoose-how-often-formulas-calculate). Factor this in if your application requires a more frequent refresh rate. If your url has parameters to qualify the request, it would be reflected in what's passed to the [***IMPORTDATA***](https://support.google.com/docs/answer/3093335?hl=en) function. Example: If the data was produced by a ***dataServer.com*** server-side process with starting date of *Start*, the url parameter could be constructed with the [CONCAT](https://support.google.com/docs/answer/3094123?hl=en), [TEXT](https://support.google.com/docs/answer/3094139?hl=en&ref_topic=3105625) and [TODAY](https://support.google.com/docs/answer/3092984?hl=en) sheet functions: > =***IMPORTDATA***(***CONCAT***("https://dataServer.com/get?Start=",***TEXT***(***TODAY***(),"MM/DD/YY"))) Remember to place the function in cell **A1**. The following publishing, proxy fecthing and response parsing depend on only the proxied data being in a single Google Sheet.30:T486,/* Usage in md/html: `${yield* progressivelyStrike(["String to Progrssively Strike","NoStrike"],.5, 2)}` where "String to Strike" is the strikable string","NoStrike" is non-strikable .5 is proportion of second pause between strike; defaults to 1; 2 is Heading level; defaults to undefined...no heading References: https://observablehq.com/@observablehq/introduction-to-generators https://observablehq.com/@observablehq/custom-generators https://developer.mozilla.org/docs/Web/JavaScript/Reference/Operators/yield* https://developer.mozilla.org/docs/Web/JavaScript/Reference/Statements/function* */ function* progressivelyStrike(aStr, iteration = 1, level = undefined) { let i = 0; const len = aStr[0].length; const levelIt = (s, l) => (l ? html`${s}${aStr[0]} levelIt( `~~${aStr[0].slice( 0, i % (len + 1) )}~~${aStr[0].slice(i % (len + 1), len)} ${aStr[1]}`, level ) ); } }31:[{"id":"99d612d4da6ec6475b932fafdf819c92076bec15547101c55e4755b930c15c5dd608fee550cce807198dddc578d0db583f7870f4b65454b457837aa239de752f","href":"https://static.observableusercontent.com/files/99d612d4da6ec6475b932fafdf819c92076bec15547101c55e4755b930c15c5dd608fee550cce807198dddc578d0db583f7870f4b65454b457837aa239de752f","name":"image@1.png","type":"image/png","lastModified":1787685623963.6,"size":9647}] 2b:{"id":"2c96b4b7c04d57a1","slug":null,"title":"Using Google Sheets as a Proxy Server Workaround for Cross-Origin Resource Sharing (CORS) Restrictions","thumbnail":"a0168b81f6c33d13417232d568dbc5064e99751cfdd150208bcbcc2e476c9f0f","access_level":"public","create_time":"$D2022-11-22T21:00:18.910Z","update_time":"$D2026-08-25T19:55:14.878Z","view_time":null,"first_public_time":"$D2022-11-23T05:45:59.931Z","has_unpublished_edits":null,"fork":false,"stars":2,"starred":false,"owner":{"type":"team","id":"11bfeb572ddbe905","login":"ambassadors","name":"Observable Ambassadors","avatar_url":"https://avatars-next.observableusercontent.com/93bf798c058a2bf8d3bb90c0754f774b6278b8b16748784f520b07a8800ef772","tier":"pro_2024"},"version":374,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null}],"members":[{"type":"team","id":"11bfeb572ddbe905","login":"ambassadors","name":"Observable Ambassadors","avatar_url":"https://avatars-next.observableusercontent.com/93bf798c058a2bf8d3bb90c0754f774b6278b8b16748784f520b07a8800ef772","tier":"pro_2024","role":"editor"},{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":3,"collection":null,"type":"notebook","body":{"title":"Using Google Sheets as a Proxy Server Workaround for Cross-Origin Resource Sharing (CORS) Restrictions","cells":[{"id":0,"value":"# Using Google Sheets as a Proxy Server Workaround for [Cross-Origin Resource Sharing (CORS) Restrictions](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":73,"value":"$2d","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":146,"value":"$${yield* progressivelyStrike([\"Data\",\" 👎\"],1,2)} \n\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":267,"value":"Using a [sample csv](https://github.com/datablist/sample-csv-files) file found on GitHub, the following cell illustrates the CORS restriction; with an attempted fetch on:\n\n > https://drive.google.com/uc?id=1zO8ekHWx9U7mrbx_0Hoxxu6od7uxJqWw&export=download","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":83,"value":"fetch(\n \"https://drive.google.com/uc?id=1zO8ekHWx9U7mrbx_0Hoxxu6od7uxJqWw&export=download\"\n).then((response) => response.text())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":198,"value":"$2e","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":158,"value":"## The Proxy Google Sheet 👍\nThe following [embedded Google Sheet](https://support.google.com/docs/answer/183965) hosts the [sample csv data](https://github.com/datablist/sample-csv-files/raw/main/files/organizations/organizations-100.csv) that was unsuccessfully fetched. \n\nIt is referenced in the notebook via an [***iframe***](https://developer.mozilla.org/en-US/docs/Web/HTML/Element/iframe) solely for illustration and its embedding is not required for utilizing it as the data's proxy.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":11,"value":" ","pinned":true,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":162,"value":"$2f","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":171,"value":"## Publish Google Sheet to the Web with the *Comma-separated values (.csv)* Option\nThe final step in setting up the 'data proxy' is to [publish the data sheet and obtain a fetchable url](https://sites.google.com/a/ccpsnet.net/googletraining/faq/FAQ-Drive/how-do-i-publish-a-sheet-to-the-web-and-what-does-that-mean); selecting the *Comma-separated values (.csv)* as the publish option. (Related Notebook: [Importing data from Google Spreadsheets into a notebook / webpage](https://observablehq.com/@bryangingechen/importing-data-from-google-spreadsheets-into-a-notebook-we))\n\nFor this notebook's scenario, the following url was generated when publishing the Google Sheet; note the ***output=csv*** attribute at the end of the url:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":181,"value":"googleProxyURL = \"https://docs.google.com/spreadsheets/d/e/2PACX-1vQODu_Au-Ynem_QBlyWNXxzyvQF5KuS7MpNUG-11lcH-vYJgF72Ks5Z5xswuyymooiJhqVRxD_HxByI/pub?gid=0&single=true&output=csv\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":299,"value":"## Proxy Fetch and Parse 📊","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":185,"value":"Using *googleProxyURL* the proxied data is fetched and placed into an array via [d3.csvParse](https://github.com/d3/d3-dsv/blob/v3.0.1/README.md#csvParse):","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":27,"value":"dataFromGoogleSheet = fetch(googleProxyURL)\n .then((response) => response.text())\n .then((text) => d3.csvParse(text))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":230,"value":" A subset of the data retrieved is plotted:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":113,"value":"Plot.plot({\n x: {\n // Extracts the first 10 unique countries encountered in the sheet\n domain: Array.from(\n new Set(dataFromGoogleSheet.map((d) => d.Country))\n ).slice(0, 10),\n tickFormat: (d) => (d ? (d.length > 5 ? d.slice(0, 5) + \"…\" : d) : \"\"),\n label: \"Country\"\n },\n y: {\n label: \"Total Unique Customers\"\n },\n marks: [\n Plot.barY(\n dataFromGoogleSheet, // Pass full dataset so all occurrences are counted\n Plot.groupX(\n { y: \"distinct\" },\n {\n x: \"Country\",\n y: \"Customer Id\",\n fill: \"blue\"\n }\n )\n )\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":259,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W31","stdlib":"1","titling":"auto"}} 2c:"$2b:body"