Spotlight for Google Sheets
  • 17 Minutes to read
  • Dark
    Light
  • PDF

Spotlight for Google Sheets

  • Dark
    Light
  • PDF

Article summary

Introduction

Planful Spotlight add-on for Google Sheets is used for a wide range of financial planning, forecasting, budgeting, and ad-hoc analysis tasks. It fetches data from SpotlightXL and Spotlight Web in a fraction of the time. An intuitive user interface makes it easy to use and navigate and seamlessly creates a sync between your Dynamic Planning modules and Google Sheets.

You can access data sets from the Dynamic Planning interface using your login and specifying environment details. Once connected, you can perform tasks such as 

  • Quick ad-hoc analysis using the Default view even if you do not have a view setup.
  • Zoom in to a specific level within the hierarchy.
  • Zoom out from a specific level within the hierarchy.
  • Swap row and column dimensions using different Pivot options.
  • Quickly segregate data and only see what you want using the Keep Only or Remove Only options.
  • Customize your view and organize your data the way you like by using the different More options like Display, Suppress Rows, Indent Row Members, Number Format, etc. 
  • View the reports and analyze the data further through graphs and charts available in the reports.

Prerequisites

  • The user must predefine all models, dimensions, calculations, maps, and ad-hoc analysis on SpotlightXL or Spotlight Web.
Note:
Certain limitations apply to Spotlight Web. Therefore, having at least one license of SpotlightXL to perform all the setups is mandatory.
  • By using Google spreadsheet, you can only display the data in the generated reports, and views and perform ad-hoc analysis from the available or predefined dimensions for the model.
  • You can not create any Views or Models using Google Sheets as it is currently in read-only mode.
Install Planful Spotlight Add-on for Google Sheets

To install a Planful Spotlight add-on for Google Sheets, you must have a Google account and access to Google Sheets to install the Planful add-on.

In Practice: Install the Planful add-on

  1. In Google Sheets, select Blank to create a new sheet or open an existing sheet.
  2. Select Extensions > Add-ons > Get add-ons.
    Picture2
  3. The Google Workspace Marketplace window appears. Navigate to the Planful Spotlight.
    Note:
    You can also search for the Planful Spotlight add-on from the Search apps bar.
  4.  Click Install.
  5.  Click Continue.
    Note:
    By clicking Continue, you are acknowledging Planful to use your information as per the application’s terms of services and privacy policy.
  6. Select the required account
  7. Click Allow.
  8. The confirmation window appears once the Planful Spotlight add-on is installed.
  9. Click Done.
Log in to Planful Spotlight Add-on

You must follow the steps below in the Google Sheets to log in to the Planful Spotlight add-on.

In Practice: Login to the Planful Spotlight add-on

  1. Open any existing or new Google spreadsheet, and navigate to Extensions > Planful Spotlight > Start.
    Picture1(2)
  2. On the sidebar, Planful’s credential page appears.
  3. Enter the Planful URL.
    Notes:
    • Unified users can contact the support team to set up the user password and obtain the Dynamic Planning URL.
    • Use the Dynamic Planning URL as the login URL for Google sheets.
    • The user should be an External user. 
    • Use the Dynamic Planning site to generate a password for the first time.
    • When logging in for the first time, enter the URL, post which you can select the URL from the drop-down list to connect with the required environment.   
  4. Enter the Username and Password.
    Picture2(1)
  5. Click Login
  6. Select the required application from Select Your Application.
    Note:
    Every user will be associated with at least one application.
    Picture3(3)
  7.  Click Launch.
    Note:
    Once you successfully log in to the application, you will be notified on the home page.
  8. Navigate to Analyze or Report from the home page.
  9. (Optional), you can also navigate to Analyze or Report by clicking the Planful hamburger menu.
Single Sign-On (SSO) for Planful Spotlight on Google Sheets

With Single Sign-On (SSO), you can access the Planful Spotlight platform using a single login method, instead of using multiple logins for different platforms. This means that if you are already logged in to the Dynamic Planning environment or any other Planful environment, you can use the same URL to log in to Planful Spotlight. Similarly, if you are already logged in to Planful Spotlight, you can use the same URL to log in to the Dynamic Planning environment or any other Planful environment.

