Code for a Google Cloud Function that handles chat messages to a Hangouts Chat API endpoint.

01_gcf_readme.md

README: Google Cloud Function Demo

The following files can be used in Google Cloud Functions (GCF). Each GCF should consist of a file named "main.py", a file named "requirements.txt", and an OAuth keyfile with the extension ".json". All GCFs should have handle_request as the "Function to execute".

Report Bot Configuration

GCF Trigger Type:
HTTP

Required environment variables:
GCP_PROJECT_ID - the id of your Google Cloud Platform project to which the GCF belongs
PUBSUB_TOPIC_NAME - the Cloud Pub/Sub topic name that triggers the report
NETWORK_CODE - the Ad Manager network code on which to run the report
KEYFILE - the filename of the OAuth keyfile to be used for authentication

Create Report Sheet Configuration

GCF Trigger Type:
Cloud Pub/Sub

Required environment variables:
DESTINATION_DRIVE_FOLDER_ID - the id of the drive folder in which to put the generated report
NETWORK_CODE - the Ad Manager network code on which to run the report
KEYFILE - the filename of the OAuth keyfile to be used for authentication

Update Line Items Configuration

GCF Trigger Type:
Cloud Pub/Sub

Required environment variables:
CLOUDSQL_PASSWORD - password for the Cloud SQL database
CLOUDSQL_USER - user with which to acces the Cloud SQL database
CLOUDSQL_INSTANCE_CONNECTION - the name of the Cloud SQL instance (found under "Connect to this instance" on the "Instance details" page)
CLOUDSQL_DATABASE - the name of the database where the LineItems table is
APPLICATION_NAME - the application name with which to access the Ad Manager API
NETWORK_CODE - the Ad Manager network code to fetch the LineItems
KEY_FILE - the filename of the OAuth keyfile to be used for authentication

02_report_bot_main.py

# Copyright 2019 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

from googleads import ad_manager
from googleads import oauth2
from google.oauth2 import service_account
from google.cloud import pubsub_v1

import json
import tempfile
import csv
import gzip
import base64
from os import getenv

def handle_request(request):
    """Request handler for Ad Manager Report Bot"""
    try:
        request = request.get_json()

        # Fetch credentials and set up all necessary services.
        oauth2_client = oauth2.GoogleServiceAccountClient(getenv('KEYFILE'), oauth2.GetAPIScope('ad_manager'))
        credentials = service_account.Credentials.from_service_account_file(getenv('KEYFILE'))
        ad_manager_client = ad_manager.AdManagerClient(oauth2_client, 'google-cloud-function-report-bot', getenv('NETWORK_CODE'))
        report_service = ad_manager_client.GetService("ReportService")
        publisher = pubsub_v1.PublisherClient()
        topic_path = publisher.topic_path(getenv('GCP_PROJECT_ID'), getenv('PUBSUB_TOPIC_NAME'))

        # Respond to the chat request depending on the nature of the incoming message.
        if (request.get('type') == "CARD_CLICKED" and request.get('action', {}).get('actionMethodName') == 'run_saved_query'):
            saved_query_id = int(request.get('action').get('parameters')[0].get('value'))
            message_future = publisher.publish(topic_path, data=base64.b64encode(json.dumps({
                'saved_query_id': saved_query_id,
                'chat_thread': request.get('message', {}).get('thread', {}),
                'space': request.get('space', {})
            }).encode('utf-8')))

            response = {
                'text': 'Running your report. This may take a few minutes.'
            }
        elif (request.get('type') == 'MESSAGE' and request.get('space', {}).get('type') == 'DM' or
              request.get('type') == 'MESSAGE' and reportBotWasMentioned(request)):
            statement = ad_manager.StatementBuilder()

            saved_query_response = report_service.getSavedQueriesByStatement(
                statement.ToStatement())

            if 'results' in saved_query_response and len(saved_query_response['results']):
                saved_queries = saved_query_response['results']
                response = {
                    'cards': [{
                        'header': {
                            'title': 'Report Bot',
                            'imageUrl': 'https://www.gstatic.com/admanager/logo_admanager_2x.png',
                            'imageStyle': 'IMAGE'
                        },
                        'sections': [{
                            'widgets': [{
                                'textParagraph': { 'text': 'Select a report to run.' },
                                'buttons': list([{'textButton': 
                                                {
                                                    'text': saved_query['name'],
                                                    'onClick': {
                                                        'action': {
                                                            'actionMethodName': 'run_saved_query',
                                                            'parameters': [{
                                                                'key': 'saved_query_id',
                                                                'value': saved_query['id']
                                                            }]
                                                        }
                                                    }
                                                }
                                            } for saved_query in saved_queries])
                            }]
                        }]
                    }]
                }                         
            else:
                raise 
        else:
            raise
    except:
        response = {
            'text': 'Something went wrong. Please try again later.'
        }
    
    return json.dumps(response)

