There are a few things to address in this question so I'll break them out an answer them one-by-one:
API calls with Excel
Excel has a few different ways to fetch API data: Excel Web Queries, VB Script + Libraries, and Get and Transform (formerly called Power Query) each with its own eccentricities.
Get and Transform
Get and Transform will let you do things like add headers, use POST requests and parse JSON but is, as of the time of this writing (Jan 2020) only available on Windows and is quite frustrating to use. It also requires that manipulations are done inside of the Excel document which can be difficult to modify if copies have been made.
VBScript
In addition to requiring a different type of Excel sheet, HTTP/s calls need to be made with a system library. In Windows, this may be available but in OSX, you'll need to link to a local copy of curl which is a convenient hack but should never leave a developer machine.
Excel Web Queries
Excel Web Queries have been around for a very long time and allow you to give Excel a URL to fetch data from. You cannot change headers, make a POST request or manipulate the data in flight but it'll work for all Excel users.
Security of long-lived tokens in an URL
Yes, sending your long-lived credentials in plaintext is insecure. However, now that DNS over HTTPS is becoming more common with browsers like Firefox and Chrome looking to turn it on by default, this may not be an issue forever.
Using API Gateway to authorize with tokens in a URL
While trying to figure this out, I ran across a dozen or so posts saying that this was not possible. When API Gateway first came out, this was true and the position of the AWS team was that, due to security, it was not something they wanted to support. Now, however, not only is it possible, but it's also one of the available options when using the AWS console to create a custom authorizer.
Authorizer
module.exports = function urlTokenAuthorizer(event, context, callback) {
callback(null, {
principalId: "excel",
usageIdentifierKey: event.queryStringParameters["apikey"],
policyDocument: {
Version: "2012-10-17",
Statement: [
{
Action: "execute-api:Invoke",
Effect: "Allow",
Resource: event.methodArn
}
]
}
});
};
serverless config
service: serverless-secured-api
provider:
name: aws
runtime: nodejs12.x
# The serverless framework will automatically create a usage plan and
# associate the key with it. If you're not using serverless, make sure you
# have created a usage plan for your API and that the key is attached.
# Otherwise, you will get { message: forbidden }
apiKeys:
- my-key
# According to the AWS docs on Lambda Authorizers, "If the API uses a usage
# plan (the apiKeySource is set to AUTHORIZER), the Lambda authorizer
# function must return one of the usage plan's API keys as the
# usageIdentifierKey property value."
#
# I did not find that to be true, for some reason. When you add an `apiKey`
# in serverless, it creates a usage plan and assiciates it with that plan.
# No matter what I did, nothing worked until I forced the apiKeySourceType
# to AUTHORIZER. Without this line, every request will return:
# { message: Forbidden }
#
# https://docs.aws.amazon.com/apigateway/latest/developerguide/api-gateway-lambda-authorizer-output.html
apiGateway:
apiKeySourceType: AUTHORIZER
functions:
urlTokenAuthorizer:
handler: handler.urlTokenAuthorizer
getMedications:
handler: handler.getMedications
events:
- http:
path: get-medications
method: GET
private: true
authorizer:
name: urlTokenAuthorizer
resultTtlInSeconds: 0
# Configure your authorizer to look in the querystring for the key.
# If it does not find a value here, the authorizer will not fire.
identitySource: method.request.querystring.apikey
# The default type is token which won't return information about
# the request. You can read more about token vs request authorizers
# in the AWS docs here.
# https://docs.aws.amazon.com/apigateway/latest/developerguide/api-gateway-lambda-authorizer-input.html
type: request