如何使用Apps脚本将Google表格内容粘贴到Google幻灯片中? [英] How to paste Google Sheets Content into a Google Slide using Apps Script?

问题描述

我现在搜索了三天，找不到找到将Google表格范围放入幻灯片的方法.

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.

感谢您的帮助.

推荐答案

不幸的是，Spreadsheet的范围还不能直接放入Slide的表中(至少现在).那么如何解决呢?该示例脚本的流程如下.

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.

1. 从电子表格中检索值.
2. 通过检索到的值的行和列为幻灯片创建表.
3. 将值放入创建的表中.

示例脚本:

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]);
  }
}

注意:

• 使用此脚本时，请准备presentationId.

Note :

• When you use this script, please prepare presentationId.
• 类表

如果这不是您想要的，对不起.

If this was not wha you want, I'm sorry.

在此修改后的脚本中，添加了以下几点.

In this modified script, the following points were added.

• 使用getDisplayValues()检索电子表格的值.这样，可以复制格式.
  • 我认为，将Spreadsheet手动复制到Slide的情况似乎也相同.
  • 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.

    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);
      }
    }
    

    参考文献:

    • getDisplayValues()
    • getText()
    • getFill()
    • getParagraphStyle()

    References :

    • getDisplayValues()
    • getText()
    • getFill()
    • getParagraphStyle()

    这篇关于如何使用Apps脚本将Google表格内容粘贴到Google幻灯片中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！