Automating G Sheets to G Docs Report

Requirements:

1. Automate the process of generating reports.

2. The folder of Google Sheets containing graphs & without graphs, and the automation will extract the graphs from these Sheets and populate them into a Google Doc template.

3. Also want a way of extracting the data from Sheets and generating a written summary of the information in the graphs using these figures.

4. Some files will have no graphs but still include valuable data and these to be linked to at the end of the generated report document.

5. The tool should be able to work the same regardless of whether there are 5 websites or 2. Each website is represented in multiple reports so we need some conditional logic.

 

Challenges:

There were a lot challenges since there were many different sections on the template associated with a specific sheet, but I will just write below about some of the top challenges:

  1. Exporting Graph as is from the spreadsheet was messing up with the original graph color and layout. So I had to first export them as an image and then grab that image onto the document.
  2. There were few section where the replaceable values were in the form of a table and that were to prioritize based on a website value such as ascending order or descending order. To get an idea of that please see "Bounce Rate by Channel" title from the generated report.

Result:

Document Template: https://docs.google.com/document/d/1byBpVmuaur3oAA1jKt2IyiQihrPkV_Xv2OTHq_BjiR4/edit

Generated Report: https://docs.google.com/document/d/14yjEydV0C4QEDaBkdrpNP2njqIwsLRmubNL1fFNMTyc/edit

Description

Automate the process of generating reports from 24+ google spreadsheets to google documents.