Database với Google sheets

AWSTemplateFormatVersion: "2010-09-09"
Transform: AWS::Serverless-2016-10-31
Description: >
  currency-crawler

  Sample SAM Template for currency-crawler

# More info about Globals: https://github.com/awslabs/serverless-application-model/blob/master/docs/globals.rst
Globals:
  Function:
    Timeout: 300
    Environment:
      Variables:
        SPREADSHEET_ID: '1nzKN3NSTh_AVQlSOm5Zv4CQZlejkPpHw3FkOm6T1zio'
        RANGE_NAME: 'A1:H1'
        VALUE_INPUT_OPTION: 'USER_ENTERED'


Resources:
  CurrencyCrawlFunction:
    Type: AWS::Serverless::Function # More info about Function Resource: https://github.com/awslabs/serverless-application-model/blob/master/versions/2016-10-31.md#awsserverlessfunction
    Properties:
      CodeUri: currency/
      Handler: app.crawl
      Runtime: python3.9
      Architectures:
        - x86_64
      Events:
        InvocationLevel:
          Type: Schedule
          Properties:
            Schedule: cron(0 11 ? * MON-FRI *) # All scheduled events use UTC+0 time zone

Outputs:
  CurrencyCrawlFunction:
    Description: "Currency Crawler Lambda Function ARN"
    Value: !GetAtt CurrencyCrawlFunction.Arn
  CurrencyCrawlFunctionIamRole:
    Description: "Implicit IAM Role created for Currency Crawler function"
    Value: !GetAtt CurrencyCrawlFunctionRole.Arn

from datetime import date
from mbb import get_record as get_mbb_record
from scb import get_record as get_scb_record
from bidv import get_record as get_bidv_record
import json
from config import logger
from utils import append_to_db, format_record

def crawl(event, context):
    try:
        requested_date_str = date.today().strftime("%d/%m/%Y")
        mbb_record = get_mbb_record(requested_date_str)
        append_to_db(format_record(mbb_record))

        bidv_record = get_bidv_record(requested_date_str)
        append_to_db(format_record(bidv_record))

        scb_record = get_scb_record(requested_date_str)
        append_to_db(format_record(scb_record))

        logger.info("Inserted to Google sheet")
    except Exception as e:
        logger.error(e)
        raise e

    return {
        "statusCode": 200,
        "body": json.dumps({
            "message": "Done! Currencies data imported",
        }),
    }

record[currency] = Decimal(ck) -> record[currency] = float(ck)

requests
bs4
google-api-python-client
google-auth-httplib2
google-auth-oauthlib

import os
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = './credentials.json'
SPREADSHEET_ID = os.environ.get('SPREADSHEET_ID')
RANGE_NAME = os.environ.get('RANGE_NAME')
VALUE_INPUT_OPTION = os.environ.get('VALUE_INPUT_OPTION')

def append_to_db(values):
    creds, _ = google.auth.default()
    try:
        service = build('sheets', 'v4', credentials=creds)
        # ["2022/03/17", "MBB", "23020", "25876", "198.31", "16962", "2996", "17169"]
        body = {
            'values': [values]
        }
        result = service.spreadsheets().values().append(
            spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,
            valueInputOption=VALUE_INPUT_OPTION, body=body).execute()
        print(f"{(result.get('updates').get('updatedCells'))} cells appended.")
        return result

    except HttpError as error:
        print(f"An error occurred: {error}")
        return error

# date bank USD	EUR	JPY	AUD	HKD	SGD
def format_record(record):
    arr = []
    arr.append(record['date'])
    arr.append(record['bank'])
    arr.append(record['USD'])
    arr.append(record['EUR'])
    arr.append(record['JPY'])
    arr.append(record['AUD'])
    arr.append(record['HKD'])
    arr.append(record['SGD'])
    return arr

# test function
sam local invoke "CurrencyCrawlFunction" -e events/event.json

# validate SAM template
sam validate --profile vntechies --region ap-southeast-1

# build package
sam build

# deploy
sam deploy --guided --profile vntechies