CORS problem between aws and google spreadsheet

Serverless Framework
1

I need to read data from google spreadsheet. But stumbled on the CORS problem. I was recommended to use Amazon as a CORS gateway between google spreedsheet and my external page. I’ve set everything up with serverless and solved the CORS problem between my page and amazon by setting

headers: {
			"Access-Control-Allow-Origin" : "*", // Required for CORS support to work
			"Access-Control-Allow-Credentials" : true // Required for cookies, authorization headers with HTTPS
		  },

in handler.js for my respone parameter.
Also added

cors: true

in serverless.yml.
So far a simple hello function works fine.
But now I want to do the second and most important part. Read the data from google spreadsheet so I can pass it on. So is it possible to do that? Is there some settings or code that can be used as a work around for the fact that google doesn’t send proper headers?
I’ve so far only tried a simple https.get to retrieve the data, but get CORS problem.

Make changes at google is not an option. The url for the spreadsheet is dynamic and set by the user as input.

2

I might be misunderstanding something here, but you don’t need to worry about CORS between a Lambda function and some external API, in this case, Google Sheets.

This is because Lambda is not bound by any “single origin policy”, like you have in a web browser.

If this isn’t the case, maybe post the code and error message. I’ve sometimes seen CORS in error messages when it turned out to have nothing to do with CORS.

3

I’ve probably done some obvious mistake, but I’m looking for it in the wrong place.

My handler.js looks like this.

> module.exports.hello = (event, context, callback) => {
> 
>   const response = {
>     statusCode: 200,
>     body: JSON.stringify({
>       message: 'Go Serverless v1.0! Your LUST gateway function executed successfully!',
>       input: event,
>     }),
>   };
> 
>   callback(null, response);
> };
> 
> 
> module.exports.getUrl = (event, context, callback) => {
> 
> 	var url;
> 
> 	if (event.queryStringParameters !== null && event.queryStringParameters !== undefined) {
>         url = event.queryStringParameters.url;
> 	}
> 	
> 	
> 	const response = {
> 		statusCode: 200,
> 		headers: {
> 			"Access-Control-Allow-Origin" : "*", // Required for CORS support to work
> 			"Access-Control-Allow-Credentials" : true // Required for cookies, authorization headers with HTTPS
> 		  },
> 		body: JSON.stringify({
> 		   url
> 		}),
> 	};
> 	if(url !== null) {
> 		https.get(url, res => {
> 			res.on("data", (d) => {
> 				console.log("Data: ", d);
> 				response.body = d;
> 				callback(null, response);
> 			});
> 		}).on("error", (e) => {
> 			console.log("error: ", e);
> 			callback(null, response);
> 		});	
> 	}
> };

Everything works when call it using

serverless invoke -f getUrl --path lib/data.json --log

data.json contains

{“queryStringParameters”:{“url”:“https://docs.google.com/spreadsheets/d/1G0j8b47KjXUh_EJiZdNO2jJ_JNVcm_gMMCQHGtxG4T4/pub?gid=2014987267&single=true&output=csv”}}

but when I call it from an simple html-page (within script tags) it gives me CORS-error in return. (Calling the hello function in handler.js works fine.)

    var googleUrl = 'https://docs.google.com/spreadsheets/d/1G0j8b47KjXUh_EJiZdNO2jJ_JNVcm_gMMCQHGtxG4T4/pub?gid=2014987267&single=true&output=csv';
  var testUrl = 'https://5vwsfh6pyi.execute-api.eu-west-1.amazonaws.com/dev/corsUrl?url=';
    
    $.get(testUrl + encodeURIComponent(googleUrl), function( data ) {
  console.log(data);
    });

error message:

5vwsfh6pyi.execute-api.eu-west-1.amazonaws.com/dev/corsUrl?url=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1G0j8b47KjXUh_EJiZdNO2jJ_JNVcm_gMMCQHGtxG4T4%2Fpub%3Fgid%3D2014987267%26single%3Dtrue%26output%3Dcsv Failed to load resource: the server responded with a status of 502 ()
corsAmazonTest.html:1 XMLHttpRequest cannot load https://5vwsfh6pyi.execute-api.eu-west-1.amazonaws.com/dev/corsUrl?url=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1G0j8b47KjXUh_EJiZdNO2jJ_JNVcm_gMMCQHGtxG4T4%2Fpub%3Fgid%3D2014987267%26single%3Dtrue%26output%3Dcsv. No ‘Access-Control-Allow-Origin’ header is present on the requested resource. Origin ‘null’ is therefore not allowed access. The response had HTTP status code 502.

4

Nevermind…I found my stupid mistake, and u where absolutely correct. Nothing to do with CORS. It was my response object and how I updated it.

5

Nice work :slight_smile: And yes, it’s usually just some mistake in the code, misuse of an API, etc.

An uncaught error in the lambda function will be thrown out to the API gateway, which will wrap the error in a 500 response and send it back. But this implicit 500 response doesn’t have a CORS header, so when it gets to the browser, it fails the CORS check. The actual error message from lambda never gets to the browser.

It’s misleading, but from the browser’s perspective, it’s correct behaviour. If you make the request from, say, curl, you will find it doesn’t hit the CORS error, because CORS is not required in these cases (there is no other origin).

Some good ways to avoid this (not implying that you don’t do any or all of this): have well-structured, modular code; write unit and/or integration tests; automate as much as you can; and run functions locally. It’s much less expensive (on your time) to run something locally and have it spit an error straight back at you, than it is to deploy, then call the API, then check the remote logs, etc.

I’m actually wondering now, whether there is a fix for this in the API Gateway configuration, just by enabling CORS on the 500 response. And then how you would do that in serverless and/or cloud formation (i.e. via a serverless plugin). :thinking: