Home / Questions / 556494
Accepted
aldo bert
Asked: 2022-09-12 19:30:25 +0800 CST 2022-09-12 19:30:25 +0800 CST

How to add Data Labels to charts in Office Script?

  • 772

I've been trying to add the data labels to an online excel graph for a couple of hours now using a script, I plan to use this same script in a flow to optimize the reports in the company I work for. For this I have been trying to use the record actions function however it does not seem to work when I execute it in the flow it seems not to work. I have tried using chart.getDataLabels().setPosition(ExcelScript.ChartDataLabelPosition.outsideEnd); inclusive and tried to use chart_1.getDataLabels().setShowValue(true); however none of these codes seem to work.

To give you an idea of ​​what I'm looking for, I attach some photos: What I have

What I look for

Well that's what I'm looking to create using an excel script. For now my script is as follows:

function main(workbook: ExcelScript.Workbook) {
// Delete the "TESTING" worksheet if it's present, then recreate it.
workbook.getWorksheet('TESTING')?.delete();
// Add a new worksheet
let probando = workbook.addWorksheet("TESTING");
let table1 = workbook.getTable("Table1");
// Add a new pivot table on probando
let newPivotTable = workbook.addPivotTable("PivotTable1", table1, probando.getRange("A1:C18"));
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Customer Name"));
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Amount due"));

let selectedSheet = workbook.getWorksheet("TESTING");
// Insert pivot chart on sheet selectedSheet
let chart_1 = selectedSheet.addChart(ExcelScript.ChartType.pie, newPivotTable.getLayout().getRange());
// Resize and move chart chart_1
chart_1.setLeft(218.25);
chart_1.setTop(0);
chart_1.setWidth(360);
chart_1.setHeight(216);
chart_1.getTitle().setText('Perforacion por Obra');}

And this would be my normal table (Table1): excel table

The name of the worksheet is InvoiceAmounts. Well I hope you can help me, I thank you in advance.

typescript

3 Answers

  1. Run the following code:

    function main(workbook: ExcelScript.Workbook) {
// Delete the "TESTING" worksheet if it's present, then recreate it.
workbook.getWorksheet('TESTING')?.delete();
// Add a new worksheet
let probando = workbook.addWorksheet("TESTING");
let table1 = workbook.getTable("Table1");
// Add a new pivot table on probando
let newPivotTable = workbook.addPivotTable("PivotTable1", table1, probando.getRange("A1:C18"));
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Customer Name"));
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Amount due"));

let selectedSheet = workbook.getWorksheet("TESTING");
// Insert pivot chart on sheet selectedSheet
let chart_1 = selectedSheet.addChart(ExcelScript.ChartType.pie, newPivotTable.getLayout().getRange());
// Resize and move chart chart_1
chart_1.setLeft(218.25);
chart_1.setTop(0);
chart_1.setWidth(360);
chart_1.setHeight(216);
chart_1.getTitle().setText('Perforacion por Obra');
chart_1.getSeries()[0].setHasDataLabels(true);
chart_1.getSeries()[0].getDataLabels().setShowPercentage(true);
chart_1.getDataLabels().setPosition(ExcelScript.ChartDataLabelPosition.outsideEnd);}

    However, it does not finish working in my flow, it tells me that there is a bad gateway and the expected task does not finish. I don't know if you have another solution Abraham. Anyway, thank you very much for taking the time :D

    Mistake

    • 1

  2. Many thanks to @Abraham Valencia for answering my question, here is the final code:

    function main(workbook: ExcelScript.Workbook) {
  // Delete the "TESTING" worksheet if it's present, then recreate it.
  workbook.getWorksheet('TESTING')?.delete();
  // Add a new worksheet
  let probando = workbook.addWorksheet("TESTING");
  let table1 = workbook.getTable("Table1");
  // Add a new pivot table on probando
  let newPivotTable = workbook.addPivotTable("PivotTable1", table1, probando.getRange("A1:C18"));
  // Add pivot field to a hierarchy in newPivotTable
  newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Customer Name"));
  // Add pivot field to a hierarchy in newPivotTable
  newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Amount due"));

  let selectedSheet = workbook.getWorksheet("TESTING");
  // Insert pivot chart on sheet selectedSheet
  let chart_1 = selectedSheet.addChart(ExcelScript.ChartType.pie, newPivotTable.getLayout().getRange());
  // Resize and move chart chart_1
  chart_1.setLeft(218.25);
  chart_1.setTop(0);
  chart_1.setWidth(360);
  chart_1.setHeight(216);
  chart_1.getTitle().setText('Perforacion por Obra');
  chart_1.getSeries()[0].setHasDataLabels(true);
  chart_1.getDataLabels().setShowPercentage(true);
  chart_1.getDataLabels().setSeparator("\n");
  chart_1.getDataLabels().setPosition(ExcelScript.ChartDataLabelPosition.outsideEnd);
}
    • 1
  3. Best Answer

    After the chart title line, add the following:

    chart_1.getSeries()[0].setHasDataLabels(true);
chart_1.getDataLabels().setShowPercentage(true);
chart_1.getDataLabels().setPosition(ExcelScript.ChartDataLabelPosition.outsideEnd);

    comment

    • 0