Excel Forum

Office Scripts - Excel for Web

Peter Fresh

Member

Members

Forum Posts: 15

Member Since:
August 6, 2020

Offline

May 14, 2021 - 9:25 pm

Hey guys,

I am working on a project where I can create an image of a chart from excel and save the outcome to a sharepoint folder.

Goal is to create several images which can be displayed easier and faster from a sharepoint site.

For this project I used this script from the microsoft docs as a template.

Email the images of an Excel chart and table - Office Scripts | Microsoft Docs

For some reason my script fails and I am not able to create an image file.

Here is my script, which I have written so far:

function main(workbook: ExcelScript.Workbook) {

// Fetch chart and use getImage method to create image

  let selectedSheet = workbook.getActiveWorksheet();

  console.log('line ran successfully');

  console.log('selectedSheet created');

  const chart_1 = selectedSheet.getChart("diagram 15");

  chart_1.setPosition("I23");

  console.log('line ran successfully');

  console.log('chart_1 created and positioned correctly');

  const img = chart_1.getImage();

  console.log('line ran successfully');

  console.log('img created');

  return {img}

}

// The interface for chart images in power automate.

interface ReportImages {

  img: string

}

When I run this script in Excel Web, everything seems fine and I cannot track any mistakes.

However when I create a flow in power automate, this scripts fail as shown in the screenshot next.

I would be really grateful for any advice

Thanks in advance!

Regards,

Peter

Attachments

2021-05-14-13_17_53-.png (141 KB)

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

May 16, 2021 - 2:34 pm

Hi Peter,
Not seeing the correct code in your sample.

Microsoft docs has this first line:
function main(workbook: ExcelScript.Workbook): ReportImages {

then ends with:

// The interface for table and chart images.

interface ReportImages { chartImage: string tableImage: string }

You can see that there is a relationship between main and Reportimages, highlighted in red.

In your code the first line is incomplete.

Peter Fresh

Member

Members

Forum Posts: 15

Member Since:
August 6, 2020

Offline

May 17, 2021 - 8:33 pm

Hey Catalin,

thanks for this hint.

I added the corresponding word at the beginning of my code. Now, my code looks like this. (I didn't change the console outcome from German to English - but this shouldn't affect the code after all and I hope you can still give me a try 🙂 )

function main(workbook: ExcelScript.Workbook): ReportImages {

  let selectedSheet = workbook.getActiveWorksheet();

  console.log('Zeile erfolgreich');

  console.log('selectedSheet erstellt');

  let chart_1 = selectedSheet.getChart("Diagramm 1");

  chart_1.setPosition("A1");

  console.log('Zeile erfolgreich');

  console.log('chart_1 erstellt und positioniert');

  const img = chart_1.getImage();

  console.log('Zeile erfolgreich');

  console.log('img erstellt');

  return {img}

}

// The interface for table and chart images.

interface ReportImages {

  img: string

}

For some reason, it is still not working.

When I run this code in Excel Web, everythings seems fine just as before. In Power Automate I get following message:

We were unable to run the script. Please try again.
Runtime error: Line 6: Cannot read property 'setPosition' of undefined

To me, it looks like Power Automate can't handle the variable of chart_1. My guess was it to change the code as following:

let chart_1 = selectedSheet.getChart("Diagramm 1"); -> const chart_1 = selectedSheet.getChart("Diagramm 1");

Unfortunately, this did not work just like before. I got the same error message from Power Automate.

Last guess from me was it to delete the line with the setPosition method - this led to the same error message with the little change of now saying:

Runtime error: Line 6: Cannot read property 'getImage()' of undefined

Do you have another idea to fix this problem?

Regards,

Peter

Peter Fresh

Member

Members

Forum Posts: 15

Member Since:
August 6, 2020

Offline

May 18, 2021 - 12:02 am

Hey Catalin,

I finally can say I have found a solution.

Really, I do not understand the reason why this works, but I am glad it does.

I came up with the idea to change the code from referring to the active sheet by workbook.getActiveWorksheet() into referring to a specific worksheet like that: sheet_grafiken = workbook.getWorksheet("Grafiken"). This code then works just fine in Power Automate! I will attach a screenshot of the final code.

Next problem was to create an image file from a string coded in base64 and save that image in a sharepoint library.

I came up with a solution and I will attach a screenshot to this post as well so someone else can participate and I will be happy to receive any suggestions for improvement.

Thanks!

Peter

Attachments