I´m searching now for three days and cannot find a method to get a Google Sheets Range into a Slide.
var range = SpreadsheetApp.getActive().getActiveSheet().getRange('A1:B3');
var slidesPage = SlidesApp.openById(presentationId).getSlides()[0];
slidesPage.insertTable(range);
It seems it is only possible to copy a table from another slide and paste it into the page. Why is it not possible to paste a range from a spreadsheet into the Slide?. At least it is possible manually.
Thanks for every help.
Unfortunately, the range of Spreadsheet cannot be directly put to the table of Slide yet (At least for now). So how about this workaround? The flow of this sample script is as follows.
- Retrieve values from Spreadsheet.
- Create table to the slide by the rows and columns of the retrieved values.
- Put values to the created table.
Sample script :
var values = SpreadsheetApp.getActive().getActiveSheet().getRange('A1:B3').getValues();
var slidesPage = SlidesApp.openById(presentationId).getSlides()[0];
var table = slidesPage.insertTable(rows, columns);
var rows = values.length;
var columns = values[0].length;
for (var r = 0; r < rows; r++) {
for (var c = 0; c < columns; c++) {
table.getCell(r, c).getText().setText(values[r][c]);
}
}
Note :
- When you use this script, please prepare
presentationId
.
References :
If this was not wha you want, I'm sorry.
Edit :
In this modified script, the following points were added.
- Retrieve values of Spreadsheet using
getDisplayValues()
. By this, the format can be copied.
- I thought that it seems that the manual copy of Spreadsheet to Slide might be also the same situation.
- Copy background of cells.
- Copy font weight of cells.
- Copy alignment of cells.
Modified script :
var range = SpreadsheetApp.getActive().getActiveSheet().getRange('A1:B3');
var values = range.getDisplayValues();
var horizontalAlignments = range.getHorizontalAlignments()
var backgrounds = range.getBackgrounds();
var fontWeights = range.getFontWeights();
var slidesPage = SlidesApp.openById(presentationId).getSlides()[0];
var rows = values.length;
var columns = values[0].length;
var table = slidesPage.insertTable(rows, columns);
for (var r = 0; r < rows; r++) {
for (var c = 0; c < columns; c++) {
var cell = table.getCell(r, c);
cell.getText().setText(values[r][c]);
cell.getFill().setSolidFill(backgrounds[r][c]);
cell.getText().getTextStyle().setBold(fontWeights[r][c] == "bold" ? true : false);
var alignment;
switch(horizontalAlignments[r][c]) {
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);
}
}
References :
- getDisplayValues()
- getText()
- getFill()
- getParagraphStyle()