def reportBotWasMentioned(request):
    annotations = request.get('message', {}).get('annotations', [])
    return any([annotation.get('userMention', {}).get('user', {}).get('displayName') == 'Report Bot' for annotation in annotations])

03_create_report_sheet_main.py

# Copyright 2019 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

from googleads import ad_manager
from googleads import oauth2
from google.oauth2 import service_account
from googleapiclient.discovery import build
from os import getenv
import json
import tempfile
import csv
import gzip
import base64
import datetime

def handle_request(data, context):
    """Request handler for Report Bot Pub/Sub message to create sheet from provided saved_query_id"""
  
    # Fetch credentials and set up all necessary services.
    oauth2_client = oauth2.GoogleServiceAccountClient(getenv('KEYFILE'), oauth2.GetAPIScope('ad_manager'))
    credentials = service_account.Credentials.from_service_account_file(getenv('KEYFILE'))
    ad_manager_client = ad_manager.AdManagerClient(oauth2_client, "google-cloud-function-report-bot", getenv('NETWORK_CODE'))
    sheets_service = build('sheets', 'v4', credentials=credentials.with_scopes(['https://www.googleapis.com/auth/spreadsheets']))
    drive_service = build('drive', 'v3', credentials=credentials.with_scopes(['https://www.googleapis.com/auth/drive']))
    chat_service = build('chat', 'v1', credentials=credentials.with_scopes(['https://www.googleapis.com/auth/chat.bot']))    
    SHEETS_URL = 'https://docs.google.com/spreadsheets/d/'
    
    data = json.loads(base64.b64decode(base64.b64decode(data['data']).decode('utf-8')).decode('utf-8'))  
    report_file_compressed = run_saved_query(ad_manager_client, data['saved_query_id'])
    
    if report_file_compressed is not None:
        report_file = gzip.open(report_file_compressed.name, 'rt')
        spreadsheet_body = csv_to_spreadsheet(report_file)
        report_file.close()

        sheets_response = sheets_service.spreadsheets().create(body=spreadsheet_body).execute()

        drive_response = drive_service.files().update(
            fileId=sheets_response['spreadsheetId'], addParents=getenv('DESTINATION_DRIVE_FOLDER_ID')
        ).execute()  

        response = {
            'text': "Here's the link to the report you requested: %s" % (SHEETS_URL + drive_response['id']),
            'thread': data.get('chat_thread')
        }

        chat_service.spaces().messages().create(
            parent=data.get('space').get('name'),
            body=response).execute()
            
def run_saved_query(ad_manager_client, saved_query_id):
    report_service = ad_manager_client.GetService('ReportService')
    report_downloader = ad_manager_client.GetDataDownloader()
    
    statement = (ad_manager.StatementBuilder()
           .Where('id = :id')
           .WithBindVariable('id', saved_query_id)
           .Limit(1))

    saved_query_response = report_service.getSavedQueriesByStatement(
        statement.ToStatement())

    if 'results' in saved_query_response and len(saved_query_response['results']):
        saved_query = saved_query_response['results'][0]

        if saved_query['isCompatibleWithApiVersion']:
            report_job = {}
            report_job['reportQuery'] = saved_query['reportQuery']
            try:
                # Run the report and wait for it to finish.
                report_job_id = report_downloader.WaitForReport(report_job)
            except errors.AdManagerReportError as e:
                return None
            # Change to your preferred export format.
            export_format = 'CSV_DUMP'
            report_file = tempfile.NamedTemporaryFile(suffix='.csv.gz', delete=False)

            # Download report data.
            report_downloader.DownloadReportToFile(
                report_job_id, export_format, report_file)
            report_file.close()
            return report_file
    return None

def csv_to_spreadsheet(csv_file):
    rows = csv.reader(csv_file, delimiter=',')
    return {
        'properties': {
            'title': 'Ad Manager Report (' + str(datetime.datetime.now()) + ')'
        },
        'sheets': [{
            'data': [{
                'startRow': 0,
                'startColumn': 0,
                'rowData': [{
                    'values': [{
                        'userEnteredValue': {'stringValue': cell}
                    } for cell in row]
                } for row in rows]
            }]
        }]                    
    }

04_update_line_items_main.py

# Copyright 2019 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import datetime
from googleads import ad_manager
from googleads import oauth2
from google.oauth2 import service_account
from google.cloud import bigquery
from os import getenv
import pymysql
import pytz

