CODEWITHSUNDEEP
apigoogle-apps-scriptcors
Tim Dobson
Tim Dobson

Reputation: 95

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

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.

Upvotes: 1

Views: 255

Answers (1)

Tanaike
Tanaike

Reputation: 201358

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:

Upvotes: 3

Related Questions