[PYTHON] I wanted to operate google spread sheet with AWS lambda, so I tried it [Part 2]

Overview

This is a continuation of the previous article. Last time I created the lower half, so this time I will create the upper half

Untitled (1).png

serverless framework environment

Operating environment

npm (6.14.8): If it's not old ... serverless (2.8.0): If it is 2.x.x series python (3.8.2): If it is 3.8 series

Constitution

Below, for those who know serverless framework, for those who can understand python somehow The explanation is omitted. Please see for reference only.

functions/layers/serverless.yml  #Configuration file
functions/layers/package.json   #Package related
functions/layers/requirements.txt #Package related
functions/layers/python/util.py  #Common function

functions/main/serverless.yml  #Configuration file
functions/main/handler.py        #lambda main

It is convenient to create layers when there are many packages. Reference Aws Lambda Layer

Description of each module

functions/layers/serverless.yml

service: goole-test-layer
frameworkVersion: "2"

plugins:
  - serverless-python-requirements

custom:
  defaultStage: dev
  pythonRequirements:
    dockerizePip: true
    layer: true

provider:
  name: aws
  runtime: python3.8
  stage: ${opt:stage, self:custom.defaultStage}
  region: ap-northeast-1
  environment:
    TZ: Asia/Tokyo

package:
  exclude:
    - ./node_modules/** #Define where the package is

layers:
  LayersCommon:
    path: "./"  #If you put it in a folder called python, you can call it from the lambda side as a common function
    compatibleRuntimes:
      - python3.8

resources:
  Outputs:
    PythonRequirementsLambdaLayerExport:
      Value:
        Ref: PythonRequirementsLambdaLayer ##Used in the settings on the function side
    LayersCommonLambdaLayerExport:
      Value:
        Ref: LayersCommonLambdaLayer ##Used in the settings on the function side

functions/layers/package.json

{
  "name": "sample",
  "description": "",
  "version": "0.1.0",
  "dependencies": {},
  "devDependencies": {
    "serverless-python-requirements": "^5.1.0"
  }
}

functions/layers/requirements.txt

boto3
botocore
gspread
oauth2client

functions/main/serverless.yml


service: goole-test
frameworkVersion: "2"

custom:
  defaultStage: dev
  sampleS3BucketName:
    Fn::Join:
      - ""
      - - ${self:service}-
        - ${self:provider.stage}-
        - Ref: AWS::AccountId
  ##layer settings[packege]
  requirements_service: goole-test-layer
  requirements_export: PythonRequirementsLambdaLayerExport
  requirements_layer: ${cf:${self:custom.requirements_service}-${self:provider.stage}.${self:custom.requirements_export}}
  ##layer settings[common]
  layers_common_service: goole-test-layer
  layers_common_export: LayersCommonLambdaLayerExport
  layers_common: ${cf:${self:custom.layers_common_service}-${self:provider.stage}.${self:custom.layers_common_export}}

provider:
  name: aws
  runtime: python3.8
  stage: ${opt:stage, self:custom.defaultStage}
  region: ap-northeast-1
  logRetentionInDays: 30
  environment:
    KEYNAME : "/google/access_key" #Storage location of the created key
  iamRoleStatements:
    - Effect: "Allow"
      Action:
        - "s3:ListBucket"
        - "s3:GetObject"
        - "s3:PutObject"
      Resource:
        - Fn::Join: ["", ["arn:aws:s3:::", { "Ref": "S3Bucket" }]]
        - Fn::Join: ["", ["arn:aws:s3:::", { "Ref": "S3Bucket" }, "/*"]]
    - Effect: Allow
      Action:
        - secretsmanager:GetSecretValue
      Resource:
        - "*" #Permission control is possible by specifying arn of secrets manager

functions:
  google_test:
    handler: handler.google_test
    memorySize: 512
    timeout: 900
    layers:
      - ${self:custom.requirements_layer}
      - ${self:custom.layers_common}
    events:
      - s3:                 #Set the S3 create object that is often used for the time being
          bucket:
            Ref: S3Bucket
          event: s3:ObjectCreated:*
          existing: true
          rules:
            - suffix: .csv

resources:
  Resources:
    S3Bucket:                           #Create S3
      Type: AWS::S3::Bucket
      Properties:
        BucketName: ${self:custom.sampleS3BucketName}

functions/main/hander.py I wear it sideways and write it all in one module, but please divide the file for each function ...

import json
import os
import boto3
from botocore.exceptions import ClientError
import base64
import gspread
from oauth2client.service_account import ServiceAccountCredentials


def get_secret():
    #This is almost exactly the sample code when creating Secrets Manager
    try:
        secret = None
        decoded_binary_secret = None

        secret_name = os.environ['KEYNAME']
        region_name = "ap-northeast-1"

        # Create a Secrets Manager client
        session = boto3.session.Session()
        client = session.client(
            service_name='secretsmanager',
            region_name=region_name
        )

        get_secret_value_response = client.get_secret_value(
            SecretId=secret_name
        )

    except ClientError as e:
        if e.response['Error']['Code'] == 'DecryptionFailureException':
            raise e
        elif e.response['Error']['Code'] == 'InternalServiceErrorException':
            raise e
        elif e.response['Error']['Code'] == 'InvalidParameterException':
            raise e
        elif e.response['Error']['Code'] == 'InvalidRequestException':
            raise e
        elif e.response['Error']['Code'] == 'ResourceNotFoundException':
            raise e
    else:
        if 'SecretString' in get_secret_value_response:
            secret = get_secret_value_response['SecretString']
        else:
            decoded_binary_secret = base64.b64decode(
                get_secret_value_response['SecretBinary'])

    # Your code goes here.
    return decoded_binary_secret.decode()


def connect_gspread(jsonf, key):
    scope = ['https://spreadsheets.google.com/feeds',
             'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        jsonf, scope)
    gc = gspread.authorize(credentials)
    SPREADSHEET_KEY = key
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    return worksheet


def google_test(event, context):

    #Because it is passed to the API as a file/Let's output to tmp.
    jsonf = "/tmp/google-access.json"

    with open(jsonf, mode='w') as f:
        f.write(get_secret())

    spread_sheet_key = '1o3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
    ws = connect_gspread(jsonf, spread_sheet_key)

    #Put hoge1 in cell A1
    ws.update_cell(1, 1, "hoge1")

    body = {
        "message": "{} !".format("finished ."),
        "input": event
    }

    response = {
        "statusCode": 200,
        "body": json.dumps(body)
    }

    return response

Deploy

##Deploy from layers
cd functions/layers
npm install
pip install -r requirements.txt
sls deploy

##Deploy the main function
cd functions/main
sls deploy

Run! !! !!

Hoge1 has entered the A1 cell safely!

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

So I was able to update the spread sheet from AWS lambda. Well, should I write it in GAS? There is also a tsukkomi, but if you want to do something like this We would appreciate it if you could refer to it.

Have a good AWS life!

Recommended Posts

I wanted to operate google spread sheet with AWS lambda, so I tried it [Part 2]
AWS Lambda now supports Python so I tried it
There was a doppelganger, so I tried to distinguish it with artificial intelligence (laughs) (Part 1)
I tried to make a calculator with Tkinter so I will write it
I tried connecting AWS Lambda with other services
I tried to operate Linux with Discord Bot
When I tried to make a VPC with AWS CDK but couldn't make it
I tried to use Twitter Scraper on AWS Lambda and it didn't work.
I wanted to know the number of lines in multiple files, so I tried to get it with a command
I tried to get an AMI using AWS Lambda
[Introduction to AWS] I tried playing with voice-text conversion ♪
It was a life I wanted to OCR on AWS Lambda to locate the characters.
I wanted to run the motor with Raspberry Pi, so I tried using Waveshare's Motor Driver Board
Since there was a doppelganger, I tried to distinguish it with artificial intelligence (laugh) (Part 2)
[AWS / Tello] I tried operating the drone with my voice Part2
A memorandum when I tried to get it automatically with selenium
I tried to expand the database so that it can be used with PES analysis software
I tried to automate everything including Google OAuth with two-step verification
I want to send barometric pressure information (obtained by Beautiful Soup) with a graph to Slack with Python (+ I want to manage it with Google Spread Sheet)
[AWS / Tello] I tried operating the drone with my voice Part1
I tried to notify Zabbix Server of execution error of AWS Lambda function
I want to do it with Python lambda Django, but I will stop
[AWS] [GCP] I tried to make cloud services easy to use with Python
[Zaif] I tried to make it easy to trade virtual currencies with Python
I tried to make a url shortening service serverless with AWS CDK
I tried to implement Autoencoder with TensorFlow
I tried to visualize AutoEncoder with TensorFlow
I wanted to solve ABC160 with Python
I want to play with aws with python
I tried implementing DeepPose with PyTorch PartⅡ
I tried to implement CVAE with PyTorch
Connect to s3 with AWS Lambda Python
I tried to solve TSP with QAOA
I wanted to solve ABC172 with Python
I really wanted to copy with selenium
I just did FizzBuzz with AWS Lambda
I tried it with SymPy Live, Wolfram Alpha and google with reference to "Algorithm learned with Python 4th: Prime numbers".
I tried to publish my own module so that I can pip install it
When I tried to change the root password with ansible, I couldn't access it.
I tried to use Java with Termux using Termux Arch but it didn't work
When I tried to create a virtual environment with Python, it didn't work
I tried to reduce costs by starting / stopping EC2 collectively on AWS Lambda
[Introduction to AWS] I tried porting the conversation app and playing with text2speech @ AWS ♪
I tried to create an environment to check regularly using Selenium with AWS Fargate
I didn't understand the Resize of TensorFlow so I tried to summarize it visually.
I tried to make Kana's handwriting recognition Part 3/3 Cooperation with GUI using Tkinter
I tried to predict next year with AI
I tried to detect Mario with pytorch + yolov3
I tried to implement reading Dataset with PyTorch
I tried to use lightGBM, xgboost with Boruta
I tried to learn logical operations with TF Learn
I tried to move GAN (mnist) with keras
I tried "License OCR" with Google Vision API
I tried to detect motion quickly with OpenCV
I wanted to play with the Bezier curve
Serverless scraping using selenium with [AWS Lambda] -Part 1-
I wanted to install Python 3.4.3 with Homebrew + pyenv
I tried to get CloudWatch data with Python
I tried to output LLVM IR with Python
I tried to automate sushi making with python
I tried to predict Titanic survival with PyCaret