Darshan Vaghasiya
Darshan Vaghasiya

Reputation: 1

CORS in google apps script

I wanted to know how to implement CORS in google apps script. I am facing issue while fetching data(GET REQUEST) from google sheet using web app of google apps script.

GET request : Sending get request to google apps script

url = "google apps script web app link"
fetch(url, {
      
      method: "GET",  
      headers: {
        "Content-Type": "application/json",
      },
      mode: "cors",
    })

Apps script : Return data from spreadsheet

function doGet() {
  var sheetId = "sheetId";
  var sheetName = "Sheet1";
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName); // Get active sheet
  var data = sheet.getDataRange().getValues(); // Get all data (including headers)

  var headers = data[0]; // The first row will be used as the keys for JSON objects
  var jsonData = [];
  
  // Loop through each row, starting from the second row (index 1)
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var rowData = {};
    
    // Loop through columns (headers) and build key-value pairs
    for (var j = 0; j < headers.length; j++) {
      rowData[headers[j]] = row[j];
    }
    
    jsonData.push(rowData);
  }
  
  // Convert to JSON string
  var jsonOutput = JSON.stringify(jsonData);
  
  // Set the CORS headers
  var response = ContentService.createTextOutput(jsonOutput)
                    .setMimeType(ContentService.MimeType.JSON);
                  
  

  
  return response;
}

Problem : hello.html:1 Access to fetch at "/apps script web applink" (redirected from "/apps script web applink") from origin 'chrome-extension://{extensionid}' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

Upvotes: 0

Views: 71

Answers (1)

Patsytalk
Patsytalk

Reputation: 1014

Try this updated script

If you deploy a Google Apps Script as a Web App, Google automatically handles CORS headers for you. This means that if your script is deployed with the correct access permissions, you don't need to explicitly set CORS headers like you would in a typical server environment.

Script added

When a user visits an app or a program sends the app an HTTP GET request, Apps Script runs the function doGet(e).

function doGet(e) {

If there is no data (or only the header row), it sends a JSON error message: "No data available".,

 if (data.length < 2) {
      return ContentService.createTextOutput(
        JSON.stringify({ error: "No data available" })
      ).setMimeType(ContentService.MimeType.JSON);
    }

Adding a try .. catch for error handling

} catch (error) {
  return ContentService.createTextOutput(
    JSON.stringify({ error: error.message })
  ).setMimeType(ContentService.MimeType.JSON);
}

Modified script

function doGet(e) {
  try {
    var sheetId = "Google Sheet ID";
    var sheetName = "Sheet1";
    var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
    var data = sheet.getDataRange().getValues(); 

    if (data.length < 2) {
      return ContentService.createTextOutput(
        JSON.stringify({ error: "No data available" })
      ).setMimeType(ContentService.MimeType.JSON);
    }

    var headers = data[0];
    var jsonData = [];

    for (var i = 1; i < data.length; i++) {
      var row = data[i];
      var rowData = {};

      for (var j = 0; j < headers.length; j++) {
        rowData[headers[j]] = row[j];
      }

      jsonData.push(rowData);
    }

    return ContentService.createTextOutput(
      JSON.stringify(jsonData, null, 2) 
    ).setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
    return ContentService.createTextOutput(
      JSON.stringify({ error: error.message })
    ).setMimeType(ContentService.MimeType.JSON);
  }
}

Spreadsheet

(A) Name (B) How often (C) Rank
Superman 54 32
Spiderman 45 23

Sample Response on Web-App

[
  {
    "A) Name": "Superman",
    "(B) How often": 54,
    "(C) Rank": 32
  },
  {
    "A) Name": "Spiderman",
    "(B) How often": 45,
    "(C) Rank": 23
  }
]

Note: If you face CORS issues when calling the Web App from a different domain (for example, from a Chrome Extension), make sure that the Web App is deployed as accessible by the right audience ("Anyone, even anonymous") and the client (the webpage, extension, or other origins) is allowed to make those requests.

References

Upvotes: 1

Related Questions