def handle_request(data, context):
    """Background Cloud Function to be triggered by Pub/Sub topic <fetch_line_items>."""
    
    # Step 1: Authenticate and load LineItemService
    oauth2_client = oauth2.GoogleServiceAccountClient(getenv('KEY_FILE'), oauth2.GetAPIScope('ad_manager'))
    ad_manager_client = ad_manager.AdManagerClient(oauth2_client, getenv('APPLICATION_NAME'), getenv('NETWORK_CODE'))
    line_item_service = ad_manager_client.GetService('LineItemService')
    
    # Step 2: Specify SQL config and connect
    CONNECTION_NAME = getenv('CLOUDSQL_INSTANCE_CONNECTION')
    DB_USER = getenv('CLOUDSQL_USER')
    DB_PASSWORD = getenv('CLOUDSQL_PASSWORD')
    DB_NAME = getenv('CLOUDSQL_DATABASE')
    
    mysql_config = {
      'unix_socket': f'/cloudsql/{CONNECTION_NAME}',
      'user': DB_USER,
      'password': DB_PASSWORD,
      'db': DB_NAME,
      'charset': 'utf8mb4',
      'cursorclass': pymysql.cursors.DictCursor,
      'autocommit': True
    }
    
    mysql_conn = pymysql.connect(**mysql_config)
        
    with mysql_conn.cursor() as cursor:
        # Step 3: Query database for the latest recorded modified date time
        cursor.execute('SELECT lastModifiedDateTime FROM LineItems ' +
                       'ORDER BY lastModifiedDateTime DESC LIMIT 1;')
        result = cursor.fetchone()
        if result is not None:    
            latestModifiedDateTime = pytz.timezone('UTC').localize(result['lastModifiedDateTime']) + datetime.timedelta(days=-1)
            statement = (ad_manager.StatementBuilder()
                         .Where('lastModifiedDateTime > :lastModifiedDateTime')
                         .WithBindVariable('lastModifiedDateTime', latestModifiedDateTime))
        else:
            statement = ad_manager.StatementBuilder()
        
        # Step 4: Query database for the columns
        cursor.execute('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="LineItems";')
        columns = [row['COLUMN_NAME'] for row in cursor.fetchall()]
        
        # Step 5: Fetch recently updated LineItems and update database
        while True:
            response = line_item_service.getLineItemsByStatement(statement.ToStatement())
            if 'results' in response and len(response['results']):
                line_items = response['results']
                cursor.execute('INSERT INTO LineItems '
                    '(' + ','.join(columns) + ') '
                    'VALUES ' + generate_sql_values(line_items, columns) + ' '
                    'ON DUPLICATE KEY UPDATE'
                    ' ' + generate_sql_columns(columns) + ';'
                )
                statement.offset += statement.limit
            else:
                break    
                
    return {'message': 'Number of LineItems updated: ' + str(response['totalResultSetSize'])}
                
def generate_sql_columns(columns):
    return ', '.join([column + ' = VALUES(' + column + ')' for column in columns])
    
def generate_sql_values(line_items, columns):
    sql_values = ('(' +
        '),('.join([
            ', '.join([convert_value_for_sql_query(line_item[column]) for column in columns]) 
            for line_item in line_items]
        ) + ')')
    return sql_values
                               
def convert_value_for_sql_query(ad_manager_value):
    value_type = type(ad_manager_value)
    date_keys = ['year', 'month', 'day']
    datetime_keys = ['hour', 'minute', 'second']
    if ad_manager_value is None:
        return 'NULL'
    elif value_type == str:
        return '"' + str(ad_manager_value).replace('"', '\\"') + '"'
    elif str(value_type) == "<class 'zeep.objects.Date'>":
        return '"' + datetime.date(*[ad_manager_value[key] for key in date_keys]).isoformat() + '"'
    elif str(value_type) == "<class 'zeep.objects.DateTime'>":
        datetime_value = datetime.datetime(
            ad_manager_value['date']['year'], ad_manager_value['date']['month'], ad_manager_value['date']['day'], 
            ad_manager_value['hour'], ad_manager_value['minute'], ad_manager_value['second'], tzinfo=pytz.timezone(ad_manager_value['timeZoneId'])
        )
        utc_datetime_value = datetime_value.astimezone(pytz.timezone('UTC'))
        utc_datetime_value = datetime.datetime(utc_datetime_value.year, utc_datetime_value.month, utc_datetime_value.day, utc_datetime_value.hour, utc_datetime_value.minute, utc_datetime_value.second)
        return '"' + utc_datetime_value.isoformat() + '"'
    else:
        return str(ad_manager_value).replace('"', '\\"')

05_requirements.txt

# Function dependencies, for example:
# package>=version
googleads>=17.0.0
google-api-python-client>=1.7.8
google-auth>=1.6.3
google-cloud-bigquery>=1.11.2
google-cloud-pubsub>=0.40.0
pymysql>=0.9.3