We’re excited to announce that our “Hack” for the Google Cloud BI Hackathon 2022 is now ready for the Looker Studio community. The speciality of this community connector is that we can get the maximum use of Google Sheets data modelling features right inside Looker Studio.

You can access our deployment of the Google Sheets What-If Community Connector by clicking here!

We will go over some example use cases for this Community Connector in the following sections. These would include demo dashboards that we created & also a quick step-by-step tutorial on how you can get started.

Scenario manager for Looker Studio allows you to change or substitute input values for multiple cells in a Google Sheet (in this Hackathon we have limited it to 6, but we can make it to something like 32 cells just like in Microsoft Excel). Our hack was to create a Looker Studio Community Connector which will help you view the results of different input values or different scenarios at the same time. These input values (parameters) will be passed to a Google Sheet which will then do a series of complex calculations before it returns data back to Looker Studio. Some refer to this as What-If analysis as well.

To use our Google Sheets What-If connector we basically have to follow the following 3 steps:

1. We have to create a Google Sheets spreadsheet model with various different calculations. These calculations can be anything from simple adding or subtracting to complex LAMBDA functions.

2. Once we create the Google Sheets model, now we're ready to connect to Looker Studio using our Google Sheets What-If Connector. We start by either selecting the Google Sheet from a dropdown or by entering the Google Sheets ID directly. Once we do that, our community connector will let you select a specific tab from your Google Sheet. Now we're ready to setup all default parameters. We can opt-in to allow these parameters while we view the Looker Studio dashboard.

3. We can now start building the Looker Studio dashboard as we usually do with the regular Google Sheets connector by Google. But the real magic starts when we pull in some of the parameters to our Looker Studio canvas (watch the short video below)

• Step 1 - Create the Google Sheet as you usually do with all the regular formulas and calculations. I am going to create a Google Sheet data model which can tell us the historical values of a given currency pair. Eg. USDGBP
  
  
  
  
• Step 2 - Create a data source for the above sheet using our Google Sheets What-If community connector. Start with this option & click "Next".
  
  
  
  Now in the next screen select your Google Sheet from the list and click "Next".
  
  
  
  Now we have to set some settings as follows and click the "Connect" button at the top-right.
  
  
  
  Let’s rename our parameter 1 & 2 to read as "From Currency" and "To Currency", respectively. Also let’s make the "Close" dimension to the type Number. Now we’re ready to create our report. So click “Create Report”.
  
  
  
  We will now have a Looker Studio dashboard similar to the following:
  
  
  
  
• Step 3 - Let’s now go back to our Google Sheet. You will see cells C1, D1 and E1 are now populated with some values. These are actually the values passed from the Looker Studio to Google Sheets through our community connector.
  
  
  
  
• Step 4 - We’re going to link cells C1, D1 and E1 to cells B2, B3, B4 and B5 using some simple formulas. Write the following formulas in each of the specified cells:
  
  In B2 write =index(split(E1,";"),1,1)
  In B3 write =index(split(E1,";"),1,2)
  In B4 write =C1
  In B5 write =D1
  
  
• Step 5 - Let’s now revert back to our Looker Studio dashboard and drag the two parameter fields into the dashboard. We can also add a date range control. Your dashboard will now look like the following.
  
  
  
  
• Step 6 - Now you can play with some values for the 2 currency codes and data range control. Take a look at the following demo dashboard to see how the above "Hack" can be used in various different complex use-case scenarios.
  Link to the dashboard - https://lookerstudio.google.com/s/gSILkfNhSfc

You can have a look at the following quick guide video incase you're not familiar with how Looker Studio & Looker Studio Community Connectors work.

• Scenario Manager Demo Dashboard (Google Sheet Containing the Data)

• GOOGLEFINANCE Example (with Advanced Monte Carlo Simulation) - This example is a kind of mind-blowing use case as this shows you how we can use our Google Sheets What-If community connector to run some advanced simulations in Google Sheets using Monte Carlo Simulation and return the results to Looker Studio in just the click of a button. We're hoping with our Google Sheets What-If connector, the Looker Studio community will have some great use of these kinds of extreme use cases. Just imagine running some Monte Carlo simulation for one of your advertising campaigns to decide the best mix of channels brings you the highest ROI?

• Organic Traffic Forecasting Demo Dashboard (Google Sheet Containing the Data)