About Multiple Ad Hoc Grids on a Sheet

When connected to supported Smart View data sources, you can create multiple grids on one sheet.

These grids can be connected to the same data source or to different data sources. For example, one grid can be connected to Planning and another can be connected to Tax Reporting. They can also be sourced from cubes. You can retrieve data in these grids and shift them on the sheet.

Note:

Administrators: To enable multiple-grid ad hoc for Smart View users, set the Smart View Ad Hoc Behavior option to Standard in the service application settings. See the administration documentation for your service for more information.

Guidelines for Working with Multiple-Grid Sheets

Note the following guidelines while working with sheets that contain multiple ad hoc grids:

  • While inserting multiple ad hoc grids, always insert them on a new sheet which does not have any previous grid in it. If you have already opened a form or an ad hoc grid on a sheet and then select a cell range for adding another grid on the same sheet, a message prompts you stating, "Multiple grids cannot be added to a single-grid sheets. Add a new sheet to add multiple grids".
  • When working with multiple ad hoc grids, select at least one cell within a grid before performing any grid-specific operation. This helps Smart View identify the grid on which you want to take the action. If your selection is on a cell outside the grid, a message prompts you stating, "Select at least one cell in the grid on which you want to perform this operation".

  • You can submit data for only one grid at a time in a multiple-grid sheet.

    If you try to submit data for more than one grid at a time—that is, if you have selected cell ranges in more than one grid—the first range returned by Google Sheets is used to determine the selected grid and the data on only that grid is submitted.

  • You can refresh grid data in a sheet for all connections at the same time.

    For example, you are working on a sheet with Grid 1 and Grid 2 connected to Planning and Grid 3 connected to Tax Reporting. When you click Refresh from the Smart View for Google Workspace menu, all grids—Grid 1, Grid 2, and Grid 3—are refreshed at the same time in a single operation.

    • To refresh all grids together, select any cell outside the ranges of the grids and click Refresh.
    • To refresh only a particular grid, select any cell within the range of the grid and click Refresh. This is saves the time taken to refresh data, especially when the sheet contains a lot of grids.

    For more information, see Refreshing Data.

  • The settings in the Options dialog box apply only to the selected grid on the sheet. This means that you can set different Option settings for each grid in a multiple-grid sheet.

    For example, you want to apply Blue as the background color for Member cells for Grid 1 and Green for Grid 2. First, click anywhere in Grid 1, open the Options dialog box, and then set the Blue cell style under Formatting, Cell Styles. Click Done and then OK to apply the change and close the dialog box. Now, repeat the same for Grid 2, by clicking anywhere in Grid 2 and opening the dialog. After setting the styles, click Refresh. The Member cells appear in Blue and Green background colors respectively for Grid 1 and Grid 2.

  • When you zoom in on a grid and it expands to display data on more rows and columns, the placement of the other grids is automatically adjusted such that the expanded grid does not overlap the contents of the other grids on the sheet.

    For example, if you have two grids one below the other separated by two blank rows of space and you zoom in on the top grid, the bottom grid is pushed down in the sheet, and you can scroll down till the top grid ends to view the other grid.

  • While viewing sheet information for a multiple-grid sheet, the Sheet Info dialog box displays a separate Connection section for each grid. For example, Connection (Grid 1), Connection (Grid 2), and so on. Each section displays details such as Server, Application, URL, Provider, Alias Table, and Associated Ranges. For more information, see Sheet Information.

  • Functions can be inserted in a multiple-grid sheet by typing them manually or using the Function Builder..

    However, in case the multiple-grid sheet contains grids from multiple connections, the HsGetSheetInfo function retrieves the sheet properties only for the first connection, irrespective of the connection that is currently active on the sheet. This is a limitation.

  • Creating a connection for functions is not supported on a multiple-grid sheet.
  • On a Functions-only sheet, multiple grids cannot be inserted.
  • If a particular operation from the Smart View for Google Workspace menu is not supported in a multiple-grid sheet, a message appears to indicate that you cannot perform the operation on the current sheet.
  • When a Smart Form is opened as an ad hoc grid in a multiple-grid sheet, the formula cells appear blank since the layout changes and formula references are not retained.