Getting an Image of a Power Pivot including Conditional formatting and send email

Question

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
}
'''

score 1 · Answer 1 · answered Jul 05 '22 at 23:14

1

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!

answered Jul 05 '22 at 23:14

Petra

356
1
3

Thanks Petra. It helped reduce the number of lines in my code. But unfortunately, the conditional formatting in the pivot table doesn't appear in the resulting embedded image in the email :( Any thoughts? – Nico Jul 06 '22 at 01:06

Brian Gonzalez · Answer 2 · 2023-01-12T01:33:06.660

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");
            }
          }
        }
      }

Getting an Image of a Power Pivot including Conditional formatting and send email

2 Answers2