How to copy a range from a spreadsheet as an image to Google Slides?

Question

I am trying to copy a range of cells of a specific Google spreadsheet as an image onto a Google slide. But I could barely find useful code. This is what I came up with, but I still cannot transfer the cell range into an image/png.

Goal: Insert the image stored just in a variable to a specific slide!

function add_WSA(){
  //Opening the Spreadsheet
  var ss = SpreadsheetApp.openById("insertSpreadsheetID");
  var range = ss.getRange("example!A1:F20");//in A1 Notation
  //Conversion into an png image
  var image = range.getAs('image/png');
  
  //Opening the specific Slide (Nr. 3)
  var slide = SlidesApp.openById("mySlidesID").getSlides()[2]; 
  
  //Insertion of image
  slide.insertImage(image);
}

Error: TypeError: range.getAs is not a function
    at add_WSA(report:5:21)

https://stackoverflow.com/tags/google-apps-script/info – Cooper Aug 18 '20 at 15:18 — Cooper, Aug 18 '20 at 15:18

score 2 · Accepted Answer · edited May 30 '23 at 20:24

2

Issue and workaround:

Unfortunately, in the current stage, the range object cannot be directly converted to the PNG format. So in this case, it is required to use a workaround. In this answer, as the workaround, I would like to propose to use Charts Service. When Charts Service is used, the range of Spreadsheet can be converted to an image blob.

Sample script:

function add_WSA(){
  //Opening the Spreadsheet
  var ss = SpreadsheetApp.openById("insertSpreadsheetID");
  var range = ss.getRange("example!A1:F20");//in A1 Notation
  //Conversion into an png image


  // I modified below script.
  const [header, ...values] = range.getDisplayValues();
  const table = Charts.newDataTable();
  header.forEach(e => table.addColumn(Charts.ColumnType.STRING, e));
  values.forEach(e => table.addRow(e));
  const image = Charts.newTableChart().setDataTable(table.build()).setDimensions(500, 500).setOption('alternatingRowStyle', false).build().getBlob();


  //Opening the specific Slide (Nr. 3)
  var slide = SlidesApp.openById("mySlidesID").getSlides()[2]; 
  
  //Insertion of image
  slide.insertImage(image);
}

Result:

When above script is run, the following sample result can be obtained.

Note:

Please use this script with enabling V8.
In this case, for example, when you want to change the font color, please use HTML code in each cell value.

Reference:

Charts Service

edited May 30 '23 at 20:24

desertnaut

57,590
26
140
166

answered Aug 19 '20 at 03:15

Tanaike

181,128
11
97
165

unfortunately this doesn't support formatting sheet. image result was lost all coloring etc – Ling Loeng Feb 28 '22 at 07:53
1

@Ling Loeng About your comment of `unfortunately this doesn't support formatting sheet. image result was lost all coloring etc`, yes. So I proposed this as one workaround for converting the range to an image, and I said `In this case, for example, when you want to change the font color, please use HTML code in each cell value.` in my answer. This is due to my poor skill. I deeply apologize for my poor skill. I would be grateful if you can forgive my poor English skill. – Tanaike Feb 28 '22 at 08:20
Hello @Tanaike. I'm interested in something like this. I am using ´´´sendEmail(´´´ to send an email every day with some info from my spreadsheet. I have some ´´´SPARKLINE´´´ graphs that Iºd like to be sent with the email. Is it possible to receive an email with some ranges of cells converted to PNG and embeded in the email body? I will write a question later with the details. I tried this but did not work: https://stackoverflow.com/questions/41367365/403-error-with-message-google-slides-api-has-not-been-used-in-project-befor – Verminous Jun 04 '22 at 17:45
1

@Verminous About your question, I would like to support you. But, I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. So, can you post it as a new question by including more information? By this, it will help users including me think of the solution. If you can cooperate to resolve your question, I'm glad. Can you cooperate to do it? – Tanaike Jun 04 '22 at 23:12
Basically I wanted to do what is here: https://stackoverflow.com/questions/58327686/get-range-of-google-spreadsheet-into-an-image-using-google-script But it's not working. It looks like there is no way to convert a range to a .PNG file. I will post the question soon. I was trying to find some answer before I post mine and I found that one. But I will post again because maybe there are new developments in Google Apps Script that allows me to do what I need. Thank you. Maybe tomorrow I'll have the question ready. – Verminous Jun 04 '22 at 23:39
1

@Verminous Now, I came up with an idea for achieving your goal. For example, when the range is exported as PDF data and convert the PDF data to PNG data? About exporting the range as a PDF, you can see the sample script at https://stackoverflow.com/a/72500183 . And, about converting PDF to PNG, you can see the sample script at https://stackoverflow.com/a/55152707 . If this was not useful, I apologize. – Tanaike Jun 05 '22 at 01:16
Hello @Tanaike I have posted my question about this here: https://stackoverflow.com/questions/72508810/email-sparkline-graphs-as-image-blog-png-from-google-sheets-range – Verminous Jun 05 '22 at 15:48
@Verminous Thank you for your response. Now, I noticed that an answer has already been posted and the discussions have already been advanced. In this case, I would like to respect the answer and discussions. – Tanaike Jun 05 '22 at 23:50

How to copy a range from a spreadsheet as an image to Google Slides?

1 Answers1

Issue and workaround:

Sample script:

Result:

Note:

Reference:

Linked

Related