I'm trying to use the combination of Excel Office Script and Power Automate to send email with an image of Pivot Table.
Below is the code I came up with, but the resulting image that gets sent doesn't include the conditional formatting, only the data and the standard formatting get sent.
I even tried to recreate the conditional formatting within the script code, but no success.
Any ideas? Thanks!
function main(workbook: ExcelScript.Workbook): BudImg {
//Select Budget table
let selection = workbook.getWorksheet("Overview").getRange("A45:R59")
// Add a new worksheet
let sheet1 = workbook.addWorksheet("ScreenShotSheet");
//Paste to range A1 on sheet2 from range A20:J37 on selectedSheet
sheet1.getRange("A45").copyFrom(selection, ExcelScript.RangeCopyType.values, false, false);
sheet1.getRange("A45").copyFrom(selection, ExcelScript.RangeCopyType.formats, false, false);
//adjust columns
//sheet1.getRange("A:R").getFormat().autofitColumns();
//re-create conditional formatting
let conditionalFormatting: ExcelScript.ConditionalFormat;
conditionalFormatting = sheet1.getRange("K:R").addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);
conditionalFormatting.getCellValue().getFormat().getFont().setColor("#9C0006");
conditionalFormatting.getCellValue().getFormat().getFill().setColor("#FFC7CE");
conditionalFormatting.getCellValue().setRule({ formula1: "=0", formula2: undefined, operator: ExcelScript.ConditionalCellValueOperator.lessThan, });
//take screenshot
let table = sheet1.getRange("A45:R59");
let tableImg = selection.getImage();
//delete screenshotsheet
workbook.getWorksheet('ScreenShotSheet').delete();
return {tableImg};
}
interface BudImg {
tableImg: string
}
'''
CodePudding user response:
To get an image of a pivot table, you need to have a line like the below:
workbook.getWorksheet("Sheet1").getPivotTable("My Pivot Table").getLayout().getRange().getImage();
Basically, you can specify the pivot table that you want using getPivotTable(id) and then you need to get the layout and the range of that layout. Then finally, you can use the getImage method. Hope that helps!
CodePudding user response:
Your conditional formatting rule highlights the values which are equal to zero. You can just loop through the values of the range (K:R), see if they're zero, and if so, set the cells to the color you used in the conditional formatting. If you do it this way, the colors should be maintained when you create an image. You can see code to do that below:
function main(workbook: ExcelScript.Workbook) {
let sh: ExcelScript.Worksheet = workbook.getWorksheet("Sheet1")
let range: ExcelScript.Range = sh.getRange("K:R")
let vals: string[][] = range.getValues() as string[][]
let rowCount:number = range.getRowCount()
let colCount:number = range.getColumnCount()
for (let i = 0; i < rowCount; i ){
for (let j = 0; j < colCount; j ){
if (vals[i][j] as unknown === 0) {
let rang: ExcelScript.Range = sh.getRangeByIndexes(i,j,1,1)
rang.getFormat().getFont().setColor("#9C0006");
rang.getFormat().getFill().setColor("#FFC7CE");
}
}
}
}