Dialogflow CX to Bigquery in GCP

[jangaganesh]

I've set up a BigQuery table with columns representing various intents used in Dialogflow CX, each stored as integers except for 'Date', which tracks specific dates. Every row in the table logs how frequently each intent was triggered on that particular day across all conversations. I'm trying to automate this logging process by implementing webhook calls for each intent. However, the code I've written for this isn't working as expected. Any guidance or assistance would be greatly appreciated.  (Note: I had give required permissions to edit bigquery table in my service account, and also given permission to service account in Bigquery to edit things)

from google.cloud import bigquery
import datetime
import functions_framework

client = bigquery.Client()

# Use your BigQuery dataset values:
dataset_id = 'XXXXXXXXXX' 
table_id = 'XXXXXXXXXXXX'

@functions_framework.http
def intent_updater(request):
    request_json = request.get_json(silent=True)

    # Extract detected intent
    detected_intent = request_json.get('intentInfo', {}).get('displayName', None)
    
    if not detected_intent:
        return "No intent detected or invalid request format", 400

    # Prepare data for BigQuery update
    date = datetime.datetime.utcnow().date()
    date_str = date.isoformat()

    column_name_to_update = detected_intent

    # Check if the column name is valid
    valid_columns = ['Default Welcome Intent', 'Email', 'Name', 'Phone number', 'End']
    if column_name_to_update not in valid_columns:
        return f"Invalid column name: {column_name_to_update}", 400

    # Check if the date already exists in the table
    query_check_date = f"""
    SELECT COUNT(*) as count
    FROM `{table_id}`
    WHERE Date = '{date_str}'
    """

    query_job = client.query(query_check_date)
    results = query_job.result()
    date_exists = any(row['count'] > 0 for row in results)
  
    if date_exists:
        # Update the existing row
        query_update = f"""
        UPDATE `{table_id}`
        SET `{column_name_to_update}` = `{column_name_to_update}` + 1
        WHERE Date = '{date_str}'
        """

        query_job = client.query(query_update)
        query_job.result()  # Waits for the query to finish
        return f"Updated {column_name_to_update} for date {date_str}"

    else:
        # Insert a new row with the current date and initialize values
        query_insert = f"""
        INSERT INTO `{table_id}` (Date, `Default Welcome Intent`, Email, Name, `Phone number`, End)
        VALUES (
            '{date_str}', 
            0, 
            0, 
            0, 
            0, 
            0
        )
        """
        
        query_job = client.query(query_insert)
        query_job.result()  # Waits for the query to finish

        # Update the inserted row
        query_update_new_row = f"""
        UPDATE `{table_id}`
        SET `{column_name_to_update}` = `{column_name_to_update}` + 1
        WHERE Date = '{date_str}'
        """

        query_job = client.query(query_update_new_row)
        query_job.result()  # Waits for the query to finish
        return f"Inserted and updated {column_name_to_update} for date {date_str}"