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());
}