- 17 Minutes to read
- Print
- DarkLight
- PDF
Spotlight for Google Sheet
- 17 Minutes to read
- Print
- DarkLight
- PDF
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.
- 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
- In Google Sheets, select Blank to create a new sheet or open an existing sheet.
- Select Extensions > Add-ons > Get add-ons.
- 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.
- Click Install.
- 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.
- Select the required account
- Click Allow.
- The confirmation window appears once the Planful Spotlight add-on is installed.
- 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
- Open any existing or new Google spreadsheet, and navigate to Extensions > Planful Spotlight > Start.
- On the sidebar, Planful’s credential page appears.
- 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.
- Enter the Username and Password.
- Click Login.
- Select the required application from Select Your Application.Note:Every user will be associated with at least one application.
- Click Launch.Note:Once you successfully log in to the application, you will be notified on the home page.
- Navigate to Analyze or Report from the home page.
- (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.
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.
- You can navigate to Account Details by clicking the Planful hamburger menu.
- 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
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.
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.
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.
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.
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.
This page will contain a list of models and views in the below pattern:
- Analytical Models (listed alphabetically), followed by their view
- ESM Models (listed alphabetically, followed by their viewNote:A default view will be created if there is no predefined view.
- FavoritesNote:This displays a list of views selected as favorites in SpotlightXL or Spotlight Web.
- RecentNote:This displays the recent view opened by you.
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.
- 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.
- To create an ad-hoc (Default) view, navigate to Analyze and select any model > Default.
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
- Zoom Out
- Pivot
- Keep Only
- Remove Only
- Get Data
- Display
- Suppress Row
- Suppress Column
- Indent Row Members
- Number Formats
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
- Select a dimension and navigate to Zoom In > Children.
- The immediately associated children for the selected dimension will appear.
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
- Select a dimension and navigate to Zoom In > Leaves.
- When Leaves is selected, leaf-level members associated with the selected dimension member appear with the existing member.
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
- Select a dimension and navigate to Zoom In > All Children.
- When All Children is selected, all children members will appear for the selected dimension member.
Before Zoom In- All Children
After Zoom In- All Children
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
- Select a dimension on the row axis and then navigate to Zoom In > Data Leaves.
- 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
After Data Leaves
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
- Select a dimension member and navigate to Zoom Out > Parent.
- When Parent is selected, only the next highest-level parent is displayed.
Before Zoom Out, Parent
After Zoom Out, Parent
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
- Select a dimension member and navigate to Zoom Out > Top Level.
- 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 Level
After Zoom Out, Top Level
Pivot
The Pivot feature helps you to swap the dimensions between page, row, and column by defining the view as required.
In Practice: Pivot
- Click the Pivot icon and select Pivot.
- The column data is swapped to a row, and the row data is swapped to the column.
Before Pivot
After Pivot
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.
In Practice: Pivot to Page
- Select a dimension on a row or column and navigate to Pivot > Pivot to Page.
- The selected dimension is swapped to the page level.
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.
In Practice: Pivot to Row
- Select a dimension from the column, and navigate to Pivot > Pivot to Row.
- The selected dimension is swapped from column to row.
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.
In Practice: Pivot to Column
- Select a dimension on the row from the view, and navigate to Pivot > Pivot to Column.
- The selected dimension is swapped from row to column.
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.
- Select a cell containing members from a dimension you want to retain and click Keep Only.
- All other dimension members are removed, and only the data and metadata within the selected intersection are displayed.
Remove Only
The Remove Only feature helps you to remove the data and metadata for the selected member(s) from a view.
- Select the required cell and click Remove Only.
- The selected cells are removed.
Get Data
This action updates data in the spreadsheet after selections are made.
- Select any view.
- Click the Get Data icon to update or refresh the selected view.
In Practice: To Display the Hierarchical Order by Updating the dimension members of a view
- Select any dimension member or view, and click Update Selection.
- 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.
- Likewise, select any dimension member from the sidebar, and click Apply.
- 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.
- Select the dimension, and click Update Selection on the sidebar.
- Select the member and click Apply.
- Select the POV members by clicking the Update Selection option in ESM views.
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.
- 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
- Select the required row, and click the vertical ellipsis icon.
- The sub-menu bar appears; click the Suppress Row icon.
- 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.
- 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
- Select the required row, and clickthe vertical ellipsis icon.
- The sub-menu bar appears, click the Suppress Column icon.
- 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.
In Practice: Indent Row
- Select the row, and click the vertical ellipsis icon. The sub-menu bar appears.
- Click the Indent Row icon, and select Yes.
- The row is indented as per hierarchical order.
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.
- Click the Number Format icon > Currency.
- The numbers available in the cell are converted to currency format.
- Percent: By selecting Percent, your data is displayed in a percentage format.
- Click the Number Format icon > Percentage.
- The numbers available in the cell are converted to percentage format.
- Numeric: By selecting Numeric, the data is displayed in numeric format.
- Click the Number Format icon > Numeric.
- 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
- Open a view and click the vertical ellipsis icon.
- The sub-menu bar appears; click the Display icon > Display-Label.
- 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
- Open a view and click the vertical ellipsis icon.
- The sub-menu bar appears; click the Display icon > Code.
- The dimensions will appear with the member code.
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
- Click Report.
- Expand the Reports option and select the report you want to open.
- Double-click on the report to open it.
- Select the required option from the drop-down list.
- Click Refresh to update the selected view.
- Optionally, you can also use the Update Selection option in the sidebar to specify specific data from the drop-down list.
- Click Apply to display the selected data.
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.