0

I want to put cell content row by row from different columns within one google sheet into one single google slide.

I found this video from google developpers, but they use the api to keep the slide updated, which I don't need. Within stackoverflow the closest question is here, where a range of a sheet is copied into a slide.

for example this sheet

Column A__________Column B_________Column C
Paul______________Baker____________New York
Jerry______________Smith____________Chicago
Tim_______________Johnson__________Los Angeles

In the slide there are for example the placeholders for {{column A}} and {{column C}} (I don't need B) each one of them for example 5x. Now the script should fill row by row the values from column A and column C into the slide till the last row of the sheet and leave the remaining placeholders empty.

zoliverz
  • 9
  • 3

1 Answers1

0

Suppose you have a presentation with the first blank slide (no tables). It is useful to start from blank, because we have not to keep in mind exact number of data rows. Then we should create new table with two columns and fill it with imported spreadsheet data.

We can do it by Google script inside both Spreadsheet and Slide container. I've chosen Slide container with known spreadsheet id:

function addTable() {
  var ssId = '-- spreadsheet ID here --';
  var values = SpreadsheetApp.openById(ssId).getSheetByName('Test').getRange('A1:C3').getValues();
  var table = SlidesApp.getActivePresentation().getSlides()[0].insertTable(values.length, 2);
  for (var i in values) {
    table.getRow(i).getCell(0).getText().appendText(values[i][0]);
    table.getRow(i).getCell(1).getText().appendText(values[i][2]);
  }
}

Table is the simplest placeholder to work with spreadsheet data and this basic code sample is for it.

You have suggested less obvious variant for placeholders (shapes), such as here:

enter image description here

Taking into account placeholders arrangement in conjunction with their indices values, we can fill them with imported data by means of this function call:

function importData() {
  var ssId = '-- spreadsheet ID --';
  var values = SpreadsheetApp.openById(ssId).getSheetByName('Tabellenblatt2').getRange('A1:C4').getValues();
  var slide = SlidesApp.getActivePresentation().getSlides()[0];
  var shapes = slide.getShapes();
  for (var i in values) {
    shapes[i].getText().setText(values[i][0]);
    shapes[parseInt(i) + 4].getText().setText(values[i][2]);
  }
}
Александр Ермолин
  • 1,145
  • 1
  • 10
  • 17
  • Thanks for your help. I hope my explanation was understandable. I don't want to create a table in google slides. Just want to put the values from the sheet where there are placeholders in the slide. Trying your code I got the following error: TypeError: Method "getSlides" from null cannot be called. (row 4) – zoliverz Dec 26 '18 at 07:57
  • Lets clarify about existing "placeholders". Do you mean page elements, such as table, but not tables? (https://developers.google.com/apps-script/reference/slides/page-element) We should know how to find them on the page to fill with data. I check the above code in my Slides app, it is working. – Александр Ермолин Dec 26 '18 at 08:15
  • Do you mean something like this - https://docs.google.com/presentation/d/1qN5dtYU--oF3qrV0paPqtWh07D7ghyP9SphZ5RUhJO0/edit?usp=sharing ? – Александр Ермолин Dec 26 '18 at 09:21
  • the element is a textbox, here is an example https://drive.google.com/open?id=1kBSMQ9PalIZvsx66biGx4E32BlhQSloeMOxDaVVT-rc . how can i refer to exact that slide for example in your code? – zoliverz Dec 26 '18 at 09:28
  • sorry, I made it accessable. its excatly like you showed it in your slide. when I wrote my comment I didn't see your comment a few minutes earlier. – zoliverz Dec 26 '18 at 10:05
  • I've added a small function to your Slides app to view text shapes indices. From this code you can see how to access these page elements and modify their text. Do not keep "edit" share for everyone! – Александр Ермолин Dec 26 '18 at 11:14
  • I tried it, [here is the folder](https://drive.google.com/drive/folders/1h64-ZtYg6yFGUyEWzVBqfVpptcpUZtqF?usp=sharing) but it is filling the placeholders not as it should be. – zoliverz Dec 27 '18 at 06:27
  • I've extended my answer to include shapes as placeholders. See also my comments in the shared Slides document. – Александр Ермолин Dec 27 '18 at 07:21
  • thank you, that works fine when there are 4 rows. But make it more general that it works for a variable number of rows? – zoliverz Dec 27 '18 at 08:13
  • What do you prefer - add more shapes on the slide by script or add more lines into every shape? Both variants are not useful, that is why I have started with table as a placeholder. But if you are sure that this way is necessary, we can solve it too in another question (too many details to include all here, and we need a long discussion to find the best solution). – Александр Ермолин Dec 27 '18 at 08:34
  • I do not know how to describe my task more precisely . What do you mean by shape? I think of a placeholder (textbox) containing a variable, for example the header of a column. But the number of the placeholders in the slide for each column is bigger than the cells with content in that column. So the script has to import the values from that column and fill the the textboxes forseen for this column as long as there are rows with content before moving to the next column and importing these values. I think it makes no difference if the script runs row by row or column by column. – zoliverz Dec 27 '18 at 16:41
  • If you don't understand the term "shape", then you probably did not read Google official documentation - https://developers.google.com/apps-script/reference/slides/shape. This is the same as text box in our case. – Александр Ермолин Dec 27 '18 at 17:50
  • From your explanation I still have no ideas, what should the script do. For the sake of simplicity let us consider 1 data column only with 5 rows of data. The slide already has 4 shapes (or text boxes). How should the script distribute 5 values among 4 shapes? – Александр Ермолин Dec 27 '18 at 17:55
  • ok we could do that. There will always be a slide with more textboxes than rows. – zoliverz Dec 27 '18 at 18:29