I wanted to work with google spread sheets with AWS lambda, so I tried it [Part 1]



The title is the same, but google spread sheet is operated with GAS (Google Apps Script). I had such a stereotype, but this time I wanted to operate with AWS lambda (Python), so I tried it.

Although it is completed by GCP alone、、、AWS慣れているので使いたかった。それだけです(笑)


The configuration is simple.

Untitled (1).png

Credentials that you create on the GCP side are encrypted and maintained in AWS Secrets Manager. It is better to stop writing authentication key in lambda code ...


Create a GCP-side authentication

It is like creating an IAM key in AWS.

First, enter the GCP console and create an appropriate project.

  • APIs and Services - > Libraries Enabling APIs
  • APIs and Services - > Credentials - > Creating a Service Account

Will do.

APIs and Services - > Libraries to enable APIs

スクリーンショット 2020-10-26 16-27-22.png

APIs and Services - > Credentials - > Create a Service Accountgcp.png

You will be asked for the name of the service account. The important thing is the service account ID. Let's make it easy for others to see and understand.


2.3 is omitted because it is optional. * If you want to restrict access to details, please set it.

スクリーンショット 2020-10-26 14-11-33.png

Once this is done, you can edit it because it looks like an email address in the service account section.


Since it is said to add a key from the edit, when you press it, you can choose whether it is json or P12, so if you choose json that everyone loves The json is downloaded locally. This completes the creation of the authentication key. (This is what AWS called credential.json, isn't it?)

[Attention!] If this leaks, there is a possibility that it will be accessed from the outside, so let's not put it on github or something!

スクリーンショット 2020-10-26 14-23-05.png

Preparing a spreadsheet to update

Set it as a user to share the service account ID (the guy who looks like a mead) mentioned earlier.

スクリーンショット 2020-10-26 15-56-10.png

AWS Preparation

AWS Secrets Manager

Create a place to store your keys.  It can also be created automatically with CloudFormation, but here we are creating it by hand.

スクリーンショット 2020-10-26 14-34-12.png

* By the way system manager You can store the same key in the keystore,  This time it's AWS Secrets Manager.

First of all, let's choose the その他のシークレット of making only the frame Select the type of secret. Please put the value appropriately (sampla: test here).

スクリーンショット 2020-10-26 14-37-50.png

スクリーンショット 2020-10-26 14-38-34.png

So convert the previous key (now renamed to google_key.json) to base64. Use AWS CLI to register for key. (*Please pay attention to the orientation of the profile w)

$ cat google_key.json | base64 > base64.json
aws secretsmanager update-secret --secret-id "/google/access_key" --secret-binary fileb://base64.json

By specifying --secret-binary, you will register in an encrypted state. ※ If you specify this, there is an advantage that you can not see the value from the console. If you want to enhance security, please use KMS.

The preparation of the GCP side and the AWS side is completed like this.

Next time: I wanted to work with google spread sheet with AWS lambda, so I tried it (Part 2)

Next time, we will create the lambda side!!! Have a good AWS life!!

mimimi sesese
Author by

mimimi sesese


Updated on October 28, 2020