Hello Team,
I have created a Canvas Dashboard. After that I’ve created 3 pivot tables. When I exported the Dashboard as an excel the result was like each pivot table in separate sheet/tab in the result excel file.
Actually, I need to create 17 pivot tables … And I need to send them in 1 email to the client but I need to split them into sheets/tabs … Each 3 in separate sheet/tab.
Is there a way to do this?
Hi Hamza Rashed,
Thank you for reaching out! To make sure I understand correctly, I want to confirm the expected behavior you are looking for.
You would like 1 Excel file where the 17 Pivot Tables are grouped together—specifically 3 tables per sheet.
Does that accurately capture what you’re looking to achieve?
If the above is your expected behavior, I’d love to clarify a few points to ensure the final report is as readable as possible for your client:
Let me know your thoughts! Thank you for your patience.
Hi @Hien_Nguyen_Thanh - You are correct and I need each 3 tables in 1 excel sheet after exporting the data.
And the result they will be vertically … So, we will have 6 sheets in the excel and each sheet will include 3 tables vertically … Also, just to make sure … I need them into a dashboard so I can send it via email as a schedules.
Thanks
Hi Hamza Rashed,
Thank you for sharing your use case. Currently, we don’t natively support vertical stacking in Excel, and it isn’t on our immediate roadmap. I’ve shared your feedback with our team, but before looking at solutions, I wanted to check your thoughts on a few potential issues with this layout:
Are your stakeholders aware of these limitations, or are the tables usually short enough (10-20 rows) that these aren’t concerns? Also, is it always strictly 3 tables per sheet?
Side note: If your tables are short and you’re comfortable with these trade-offs, I have an approach that can automate this for you with a bit of setup.
Actually each table have 4 or 5 rows only … I had a discussion with my team to make each table in separate sheet but they said it will be very complicated for the work.
They said if we can provide the pivot table in the excel that would be great as well … But in Holistics when we export the final pivot table data it returns as a data only without the pivot table filters or options.
Hi Hamza Rashed,
Thank you for providing your detailed use case. I completely understand the frustration here.
To confirm our understanding of your workflow:
While Holistics doesn’t natively support exporting the pivot engine, we have an approach to help you merge multiple views into a single tab.
This guide walks you through creating a simple automation that intercepts your scheduled Holistics Excel files in Gmail and merges their multiple tabs into a single, scannable sheet. The setup involves two main steps:
Disclaimer: This guide uses Gmail and Google Apps Script. If you use Outlook, Exchange, or other providers, you can achieve similar results with IMAP scripts or automation tools (e.g., Microsoft Power Automate).
Create an email schedule for your Holistics dashboard or report:
Paste the script below into the Code editor
/**
* Processes Holistics Excel attachments and merges multiple tabs
* into a single consolidated layout based on Script Properties.
* * Required Services: Drive API
*/
function processAndConsolidateReports() {
const props = PropertiesService.getScriptProperties();
// 1. Load Config from Existing Properties
const query = props.getProperty('SEARCH_QUERY');
const recipients = props.getProperty('RECIPIENTS');
const attachmentName = props.getProperty('ATTACHMENT_NAME');
const tablesPerSheet = Number(props.getProperty('TABLES_PER_SHEET'));
const rowSpacing = Number(props.getProperty('ROW_SPACING'));
const postProcess = (props.getProperty('POST_PROCESS') || '').toLowerCase();
// 2. Search for the Holistics Email
const threads = GmailApp.search(query, 0, 10);
console.log("Threads found: " + threads.length);
if (threads.length === 0) return;
// Process all threads found by the query
threads.forEach(thread => {
const messages = thread.getMessages();
const message = messages[messages.length - 1]; // Get the most recent message
const allOriginalAttachments = message.getAttachments();
const targetExcel = allOriginalAttachments.find(a => a.getName().endsWith('.xlsx'));
if (!targetExcel) {
console.log("No Excel found in thread: " + message.getSubject());
return;
}
// 3. Convert Excel to Google Sheets
const tempFile = Drive.Files.insert({
title: "Temp_" + message.getId(),
mimeType: MimeType.GOOGLE_SHEETS
}, targetExcel);
const ss = SpreadsheetApp.openById(tempFile.id);
const sourceSheets = ss.getSheets();
const finalSs = SpreadsheetApp.create("Merged_" + message.getId());
// 4. Merge Logic
for (let i = 0; i < Math.ceil(sourceSheets.length / tablesPerSheet); i++) {
let masterSheet = finalSs.insertSheet("Report Group " + (i + 1));
for (let j = 0; j < tablesPerSheet; j++) {
let sourceIdx = (i * tablesPerSheet) + j;
if (sourceIdx < sourceSheets.length) {
let sourceData = sourceSheets[sourceIdx].getDataRange().getValues();
let destRow = masterSheet.getLastRow() === 0 ? 1 : masterSheet.getLastRow() + rowSpacing;
masterSheet.getRange(destRow, 1, sourceData.length, sourceData[0].length).setValues(sourceData);
}
}
}
if (finalSs.getSheetByName("Sheet1")) finalSs.deleteSheet(finalSs.getSheetByName("Sheet1"));
// 5. Convert & Send
const url = `https://docs.google.com/spreadsheets/d/${finalSs.getId()}/export?format=xlsx`;
const token = ScriptApp.getOAuthToken();
const response = UrlFetchApp.fetch(url, { headers: { Authorization: "Bearer " + token } });
const mergedBlob = response.getBlob().setName(attachmentName);
const cleanRecipients = recipients.replace(/[\[\]"]/g, '');
const otherAttachments = allOriginalAttachments.filter(a => a.getName() !== targetExcel.getName());
const finalAttachmentList = [mergedBlob, ...otherAttachments];
GmailApp.sendEmail(cleanRecipients, message.getSubject(), message.getPlainBody(), {
htmlBody: message.getBody(),
attachments: finalAttachmentList
});
// 6. Post-Process (This is what prevents duplicates)
if (postProcess === 'mark_read') thread.markRead();
if (postProcess === 'archive') thread.moveToArchive();
// 7. Cleanup Drive
DriveApp.getFileById(tempFile.id).setTrashed(true);
DriveApp.getFileById(finalSs.getId()).setTrashed(true);
console.log("Successfully processed: " + message.getSubject());
});
}
Use Script Properties for configuration. In Apps Script → click Project Settings → Script properties → Open editor. Add the following key–value pairs:
| Property Key | Example Value | Notes |
|---|---|---|
SEARCH_QUERY |
is:unread from:[email protected] subject:"Sales Dashboard" |
Target query to find the specific report in your inbox. |
RECIPIENTS |
[email protected], [email protected] |
Comma-separated list of emails to receive the final report. |
SEND_IMAGES_AS_PHOTOS |
true |
Sends images via sendPhoto |
INCLUDE_INLINE |
false |
Whether to forward inline images. |
ATTACHMENT_NAME |
Merged_Sales_Report.xlsx |
The name of the final consolidated Excel file. |
TABLES_PER_SHEET |
3 |
How many original tabs to stack vertically per new worksheet. |
ROW_SPACING |
3 |
The number of empty rows to insert between stacked tables. |
POST_PROCESS |
archive |
Action to take after success: mark_read or archive. |
In Apps Script, go to Triggers (clock icon). Add trigger:
processEmailsNow, the script will check Gmail periodically and forward any matching emails to your clients.
Whenever Holistics delivers your 17-tab report to Gmail, this script will automatically trigger, merge the tables into a single-sheet (or few-sheet) layout, and email the clean version to your stakeholders. This keeps your data accurate while making it much easier for your team to scan.
We hope that this approach helps your team achieve the perfect balance between data accuracy and professional report layouts. If you need any help or encounter any struggles during the setup, feel free to ask below!
Happy reporting!
