How to handle method 'post' in google cloud function?

458 views Asked by At

I'm trying to create a cloud function which goes to the certain google sheet and collects necessary data, after that, it connects with bigquery database and writes down data to a bigquery table. When I run apps script, which trigers cloud function, I get next message - Error: could not handle the request

The code from the cloud function (main.py)

from __future__ import print_function

import json
import os.path
import pickle

import functions_framework
import requests
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from google.auth.transport.requests import Request
from google.cloud import bigquery


class GoogleSheetService:
    # The settings of the particular google table
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    TOKEN_PICKLE = 'settings/token.pickle'
    service = None

    # The settings for the bigquery service
    credentials_path = 'settings/pythonbq.privateKey.json'
    os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = credentials_path
    client = bigquery.Client()
    table_id = 'cobalt-alliance-365419.BTC_Daily.table-btc'

    DATA = []

    def __init__(self):
        creds = None
        if os.path.exists(self.TOKEN_PICKLE):
            with open(self.TOKEN_PICKLE, 'rb') as token:
                creds = pickle.load(token)
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file(
                    'settings/credentials.json', self.SCOPES)
                creds = flow.run_local_server(port=0)
            with open(self.TOKEN_PICKLE, 'wb') as token:
                pickle.dump(creds, token)
        self.service = build('sheets', 'v4', credentials=creds)

    def get_data(self, spreadsheet_id, range_name):
        sheet = self.service.spreadsheets()
        result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
        self.DATA = result.get('values', [])

    def get_row(self, data_of_column):
        r = []
        for row in self.DATA:
            if data_of_column == 'date':
                r.append(row[1].replace('0:00:00', '').rstrip())
            if data_of_column == 'symbol':
                r.append(row[2])
            if data_of_column == 'volume_BTC':
                r.append(float(row[4]))
            if data_of_column == 'volume_USD':
                r.append(float(row[5]))
        return r

    def sample_data(self, row1=None, row2=None, row3=None, row4=None):
        return {u'date': f'{row1}', u'symbol': f'{row2}', u'volume_BTC': f'{row3}', u'volume_USD': f'{row4}'}

    def write_data(self):
        rows_array = []
        number_of_rows = len(self.DATA)
        for i in range(number_of_rows):
            rows_array.append(self.sample_data(self.get_row('date')[i], self.get_row('symbol')[i],
                                               self.get_row('volume_BTC')[i], self.get_row('volume_USD')[i]))
        return rows_array

    def write_to_db(self):
        rows_to_insert = self.write_data()
        if not rows_to_insert:
            return 'Data is empty'
        errors = self.client.insert_rows_json(self.table_id, rows_to_insert)
        if not errors:
            return f'New rows have been added.'
        else:
            return f'Encountered errors while inserting rows: {errors}'


@functions_framework.http
def main(request):
    gs = GoogleSheetService()
    if requests.method == "GET":
        gs.get_data('164RTnYK49DvV2Ion45JHMCFQa8S', 'A2:F100')
        data_json = json.dumps(gs.DATA)
        data = {'data_json': data_json}
        return requests.get(data=data)
    elif requests.method == "POST":
        gs.get_data('164RTnYK49DvV2Ion45JHMCFQa8S', 'A2:F100')
        gs.write_to_db()

Apps script


function callCloudRun() {
  const token = ScriptApp.getIdentityToken();var options = {
    'method' : 'post',
    'headers': {'Authorization': 'Bearer ' + token},
  };
  options = {muteHttpExceptions: true};
  var response = UrlFetchApp.fetch(CLOUD_RUN_URL, options);
  Logger.log(response.getContentText()); 
}
0

There are 0 answers