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

Admin

Forum Posts: 1824

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