Search code examples
jsongoogle-apps-scriptcors

Google Sheet to JSON app script gives CORS error

I am getting a JSON from Sheet data using a deployed app script

The JSON data in the browsers looks correct

But when I add the URL to an app called Kumu.io to visually represent the data

https://script.google.com/macros/s/AKfycbymN0Wkfl-Lp4n0kD2L1DXmY4BbZhi8CvoB8nW2pguvelXhqAhRo4YQuYquS5Mv_FU/exec?id=12ygFGlEbqosGDj_4VSg6pzTjnHn58XuLAtmMpkdoUzM&sheet=Elements&header=1&startRow=2

I get a CORS error message

Please make sure the url below is valid and CORS is enabled.

How do I enable CORS for this deployed script?

Thanks

Google sheet with data and code https://docs.google.com/spreadsheets/d/12ygFGlEbqosGDj_4VSg6pzTjnHn58XuLAtmMpkdoUzM/edit?usp=sharing

app script

/**
 * Test the original functionality of returning all objects
 * in the spreadsheet in JSON.
 */
function test1() {
  return runTest_({
      parameters : {
        id : "12ygFGlEbqosGDj_4VSg6pzTjnHn58XuLAtmMpkdoUzM",
        sheet : "Elements",
        header : 1,
        startRow : 2,
      }
  });
}

/**
 * Basic test logs a request and response. Use View -> Logs to check if it's
 * correct.
 * (In the future maybe actually check equality to expected output.)
 * @param request The HTTP request to test
 */
function runTest_(request) {
  console.log(request);
  console.log(doGet(request).getContent().toString());
}

/**
 * Main method is called when the script receives a GET request.
 * Receives the request, generates and returns the output.
 */
function doGet(request) {
  // Get request params.
  var sheetKey  = request.parameters.id;
  var sheetName = request.parameters.sheet;
  var callback  = request.parameters.callback;
  var headerRow = request.parameters.header;
  var startRow  = request.parameters.startRow;
    
  // Parse the spreadsheet.
  var spreadsheet = SpreadsheetApp.openById(sheetKey);
  var keys = getHeaderRowKeys_(spreadsheet, sheetName, headerRow);
  var data = readData_(spreadsheet, sheetName, keys, startRow);
  
  // Filter for matching terms.
  data = data.filter(function(entry) {
    var matches = true;
    for (var k in keys) {
      var key = keys[k].replace(/\s+/g, '_');
      var searchTerm = request.parameters[key];
      // Use the string form of the value since params are strings by default
      if (searchTerm != undefined)
        matches = matches && ("" + entry[key] == searchTerm);
    }
    // Matches is true iff all params are undefined or all values for keys match.
    return matches;
  });
  
  // Write and return the response.
  var response = JSON.stringify({ elements: data });
  var output = ContentService.createTextOutput();
  if (callback == undefined) {
    // Serve as JSON
    output.setContent(response).setMimeType(ContentService.MimeType.JSON);
  } else {
    // Serve as JSONP
    output.setContent(callback + "(" + response + ")")
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
  }
  return output;
}

/**
 * Get a row in a spreadsheet as an Object, using the values in the header row as
 * keys and the corresponding row values as the values.
 * 
 * @param spreadsheet Spreadsheet object housing the sheet and header
 * @param sheetName Name of the specific sheet in the spreadsheet with the data
 * @param properties Optional array of keys to use for the row values. Default is the first row.
 * @param startRowNum Optional top row number of the rows to parse. The default is
 * the second row (i.e., below the header).
 */
function readData_(spreadsheet, sheetName, properties, startRowNum) {
  if (typeof properties == "undefined") {
    properties = getHeaderRowKeys_(spreadsheet, sheetName);
  }
  
  var rows = getDataRows_(spreadsheet, sheetName, startRowNum);
  var data = [];
  for (var r = 0, l = rows.length-1; r < l; r++) {
    var row = rows[r];
    var record = {};
    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    data.push(record);
  }
  return data;
}

/**
 * Parse spreadsheet data as an array of Javascript Objects.
 *
 * @param spreadsheet Spreadsheet object with the data to get
 * @param sheetName Name of the specific sheet in the spreadsheet with the data
 * @param startRowNum Optional top row number of the rows to parse. The default is
 * the second row (i.e., below the header).
 */
function getDataRows_(spreadsheet, sheetName, startRowNum) {
  if (typeof startRowNum == "undefined") startRowNum = 2;
  
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet.getRange(startRowNum, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
}

/**
 * Return the array of keys used in the header, replacing whitespace with underscores.
 * 
 * @param spreadsheet Spreadsheet object housing the sheet and header
 * @param sheetName Name of the specific sheet in the spreadsheet whose header values to get
 * @param rowNum Optional exact row number of the header. Default is the first row.
 */
function getHeaderRowKeys_(spreadsheet, sheetName, rowNum) {
  if (typeof rowNum == "undefined") rowNum = 1;
  return getHeaderRow_(spreadsheet, sheetName, rowNum).map(function(value) { 
    return value.replace(/\s+/g, '_');
  });
}

/**
 * Get the values in the header row of the given sheet in a spreadsheet
 * 
 * @param spreadsheet Spreadsheet object housing the sheet and header
 * @param sheetName Name of the specific sheet in the spreadsheet whose header values to get
 * @param rowNum Exact row number of the header.
 */
function getHeaderRow_(spreadsheet, sheetName, rowNum) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet.getRange(rowNum, 1, 1, sheet.getLastColumn()).getValues()[0];
}
Solution

  • This is just my guess. From your following situation,

    The JSON data in the browsers looks correct But when I add the URL to an app called Kumu.io to visually represent the data I get a CORS error message

    and, from the result that I accessed your showing URL of Web Apps,

    I guessed that the reason for your current issue might be due to the setting of your Web Apps. So, when I saw the Google Apps Script project of your provided Spreadsheet, I noticed that the setting of your Web Apps was as follows.

      "webapp": {
    "executeAs": "USER_ACCESSING",
    "access": "ANYONE"
  }

    In this case, in order to access the Web Apps, it is required to log in to Google account. Although I'm not sure about Kumu.io of But when I add the URL to an app called Kumu.io to visually represent the data, I guess that this might be the reason for your current issue.

    In this case, please update your Web Apps by the following setting.

    Execute as: me

    Who has access to the app: Anyone

    If you want to modify appsscript.json, please modify it as follows.

    • From

        "webapp": {
    "executeAs": "USER_ACCESSING",
    "access": "ANYONE"
  }

    • To

        "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  }

    With this modification, your Web Apps can be accessed without logging in to your Google account.

    Note:

    References: