Search code examples
csvgoogle-apps-scriptd3.jsgoogle-drive-apicors

Upload a csv file in apps script from google drive blocked by CORS policy

I want to generate a D3.js tree from a google sheet.

In the exemple I followed, the tree is generated with a csv. So, I have generated with apps script a csv file on my google drive from a google sheet and now, I try to load it like this example:

<script src="https://d3js.org/d3.v4.js"></script>
<script>
d3.csv('https://raw.githubusercontent.com/holtzy/D3-graph-gallery/master/DATA/data_hierarchy_1level.csv', function(data) {console.log(data)})
</script>

Here it works fine but when I try to load my own csv from google drive, I have with this code:

<script src="https://d3js.org/d3.v4.js"></script>
<script>
d3.csv('https://drive.google.com/uc?id=1S9_KA87o_i7PjHILicXX21mlVeQc1jIA', function(data) {console.log(data)})
</script>

the following error messages:

Access to XMLHttpRequest at 'https://drive.google.com/uc?id=1S9_KA87o_i7PjHILicXX21mlVeQc1jIA' from origin 'https://n-mvggraccraz7qqhyhidnzi7cecuqlcodyfxsjjq-0lu-script.googleusercontent.com' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

VM1794 d3.v4.js:11472 GET https://drive.google.com/uc?id=1S9_KA87o_i7PjHILicXX21mlVeQc1jIA net::ERR_FAILED 303

I have checked the 2 files. They seems to be the same. I do not see where is the error. Could-you explain how to use a file from the drive like any file with an URL?

Solution

  • In the current stage, it seems that when the endpoint of webContentLink like https://drive.google.com/uc?id=###&export=download is used, the error like has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource. occurs. In order to avoid this, how about the following patterns?

    Pattern 1:

    In this pattern, the API key is used. When the API key is used, this issue can be removed. You can see how to retrieve the API key at here. In your URL, it becomes as follows.

    https://www.googleapis.com/drive/v3/files/{fileId}?alt=media&key={APIkey}

    When this URL is used, it becomes the following script.

    <script src="https://d3js.org/d3.v4.js"></script>
<script>
d3.csv('https://www.googleapis.com/drive/v3/files/{fileId}?alt=media&key={APIkey}', function(data) {console.log(data)})
</script>

    Pattern 2:

    In this pattern, the API key is not used. When I saw your question, I noticed In the exemple I followed, the tree is generated with a csv. So, I have generated with apps script a csv file on my google drive from a google sheet and now. From this, I understood that you want to use the CSV data retrieved from Spreadsheet. If my understanding is correct, I thought that your goal can be achieved directly using the Spreadsheet. The flow of this is as follows.

    1. Publish Spreadsheet as web pages. Ref
      • By this, you can retrieve the URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
    2. Modify URL.

      • Please modify the above-retrieved URL as follows.

          https://docs.google.com/spreadsheets/d/e/2PACX-###/pub?output=csv

      • In this URL, the 1st sheet of the Spreadsheet is used. For example, when you want to use the other sheet, please modify it as follows using the sheet ID.

          https://docs.google.com/spreadsheets/d/e/2PACX-###/pub?output=csv&gid={sheetId}

    When this URL is used for your script, it becomes as follows.

    <script src="https://d3js.org/d3.v4.js"></script>
<script>
d3.csv('https://docs.google.com/spreadsheets/d/e/2PACX-###/pub?output=csv', function(data) {console.log(data)})
</script>