To take advantage of SSO in Planful Spotlight, all users must be unified and authenticated by the same login mechanism. Once unified, you can access all environments without the need for multiple logins.

The following are some additional points to consider while using the Planful Spotlight for Google Sheets:

  • If an inactive user uses an SSO URL to log in to Planful Spotlight for Google Sheets, they will have to contact their Planful administrator.
  • If an inactive user logs into Planful Spotlight for Google Sheets using any other Planful environment, they will have to contact their Planful administrator.
  • If an inactive user uses a Dynamic Planning URL to log in to Planful Spotlight for Google Sheets, they can sign in to Planful Spotlight for Google Sheets but cannot access the DAP (Direct Access to PCR) views and reports.
Note:

If you use the Dynamic Planning environment or any other Planful environment to access Planful Spotlight for Google Sheets, below are a few conditions under which you will have to enter your login credentials:

  • To access the Planful Spotlight for Google Sheets through the Dynamic Planning environment.
  • When you log in to the Dynamic Planning web application and open Google Sheets in a new tab using any other Planful environment.
Account details

Account details refer to all the information associated with an account, such as: 

  • Name
  • Credential
  • Role 
  • Application (Tenant detail)
  • Version and any other relevant information

It is important to keep this information up to date to ensure secure access to the account.

  1. You can navigate to Account Details by clicking


      the Planful hamburger menu.
    image (10)(4)
  2. The Account Details sidebar appears with all the information.

Resources

You can navigate to Resources by clicking
the Planful hamburger menu.

The list of options appears, such as:

  • Help 
  • Support
  • Community
  • Solution Hub
  • What's New

Help

This section will help you with Planful’s comprehensive collection of help materials, including user guides, FAQs, the latest release notes, and support forums. You can quickly and easily find the information that you need by navigating to our Help.

Community

The Community provides you with a forum where you can:

  • Ask product-related questions
  • Share your expertise with other members
  • Share thoughts and suggestions on product enhancements
  • Introduce yourself
  • Get information on the latest events, like webinars and user group meetings
  • Share tips or tricks or read about tips and tricks from the customer service pros
Note:
Authentication is required to access the Community. Use your Planful username and password to access the same.

Support 

You can contact the Planful Support Team for additional support. 

Solution Hub

The Solution Hub is a primary destination for exploring the extensive capabilities of Planful's Financial Performance Management platform. You can access it through the application and website. It is a centralized repository, featuring interactive demos and informative content tailored to prospective clients and existing users.
Google sheet

Key Features:

  • Comprehensive Searchable Library: Access an extensive array of Planful Solutions directly within the application.
  • In-App Accessibility: Easily explore tailored solutions, search, and add favorites.
    solution hub

Benefits:

  • Enhanced Platform Awareness: Discover the breadth of our platform's capabilities beyond basic FP&A functionalities.
  • Empowerment Through Knowledge: Align with evolving buyer preferences through self-education.
  • Advanced Search Options: Refine searches based on specific criteria such as finance, industry, and region.

If you are a new user, you can explore various solutions and take interactive tours to gain in-depth information. In addition, you can contact the Planful support team if you find a solution that meets your needs.

image (9)(3)

If you are an existing user, you can conveniently access the Solution Hub page within the application by clicking the Solution Hub in the top menu bar. Once there, you can explore the list of solutions by clicking on a card and navigating to the Solution Hub page. You can also view the interactive tour available on the page.

The Solution Hub ensures that you have everything you need to make informed decisions about financial performance management with Planful.

What's New

This section will help you to view all of our release notes by easily navigating to different years and topics.

 Limitations

Below are certain limitations that are applicable while using Google Sheets: 

  • Google Sheets does not allow ribbon creation.
  • Side pane and modal/modeless dialog are only allowed. The side pane will have a fixed width limit of 300px.
  • The side pane allows you to view and select the tree for member selection. Only a flat list of member hierarchy and a single member selection from the dropdown list are supported while using cell-based member selection. 
  • Drag and drop is not allowed from one cell to another.
  • In Planful Spotlight, you can only open and view one report at a time in Google Sheets. The integration does not allow multiple Spotlight reports and views to be open simultaneously.
  • When you refresh a workbook, only one Spotlight report will update with live data. The other reports will remain as static snapshots, displaying data from their initial generation.
  • By default, the worksheet is named Report. If you change the worksheet name and reopen the same report, the changes will not be reflected. To ensure proper data refresh, the active tab where live data is displayed must be labeled as Report according to the naming convention.
  • You cannot view or modify any formulas in the Report section on Google Sheets.

