Trouble accessing Google Sheet as a TSV file

user_5732386 0
Viewed 409

For the past few years, I've been using Sheets as a data source for a web app by using the following code to turn the id into a direct link to a TSV file:

    let id="1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ"
    let str="https://docs.google.com/spreadsheets/d/"+id+"/export?format=tsv";
                                    
    var xhr=new XMLHttpRequest();
    xhr.open("GET",str);
    xhr.onload=function() {/* act on data */ };
    xhr.send();
    xhr.onreadystatechange=function(e) {                                                                        
    if ((xhr.readyState === 4) && (xhr.status !== 200)) {  
       /* Show error */
       }

It still works on old files, but new ones yield a CORS error:

 Access to XMLHttpRequest at 'https://doc-00-0g-sheets.googleusercontent.com/export/l5l039s6ni5uumqbsj9o11lmdc/5filqetsf3ohbeiq2e8vbtf8ik/1593267040000/112894833168181755194/*/1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ?format=tsv' (redirected from 'https://docs.google.com/spreadsheets/d/1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ/export?format=tsv') from origin 'https://viseyes.org' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

Works: www.viseyes.org/scale?1LSnAM3A62AQipZfqxDtlOjt4MWJ0fBP22cdyqJqEj5M

Error: www.viseyes.org/scale?1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ

google-docs-api google-sheets-api javascript
1 Answers

I believe your goal as follows.

  • You want to retrieve the data with TSV format from the Google Spreadsheet using Javascript.
  • Your spreadsheet is publicly shared.

For this, how about this answer?

Issue and workaround:

I could confirm the same situation from your question. Unfortunately, I couldn't remove this error. So, in this case, as a workaround, I would like to propose to use Web Apps created by Google Apps Script as the wrapper. By this, the error can be removed. The flow of this workaround is as follows.

  1. Request to Web Apps from Javascript.
  2. At Web Apps, the data is retrieved from "https://docs.google.com/spreadsheets/d/"+id+"/export?format=tsv".
  3. Return the data with the TSV format from Web Apps.

Usage:

Please do the following flow.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Prepare script.

Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.

function doGet() {
  let id = "1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ";  // This is from your script.
  let str = "https://docs.google.com/spreadsheets/d/"+id+"/export?format=tsv";  // This is from your script.
  
  const value = UrlFetchApp.fetch(str);
  return ContentService.createTextOutput(value.getContentText());
}

  • If your Google Spreadsheet is not publicly shared, please modify as follows.

      function doGet() {
    let id = "1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ";  // This is from your script.
    let str = "https://docs.google.com/spreadsheets/d/"+id+"/export?format=tsv";  // This is from your script.

    const value = UrlFetchApp.fetch(str, {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});
    return ContentService.createTextOutput(value.getContentText());

    // DriveApp.getFiles()  // This is used for automatically detecting the scope.
  }

3. Deploy Web Apps.

  1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
  2. Select "Me" for "Execute the app as:".
    • By this, the script is run as the owner.
  3. Select "Anyone, even anonymous" for "Who has access to the app:".
    • In this case, no access token is required to be request. I think that I recommend this setting for your goal.
    • Of course, you can also use the access token. At that time, please set this to "Anyone". And please include the scope of https://www.googleapis.com/auth/drive.readonly and https://www.googleapis.com/auth/drive to the access token. These scopes are required to access to Web Apps.
  4. Click "Deploy" button as new "Project version".
  5. Automatically open a dialog box of "Authorization required".
    1. Click "Review Permissions".
    2. Select own account.
    3. Click "Advanced" at "This app isn't verified".
    4. Click "Go to ### project name ###(unsafe)"
    5. Click "Allow" button.
  6. Click "OK".
  7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

4. Run the function using Web Apps.

When you use this, please modify your Javascript script as follows and test it.

From: 
let id="1zD3eIL8LCTJ8F_8U3kWA6k5WPJNKr_UZ_93bnARlMxQ"
let str="https://docs.google.com/spreadsheets/d/"+id+"/export?format=tsv";
To: 
let str = "https://script.google.com/macros/s/###/exec";

Note:

  • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.
  • In my environment, I could confirm that when above workaround is used, no error occurs and the data with the TSV format can be retrieved.

References:

user_71086530
Related