I'm building a search tool by which one can convert a google range into an image using google script. I tried to paste that data range to google slides and from there I get thumbnails and their url. I'm searching any other tools that directly give me the url of image of the selected range of a google sheet.
Thanks
I am unsure of the reasoning behind doing this, but nonetheless, here is an answer:
This creates a custom menu on the top of your sheet that says:
Custom Functions => Export Range to Image Files.
When you click that it:
The code is ready to handle multiple ranges being exported, but right now it exports only the selected range.
function onOpen(e) {
//Create custom menu to export range to Slides.
SpreadsheetApp.getUi()
.createMenu('Custom Functions')
.addItem('Export Range to Image Files', 'SelectedRangeToImage')
.addToUi();
}
function SelectedRangeToImage() {
var slide = RangeToSlides();
var slideId = slide.getId();
var images = [];
for (var x=0; x<slide.getSlides().length;x++) {
var image = SlidesToImage(slide.getName()+x, slideId, slide.getSlides()[x].getObjectId());
images.push(image);
}
//Show interface with links to all images
var ui = SpreadsheetApp.getUi();
var html = HtmlService.createHtmlOutput();
html.append("<p>Your images:</p>");
html.append("<ul>");
for (var i=0; i<images.length; i++) {
html.append("<li><a href='"+images[i].getUrl()+"'>"+images[i].getName()+"</a></li>");
}
html.append("</ul>");
html.append("<input type='button' value='Close' onclick='google.script.host.close()' />");
ui.showModalDialog(html, "Exporting results:");
}
function RangeToSlides() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getActiveRange();
var rangeValues = range.getDisplayValues();
var rangeHorizontalAlignments = range.getHorizontalAlignments()
var rangeBackgrounds = range.getBackgrounds();
var rangeFontWeights = range.getFontWeights();
var sl = SlidesApp.create("ExportToImage"+new Date());
var slide = sl.getSlides()[0];
//Create table with size of the range
var table = slide.insertTable(rangeValues.length, rangeValues[0].length);
for (var x=0; x<rangeValues.length; x++) {
for (var y=0; y<rangeValues[x].length; y++) {
var cell = table.getCell(x,y);
cell.getText().setText(rangeValues[x][y]); //Set text
cell.getFill().setSolidFill(rangeBackgrounds[x][y]); //Set background
cell.getText().getTextStyle().setBold(rangeFontWeights[x][y]=="bold"?true:false); //Set text formatting
var alignment;
switch(rangeHorizontalAlignments[x][y]) {
case "general-left":
alignment = SlidesApp.ParagraphAlignment.START;
break;
case "general-right":
alignment = SlidesApp.ParagraphAlignment.END;
break;
case "center":
alignment = SlidesApp.ParagraphAlignment.CENTER;
break;
}
cell.getText().getParagraphStyle().setParagraphAlignment(alignment); //Set text alignment
}
}
sl.saveAndClose();
return sl;
}
function SlidesToImage(name, presentationId, slideId) {
var url = "https://slides.googleapis.com/v1/presentations/"+presentationId+"/pages/"+slideId+"/thumbnail";
var options = {
headers: {
Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
}
};
var response = UrlFetchApp.fetch(url, options);
var responseJson = JSON.parse(response.getContentText());
var imageurl = responseJson.contentUrl;
var imageResponse = UrlFetchApp.fetch(imageurl, options);
var blob = imageResponse.getBlob();
blob.setName(name);
var resultingFile = DriveApp.createFile(blob);
return resultingFile;
}
References:
Only graphic images can be made with Google AppScript. I looked at a lot of code, but they didn't work for me; either they were making a PDF then converting it to a picture, or they were sending it to a slide and then making a picture.
My aim was to make a picture directly with its formats. I converted pictures with their formats, but when the picture was taken directly from the graphic, it was colorless.
Here I’ve converted a friend's code that converts the HTML table to a picture from there.
get the table
var aa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Holiday");
active column
stun=aa.getActiveRange().getColumn();
active column width
stungen=aa.getColumnWidth(stun)+70
number of filled rows
var count = ss.getDisplayValues().flat().filter(String).length;
column and row to import
var sa =aa.getRange(1,stun,count);
formats of received data
var data = sa.getDisplayValues();
var background = sa.getBackgrounds();
var fontColor = sa.getFontColors();
var fontStyles = sa.getFontStyles();
var fontWeight = sa.getFontWeights();
var fontSize = sa.getFontSizes();
Making data into a formatted html table
for (var i = 0; i < data.length; i++) {
html += "<tr>"
for (var j = 0; j < data[i].length; j++) {
html += "<td style='height:20px;background:" + background[i][j] + ";color:" + fontColor[i][j] + ";font-style:" + fontStyles[i][j] + ";font-weight:" + fontWeight[i][j] + ";font-size:" + (fontSize[i][j] + 6) + "px;'>" + data[i][j] + "</td>";
}
html += "</tr>";
}
html + "</table>"
convert html table to chart
const img = Charts.newTableChart().setDataTable(Charts.newDataTable().addColumn(Charts.ColumnType.STRING, '').addRow([html]).build()).setOption('allowHtml',true).setDimensions(stungen,count*28).build();
the size of the picture
setDimensions(stungen,count*28) stungen=column width
convert graphic to image and get link
var imageData1 = Utilities.base64Encode(img.getAs('image/png').getBytes());
var imageUrl = "data:image/png;base64," + encodeURI(imageData1);
htmlOutput.append("Holiday Listesi <br/>");
show image in dialog
htmlOutput.append("Holiday Listesi <br/>");
htmlOutput.append("<img border=\"1\" src=\"" + imageUrl + "\">");
SpreadsheetApp.getUi().showModelessDialog(htmlOutput,"demo");
return htmlOutput;
}
the whole code
function drawTable() {
var aa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Holiday");
stun=aa.getActiveRange().getColumn();
stungen=aa.getColumnWidth(stun)+70
var sa =aa.getRange(1,stun);
var lastRow = aa.getLastRow();
var ss =aa.getRange(1,stun,lastRow);
var count = ss.getDisplayValues().flat().filter(String).length;
var sa =aa.getRange(1,stun,count);
var data = sa.getDisplayValues();
var background = sa.getBackgrounds();
var fontColor = sa.getFontColors();
var fontStyles = sa.getFontStyles();
var fontWeight = sa.getFontWeights();
var fontSize = sa.getFontSizes();
var html = "<b>HOLİDAY</b>"+ "<table border='1'>";
for (var i = 0; i < data.length; i++) {
html += "<tr>"
for (var j = 0; j < data[i].length; j++) {
html += "<td style='height:20px;background:" + background[i][j] + ";color:" + fontColor[i][j] + ";font-style:" + fontStyles[i][j] + ";font-weight:" + fontWeight[i][j] + ";font-size:" + (fontSize[i][j] + 6) + "px;'>" + data[i][j] + "</td>";
}
html += "</tr>";
}
html + "</table>"
var htmlOutput = HtmlService.createHtmlOutput();
const img = Charts.newTableChart().setDataTable(Charts.newDataTable().addColumn(Charts.ColumnType.STRING, '').addRow([html]).build()).setOption('allowHtml',true).setDimensions(stungen,count*28).build();
var imageData1 = Utilities.base64Encode(img.getAs('image/png').getBytes());
var imageUrl = "data:image/png;base64," + encodeURI(imageData1);
htmlOutput.append("Holiday Listesi <br/>");
htmlOutput.append("<img border=\"1\" src=\"" + imageUrl + "\">");
SpreadsheetApp.getUi().showModelessDialog(htmlOutput,"demo");
return htmlOutput;
}
