Get a JSON file from an AppScript backend, using an AppScript front end, without getting a CORS error?

Question

I'm trying to build a an API-driven front end in Google AppsScript that calls a REST API hosted on AppScript to make some database queries.

I am currently simply trying to retrieve a JSON file with a GET request.

Everything I try, I get "CORS Missing Allow Origin".

My understand of CORS is that I might experience this with POST request (but maybe there's some people who have phrased their requests to get work this?)

I have a sense that the situation has changed over time, and what has worked in previous SO threads, doesn't seem to work for me now.

Sigh. I feel like Google's Documentation Team would benefit from a dedicated article to explaining how this is supposed to work.

If anyone can shed light on how I can get this to work, I've be most grateful:

client side code:

useEffect(() => {
  fetch('https://script.google.com/macros/s/AKfycbz3_hgjZe0E35ZI2mw7aNs3ASkYCct77qIzL_WTOQMu_ZZeax9WpHpPIwm-MFPhZAW77g/exec/get/all', {
    redirect: "follow",
    headers: {
      "Content-Type": "text/plain",
    },
  })
    .then(result => result.json())
    .then(rowData => setRowData(rowData))
}, []);

Server side code:

export function doGet(e) {
  if (e.pathInfo.startsWith('get/all')) {
    return getAllRecords(e);
  }
  else if (e.pathInfo.startsWith('get')) {
    return getRecord(e);
  }
  else {
    return getAllRecords(e);

    //return HtmlService.createHtmlOutput('Error: invalid path-  ' + e.pathInfo + '\n\n' + e.parameter + e);
  }
}

function getAllRecords(e) {
  // Connect to the MySQL database using the JDBC connector
  const conn = Jdbc.getConnection(url, username, password);

  // Construct the SELECT statement
  const sql = `SELECT * FROM cars LIMIT 100`;

  // Execute the INSERT statement
  const stmt = conn.prepareStatement(sql);
  const results = stmt.executeQuery();

  // Return the inserted record with the generated id
  const records = [];
  while (results.next()) {
    const record = {
      id: results.getInt('id'),
      name: results.getString('name'),
      make: results.getString('make'),
      price: results.getInt('price')
    };
    records.push(record);
  }

  return ContentService.createTextOutput(JSON.stringify(records)).setMimeType(ContentService.MimeType.TEXT);
//   return ContentService.createTextOutput(JSON.stringify(records)).setMimeType(ContentService.MimeType.JAVASCRIPT);
}

I've tried various combination of MIME Type, and request headers and I'll try any combinations people suggest.

I'm trying to read fetch.spec.whatwg.org/#http-cors-protocol to see if it can help explain what, if anything, I can do — Tim Dobson
– Tim Dobson, Commented Dec 19, 2022 at 14:37

Tanaike · Accepted Answer · 2022-12-20 03:20:43Z

In order to use pathInfo, in this case, it is required to use the access token. I thought that this might be the reason for your current issue. But, when the access token is used, I'm worried that is might not be useful for your actual situation. So, in this answer, I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, your script is modified using the access token. In this case, please modify your Javascript as follows.

From:

fetch('https://script.google.com/macros/s/AKfycbz3_hgjZe0E35ZI2mw7aNs3ASkYCct77qIzL_WTOQMu_ZZeax9WpHpPIwm-MFPhZAW77g/exec/get/all', {
  redirect: "follow",
  headers: {
    "Content-Type": "text/plain",
  },
})
  .then(result => result.json())
  .then(rowData => setRowData(rowData))

To:

const accessToken = "###"; // Please set your access token.
fetch('https://script.google.com/macros/s/AKfycbz3_hgjZe0E35ZI2mw7aNs3ASkYCct77qIzL_WTOQMu_ZZeax9WpHpPIwm-MFPhZAW77g/exec/get/all?access_token=' + accessToken)
  .then(result => result.json())
  .then(rowData => setRowData(rowData))

When you use the access token, please include the scopes of Drive API. Please be careful about this.

Pattern 2:

In this pattern, I would like to propose the modification without using the access token. When the access token cannot be used, unfortunately, pathInfo cannot be used. So, in this pattern, the query parameter is used instead of pathInfo.

Please modify your Javascript as follows.

From:

fetch('https://script.google.com/macros/s/AKfycbz3_hgjZe0E35ZI2mw7aNs3ASkYCct77qIzL_WTOQMu_ZZeax9WpHpPIwm-MFPhZAW77g/exec/get/all', {
  redirect: "follow",
  headers: {
    "Content-Type": "text/plain",
  },
})
  .then(result => result.json())
  .then(rowData => setRowData(rowData))

To:

fetch('https://script.google.com/macros/s/AKfycbz3_hgjZe0E35ZI2mw7aNs3ASkYCct77qIzL_WTOQMu_ZZeax9WpHpPIwm-MFPhZAW77g/exec?value=get%2Fall') // or ?value=get
  .then(result => result.json())
  .then(rowData => setRowData(rowData))

And also, please modify doGet of your Google Apps Script as follows.

Modified script:

function doGet(e) {
  if (e.parameter.value == "get/all") {
    return getAllRecords(e);
  } else if (e.parameter.value = "get") {
    return getRecord(e);
  } else {
    return getAllRecords(e);
  }
}

Note:

In this modification, it supposes that your getAllRecords(e) works fine. Please be careful about this.
And, in this modification, it supposes that your Web Apps is deployed as Execute as: Me and Who has access to the app: Anyone. Please be careful about this.
When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".
Thit is a sample modification. So, please modify this for your actual situation.

Reference:

Taking advantage of Web Apps with Google Apps Script (Author: me)

The referenced articles are very good, I strongly recommend them. Also, many thanks for the effort!

Collectives™ on Stack Overflow

Get a JSON file from an AppScript backend, using an AppScript front end, without getting a CORS error?

1 Answer 1

Pattern 1:

From:

To:

Pattern 2:

From:

To:

Modified script:

Note:

Reference:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pattern 1:

From:

To:

Pattern 2:

From:

To:

Modified script:

Note:

Reference:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related