googleworkspace · grant · Aug 3, 2018 · Jul 17, 2018 · Jul 17, 2018 · Jul 17, 2018
diff --git a/events/next18/README.md b/events/next18/README.md
@@ -0,0 +1,48 @@
+# Quarterly business review demo
+
+This sample was created for a talk for Google Cloud NEXT'18 entitled "Building
+on the Docs Editors: APIs and Apps Script". It is the implementation of a
+commandline tool that:
+
+* Extracts template variables out of a Google Slides template presentation
+* Writes those variables to a Google Sheets spreadsheet
+* Adds data to the spreadsheet based on those variables from a stub data service
+* Generates new Google Slides presentations using the template and the
+  spreadsheet data
+
+## Getting started
+
+* Follow the [Sheets API python quickstart](https://developers.google.com/sheets/api/quickstart/python)
+  * Make sure to save the `credentials.json` file in your working directory
+* Enable the Google Slides API, Google Drive API and Google Sheets API in your
+  developer project
+* Run the tool with no arguments to complete the OAuth consent flow:
+
+```bash
+$ python qbr_tool.py
+```
+
+* Run the tool:
+
+```bash
+# Create the spreadsheet from the Google Slides template.
+# For example, 13My9SxkotWssCc2F5yaXp2fzGrzoYV6maytr3qAT9GQ
+$ python qbr_tool.py create_sheet --template_id <your template id>;
+
+# Outputs:
+# Spreadsheet URL: https://docs.google.com/spreadsheets/d/<spreadsheet id>
+
+# Add data from the stub customer service
+$ python qbr_tool.py add_customers \
+    --spreadsheet_id <spreadsheet id> \
+    --customer_id jupiter
+
+# Generate the filled in presentation
+$ python qbr_tool.py create_presentations
+    --spreadsheet_id <spreadsheet id> \
+    --customer_id jupiter
+
+# Outputs:
+# jupiter: https://docs.google.com/presentations/d/<filled in presentation id>
+```
+
diff --git a/events/next18/customer_data_service.py b/events/next18/customer_data_service.py
@@ -0,0 +1,127 @@
+# Copyright 2018 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
+#
+#     http://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.
+
+# pylint: disable=E1102
+# python3
+"""Retrieves customer data from our companies "internal" customer data serivce.
+
+This data service is meant to be illustrative for demo purposes, as its just
+hard coded data. This can be any internal or on-premise data store.
+"""
+
+
+class CustomerDataService(object):
+    _CUSTOMER_DATA = {
+        'mars': {
+            'customer_name': 'Mars Inc.',
+            'customer_logo':
+                'https://upload.wikimedia.org/wikipedia/commons/thumb/0/02/' +
+                'OSIRIS_Mars_true_color.jpg/550px-OSIRIS_Mars_true_color.jpg',
+            'curr_q': 'Q2',
+            'curr_q_total_sales': '$2,532,124',
+            'curr_q_qoq': '0.054',
+            'prev_q': 'Q1',
+            'prev_q_total_sales': '$2,413,584',
+            'next_q': 'Q3',
+            'next_q_total_sales_proj': '$2,634,765',
+            'next_q_qoq_proj': '0.041',
+            'top1_sku': 'Phobos',
+            'top1_sales': '$334,384',
+            'top2_sku': 'Deimos',
+            'top2_sales': '$315,718',
+            'top3_sku': 'Charon',
+            'top3_sales': '$285,727',
+            'top4_sku': 'Nix',
+            'top4_sales': '$264,023',
+            'top5_sku': 'Hydra',
+            'top5_sales': '$212,361',
+        },
+        'jupiter': {
+            'customer_name': 'Jupiter LLC',
+            'customer_logo':
+                'https://upload.wikimedia.org/wikipedia/commons/thumb/2/2b/' +
+                'Jupiter_and_its_shrunken_Great_Red_Spot.jpg/660px-Jupiter_' +
+                'and_its_shrunken_Great_Red_Spot.jpg',
+            'curr_q': 'Q2',
+            'curr_q_total_sales': '$1,532,124',
+            'curr_q_qoq': '0.031',
+            'prev_q': 'Q1',
+            'prev_q_total_sales': '$1,413,584',
+            'next_q': 'Q3',
+            'next_q_total_sales_proj': '$1,634,765',
+            'next_q_qoq_proj': '0.021',
+            'top1_sku': 'Io',
+            'top1_sales': '$234,384',
+            'top2_sku': 'Europa',
+            'top2_sales': '$215,718',
+            'top3_sku': 'Ganymede',
+            'top3_sales': '$185,727',
+            'top4_sku': 'Callisto',
+            'top4_sales': '$164,023',
+            'top5_sku': 'Amalthea',
+            'top5_sales': '$112,361',
+        },
+        'saturn': {
+            'customer_name': 'Saturn',
+            'customer_logo':
+                'https://upload.wikimedia.org/wikipedia/commons/thumb/c/c7/' +
+                'Saturn_during_Equinox.jpg/800px-Saturn_during_Equinox.jpg',
+            'curr_q': 'Q2',
+            'curr_q_total_sales': '$2,532,124',
+            'curr_q_qoq': '0.032',
+            'prev_q': 'Q1',
+            'prev_q_total_sales': '$2,413,584',
+            'next_q': 'Q3',
+            'next_q_total_sales_proj': '$2,634,765',
+            'next_q_qoq_proj': '0.029',
+            'top1_sku': 'Mimas',
+            'top1_sales': '$334,384',
+            'top2_sku': 'Enceladus',
+            'top2_sales': '$315,718',
+            'top3_sku': 'Tethys',
+            'top3_sales': '$285,727',
+            'top4_sku': 'Dione',
+            'top4_sales': '$264,023',
+            'top5_sku': 'Rhea',
+            'top5_sales': '$212,361',
+        },
+        'neptune': {
+            'customer_name': 'Neptune',
+            'customer_logo':
+                'https://upload.wikimedia.org/wikipedia/commons/thumb/5/56/' +
+                'Neptune_Full.jpg/600px-Neptune_Full.jpg',
+            'curr_q': 'Q2',
+            'curr_q_total_sales': '$2,532,124',
+            'curr_q_qoq': '0.027',
+            'prev_q': 'Q1',
+            'prev_q_total_sales': '$2,413,584',
+            'next_q': 'Q3',
+            'next_q_total_sales_proj': '$2,634,765',
+            'next_q_qoq_proj': '0.039',
+            'top1_sku': 'Triton',
+            'top1_sales': '$334,384',
+            'top2_sku': 'Nereid',
+            'top2_sales': '$315,718',
+            'top3_sku': 'Naiad',
+            'top3_sales': '$285,727',
+            'top4_sku': 'Thalassa',
+            'top4_sales': '$264,023',
+            'top5_sku': 'Despina',
+            'top5_sales': '$212,361',
+        },
+    }
+
+    def GetCustomerData(self, customer_id, properties):
+        customer_data = self._CUSTOMER_DATA[customer_id]
+        return [customer_data[p.lower()] for p in properties]
diff --git a/events/next18/customer_spreadsheet_reader.py b/events/next18/customer_spreadsheet_reader.py
@@ -0,0 +1,74 @@
+# Copyright 2018 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
+#
+#     http://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.
+
+# pylint: disable=E1102
+# python3
+"""Reads the customer data from the template spreadsheet."""
+
+import collections
+
+
+class CustomerSpreadsheetReader(object):
+
+    def __init__(self, sheets_service, spreadsheet_id):
+        self._sheets_service = sheets_service
+        self._spreadsheet_id = spreadsheet_id
+        self._data_filters = collections.OrderedDict()
+
+    def ReadColumnData(self, column_id):
+        data_filter = {
+            'developerMetadataLookup': {
+                'metadataKey': 'column_id',
+                'metadataValue': column_id,
+            }
+        }
+        self._data_filters[column_id] = data_filter
+
+    def ExecuteRead(self):
+        filters = list(self._data_filters.values())
+        get_body = {'dataFilters': filters}
+        read_fields = ','.join([
+            'sheets.properties.sheetId',
+            'sheets.data.rowData.values.formattedValue',
+            'developerMetadata.metadataValue'])
+        spreadsheet = self._sheets_service.spreadsheets().getByDataFilter(
+            spreadsheetId=self._spreadsheet_id, body=get_body,
+            fields=read_fields).execute()
+        customer_spreadsheet = CustomerSpreadsheet(
+            spreadsheet, self._data_filters)
+        self._data_filters = collections.OrderedDict()
+        return customer_spreadsheet
+
+
+class CustomerSpreadsheet(object):
+
+    def __init__(self, spreadsheet, data_filters):
+        self._spreadsheet = spreadsheet
+        self._data_filters = data_filters
+
+    def GetSheetId(self):
+        sheet = self._spreadsheet.get('sheets')[0]
+        return sheet.get('properties').get('sheetId')
+
+    def GetTemplateId(self):
+        metadata = self._spreadsheet.get('developerMetadata')[0]
+        return metadata.get('metadataValue')
+
+    def GetColumnData(self, column_id):
+        index = list(self._data_filters.keys()).index(column_id)
+        data = self._spreadsheet.get('sheets')[0].get('data')[index]
+        values = [row.get('values')[0].get('formattedValue')
+                  for row in data.get('rowData')]
+        # Remove the first value which is just the label
+        return values[1:]
diff --git a/events/next18/presentation_reader.py b/events/next18/presentation_reader.py
@@ -0,0 +1,77 @@
+# Copyright 2018 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
+#
+#     http://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.
+
+# pylint: disable=E1102
+# python3
+"""Reads presentation data.
+
+Retrieves a presentation and extracts the placeholders and the presentation's
+title.
+"""
+import re
+
+
+class PresentationReader(object):
+
+    def __init__(self, slides_service, presentation_id):
+        self._slides_service = slides_service
+        self._presentation_id = presentation_id
+        self._presentation = None
+
+    def _InitPresentation(self):
+        if not self._presentation:
+            self._presentation = self._slides_service.presentations().get(
+                presentationId=self._presentation_id).execute()
+
+    def GetTitle(self):
+        self._InitPresentation()
+        return self._presentation.get('title')
+
+    def GetAllPlaceholders(self):
+        self._InitPresentation()
+        slides = self._presentation.get('slides')
+        placeholders = []
+        for slide in slides:
+            elements = slide.get('pageElements')
+            for element in elements:
+                shape = element.get('shape')
+                table = element.get('table')
+                # Skip page elements that aren't shapes or tables since they're
+                # the only types that support text.
+                if not shape and not table:
+                    continue
+                if shape:
+                    placeholders += self._GetPlaceholdersFromText(
+                        shape.get('text'))
+                elif table:
+                    rows = table.get('tableRows')
+                    for row in rows:
+                        cells = row.get('tableCells')
+                        for cell in cells:
+                            placeholders += self._GetPlaceholdersFromText(
+                                cell.get('text'))
+        # Return the unique placeholders
+        seen = set()
+        return [p for p in placeholders if not (p in seen or seen.add(p))]
+
+    def _GetPlaceholdersFromText(self, text):
+        if not text:
+            return []
+        placeholders = []
+        elements = text.get('textElements')
+        for element in elements:
+            if element.get('textRun'):
+                content = element.get('textRun').get('content')
+                placeholders += re.findall('{.*?}', content)
+        return placeholders
diff --git a/events/next18/presentation_writer.py b/events/next18/presentation_writer.py
@@ -0,0 +1,60 @@
+# Copyright 2018 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
+#
+#     http://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.
+
+# pylint: disable=E1102
+# python3
+"""Functionality for writing to a presentation.
+"""
+
+
+class PresentationWriter(object):
+    """Queues writes for modifying a presentation.
+
+    Call ExecuteBatchUpdate to flush pending writes.
+    """
+
+    def __init__(self, slides_service, presentation_id):
+        self._slides_service = slides_service
+        self._presentation_id = presentation_id
+        self._requests = []
+
+    def ReplaceAllText(self, find_text, replace_text):
+        request = {
+            'replaceAllText': {
+                'replaceText': replace_text,
+                'containsText': {
+                    'text': find_text,
+                    'matchCase': True
+                }
+            }
+        }
+        self._requests.append(request)
+
+    def ReplaceAllShapesWithImage(self, find_text, image_url):
+        request = {
+            'replaceAllShapesWithImage': {
+                'imageUrl': image_url,
+                'containsText': {
+                    'text': find_text,
+                    'matchCase': True
+                }
+            }
+        }
+        self._requests.append(request)
+
+    def ExecuteBatchUpdate(self):
+        body = {'requests': self._requests}
+        self._requests = []
+        self._slides_service.presentations().batchUpdate(
+            presentationId=self._presentation_id, body=body).execute()