Excel Office Script - Getting an Image of a Power Pivot including Conditional formatting and send em-CodePudding

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