Analyze

The Analyze task allows you to perform actions by selecting a model and an associated view, refreshing the data or zooming in and out of the model. You can also keep or remove certain aspects of the view and pivot the dimensions as required.

When you select Analyze, the available models will be listed with ad-hoc analysis and predefined views. 

Note:
The user security defined on SpotlightXL or Spotlight Web would be carried forward to Google Sheets. Hence you must have access to both.

This page will contain a list of models and views in the below pattern:

  1. Analytical Models (listed alphabetically), followed by their view
  2. ESM Models (listed alphabetically, followed by their view
    Note:
    A default view will be created if there is no predefined view.
  3. Favorites
    Note:
    This displays a list of views selected as favorites in SpotlightXL or Spotlight Web.
  4.  Recent
    Note:
    This displays the recent view opened by you.

Picture1(3)

Default Views (Ad-hoc analysis)

A default layout is created with all dimensions listed for each model, which can be used for ad-hoc analysis.

Notes:
  • The Default views cannot be deleted, renamed, or moved as it is available in read-only mode.
  • The menu bar for Analyze task is non-actionable until you select or double-click any view of a Model.
    Picture2(2)
  • To create an ad-hoc (Default) view, navigate to Analyze and select any model > Default.
    Picture3(4)

View Navigation Actions

The Analyze tab gives you the ability to execute the following operations on a model and its view using the actions in the ribbon. The view data changes by your actions:

Zoom In

Under Zoom In action, there are specific actions that define how to zoom in on the displayed dimension intersection data, such as:

  • Children 
  • Leaves
  • All Children
  • Data Leaves

Children

When you click Zoom In and select Children, the immediate children for the selected dimension will appear.

In Practice: Zoom In - Children 

  1. Select a dimension and navigate to Zoom In > Children.
    Picture1(6)
  2. The immediately associated children for the selected dimension will appear.
Note:
In the above image, we have selected Account as a parent member, and its immediate children are Net Income and Statistical Accounts.

Leaves

When you click Zoom In and select Leaves, only the leaf-level members associated with the selected dimension member will appear.

In Practice: Zoom In- Leaves

  1. Select a dimension and navigate to Zoom In > Leaves.
    Picture3(5)
  2.  When Leaves is selected, leaf-level members associated with the selected dimension member appear with the existing member.
    Picture4(3)

All Children

When you click Zoom In and select All Children, all the members associated with the selected dimension are displayed.

In Practice: Zoom In- All Children

  1. Select a dimension and navigate to Zoom In > All Children.
    Picture5(4)
  2. When All Children is selected, all children members will appear for the selected dimension member.

Before Zoom In- All ChildrenPicture6(3)

After Zoom In- All ChildrenPicture7(2)

Note:
In the above scenario, we have selected Net Income as a dimension; hence, selecting All Children displays all the members associated with Net Income.

Data Leaves

When you click Zoom In and select Data Leaves, only the leaf-level members containing data associated with all the dimensions on the row axis are displayed. 

In Practice: Zoom In- Data Leaves

  1. Select a dimension on the row axis and then navigate to Zoom In > Data Leaves.
    Picture8
  2. When Data Leaves is selected, only leaf-level members containing data associated with all the dimensions on the row axis are displayed, and all parent members in the dimension are removed from the view.

Before Data Leaves Picture9

After Data Leaves 

Picture10

Zoom Out

Zoom Out helps you reach the next highest level within a dimension hierarchy. For example, if you are currently in the "City" level within a geography dimension hierarchy, zooming out will take you to the "State" level.

Zoom Out - Parent

When you select Parent, it helps you to view the highest level parent member of the selected dimension.

In Practice: Zoom Out- Parent

  1. Select a dimension member and navigate to Zoom Out > Parent
  2. When Parent is selected, only the next highest-level parent is displayed.

Before Zoom Out, ParentPicture2(5)

After Zoom Out, ParentPicture3(6)

Note:
In the above scenario, we have selected Operating Margin as a dimension member; hence selecting Zoom Out- Parent displays Account and Net Income as a Parent member. 

Zoom Out - Top Level

When you select Top Level, it helps you to go back to the root member of the selected dimension. 

In Practice: Zoom Out- Top Level

  1. Select a dimension member and navigate to Zoom Out > Top Level.
    Picture4(4)
  2. When Top Level is selected, the view changes to go back to the Top Level (the top or root of the dimension or the topmost member in the dimension you have access to).

Before Zoom Out, Top LevelPicture5(5)

After Zoom Out, Top Level Picture6(4)

Pivot

The Pivot feature helps you to swap the dimensions between page, row, and column by defining the view as required.

In Practice: Pivot

  1. Click the Pivot  icon and select Pivot.
    1(6)
  2. The column data is swapped to a row, and the row data is swapped to the column. 

Before Pivot2(14)

After Pivot3(6)

Pivot to Page

Pivot dimensions to the page level help you to pivot a dimension from a row to a Page section or from a column to a Page section.

Note:
If only one dimension is available on the row/ column, it cannot be pivoted to the Page, as we need at least one dimension on the row and column.

In Practice: Pivot to Page

  1. Select a dimension on a row or column and navigate to Pivot > Pivot to Page.
    4(5)
  2. The selected dimension is swapped to the page level.
    5(3)

Pivot to Row

With Pivot to Row, you can select a single dimension and pivot it from the column to the row or from page to row. Dimensions not selected remain on the column/page, respectively. 

Note:
If only one dimension is available on a column, it cannot be pivoted to a row, as we need at least one dimension on the column.

In Practice: Pivot to Row

  1. Select a dimension from the column, and navigate to Pivot > Pivot to Row.
    6(3)
  2.  The selected dimension is swapped from column to row.
    7(2)

Pivot to Column

With Pivot to Column, you can select a single dimension and pivot it from a row to a column or from page to column. Dimensions not selected remain on the row/page, respectively.

Note:
If only one dimension is available on the row, it cannot be pivoted to the column, as we need at least one dimension on the row.

In Practice: Pivot to Column

  1. Select a dimension on the row from the view, and navigate to Pivot > Pivot to Column.
    8(2)
  2. The selected dimension is swapped from row to column.
    9(1)

Keep Only, Remove Only & Get Data

Keep Only

The Keep Only feature helps you to retain specific data within view. This action works the opposite of Remove Only.

  1. Select a cell containing members from a dimension you want to retain and click Keep Only.
    1(7)
  2. All other dimension members are removed, and only the data and metadata within the selected intersection are displayed.
    2(15)
Note:
Data that is not kept is not permanently removed or deleted. It is just removed from the view.

Remove Only

The Remove Only feature helps you to remove the data and metadata for the selected member(s) from a view. 

  1. Select the required cell and click Remove Only.
    3(7)
  2. The selected cells are removed.
Note:
Remove Only does not permanently remove or delete data. It just removes the intersections from the view.

Get Data

This action updates data in the spreadsheet after selections are made.

  1. Select any view.
  2. Click the Get Data icon to update or refresh the selected view.
    6(4)

In Practice: To Display the Hierarchical Order by Updating the dimension members of a view

  1. Select any dimension member or view, and click Update Selection.
  2. The selected dimension member of view is displayed on the sidebar in hierarchical order.
    Note:

    In the above instance, we have selected Design Marketing Report as a view.

  3. Likewise, select any dimension member from the sidebar, and click Apply.
  4. The selected dimension member is displayed on the screen.
    Note:
    In the below instance, we have selected Net Income as a dimension.
    Notes:
    If you want to update dimension members for any other dimension in the view, follow the same steps again.
    1. Select the dimension, and click Update Selection on the sidebar.
    2. Select the member and click Apply.
    3. Select the POV members by clicking the Update Selection option in ESM views.
    Otherwise, you can also update members using the drop-down list for each dimension.

Suppress Row, Suppress Column, Indent Row Members & Number Formats

Suppress Row

Suppressing rows in a spreadsheet is the process of hiding certain rows from view. It helps you to create summary reports or hide some information. The below set of options are available to do ad-hoc analysis:

  • None: By selecting None, all rows, including Blank and Zero rows, are displayed in the view.
Note:
This is the default value.
  • Zero: By selecting Zero, the rows with zero values will be hidden from the view.
  • Blank: By selecting Blank, the rows that do not have data (blank) will be hidden from the view
  • Both: By selecting Both, the rows with zero values or blank rows are hidden from the view.

In Practice: Suppress Row

  1. Select the required row, and click the vertical ellipsis icon.
  2. The sub-menu bar appears; click the Suppress Row icon.
  3. Select the required option. 

Suppress Column

Suppress columns help to hide or remove unnecessary information from a dataset, reduce the size of the dataset, and help to keep only the columns that are required for a particular task. 

  • None: By selecting None, all columns, including Blank and Zero columns, are displayed in the view.
Note: 
This is the default value.
  • Zero: By selecting Zero, the columns with zero values will be hidden from the view.
  • Blank: By selecting Blank, the columns that do not have data (blank) will be hidden from the view
  • Both: By selecting Both, the rows with zero values or blank columns are hidden from the view.

In Practice: Suppress Column

  1. Select the required row, and clickthe vertical ellipsis icon.
  2. The sub-menu bar appears, click the Suppress Column icon.
  3. Select the required option. 

Indent Row Members

Indenting row members help to identify the hierarchical relationship between the parent and its children members and are especially useful for large datasets with multiple levels of hierarchy. Another benefit is that it helps to improve the readability of the data. Additionally, it also helps simplify navigation through the dataset. 

  • YES: Select YES to indent data. 
  • NO: Select NO to keep the data unindented.
Note: 
The default value is NO.

In Practice: Indent Row

  1. Select the row, and click the vertical ellipsis icon. The sub-menu bar appears.
  2.  Click the Indent Row icon, and select Yes.
  3. The row is indented as per hierarchical order. 
Note: 
The default value is NO.

Number Formats

The Number Format helps you to show the digits of your data in three formats: 

  • Currency: By selecting Currency, you can view your data in your local currency format.
  1. Click the Number Format icon > Currency.
  2. The numbers available in the cell are converted to currency format.
  • Percent: By selecting Percent, your data is displayed in a percentage format.
  1. Click the Number Format icon  > Percentage.
  2. The numbers available in the cell are converted to percentage format.
  • Numeric: By selecting Numeric, the data is displayed in numeric format.
  1. Click the Number Format icon > Numeric.
  2. The numbers available in the cell are converted to numeric format.

Display

Using this feature, you can customize the display of dimension members in two sub-options:

  • Display-Label
  • Code

Display-Label

This feature helps to display the dimension member, including both code and label. 

In Practice: Display-Label

  1. Open a view and click the vertical ellipsis icon.
  2. The sub-menu bar appears; click the Display  icon > Display-Label.
  3. The dimensions will appear with the member code and display label.

Code

With this feature, you can display the dimension member with code only. 

In Practice: Code

  1. Open a view and click  the vertical ellipsis icon.
  2. The sub-menu bar appears; click the Display  icon > Code.
  3. The dimensions will appear with the member code.

Reports

This section allows you to refresh your existing reports. The Report section is divided into four subsections: Reports, Snapshots, Favorites, and Recent.

In Practice: To Open a Report

  1. Click Report.
  2. Expand the Reports option and select the report you want to open.
  3. Double-click on the report to open it.
  4. Select the required option from the drop-down list.
  5. Click Refresh to update the selected view.
  6. Optionally, you can also use the Update Selection option in the sidebar to specify specific data from the drop-down list.
  7. Click Apply to display the selected data.
Note:
The Refresh icon in Planful Spotlight ensures that any changes made to the opened report in SpotlightXL will be synchronized with the currently open report in Google Sheets, keeping it updated with the latest modifications.

Let's consider a scenario where you are working with Google Sheets and have opened a report named 2015_Report in Planful Spotlight. If you need to modify the value for All Scenarios, you can easily do so by accessing SpotlightXL. Within SpotlightXL, you can make the necessary changes to the same report. Once you have saved the modifications, return to your Google Sheets and click the Refresh icon. This action will automatically update your Google Sheet with the latest modifications made in SpotlightXL.


Was this article helpful?