0

I'm new to app script and coding in general.

I've written a script that uses an "on form submit" trigger to create a new folder in my drive, copy three (3) template files and drop them in there:

  • Two (2) of these are spreadsheets and the other is a Slides presentation.

I have tables in the spreadsheets that I want to embed and have update automatically into the presentation.

It looks like I can only embed charts from a spreadsheet into a presentation. Is this true?

If so, can I call on certain cells in the spreadsheet and get their values; then, place those in a pre-made table on the slides presentation?

Any other workarounds that I may be missing?

My group does this process any where between 8-30 times a day, so any way to automate this part of it would save a lot of time!

Thanks!

Aerials
  • 4,231
  • 1
  • 16
  • 20
Adam A
  • 3
  • 2
  • Welcome! Please refer to [how to ask](https://stackoverflow.com/help/how-to-ask) when providing details to your questions. – segFault Feb 17 '20 at 17:21
  • takes look at this; https://developers.google.com/slides/reference/rest/v1/presentations.pages/tables#Page.TableCell – Cooper Feb 17 '20 at 21:30
  • There's also some addons that can facilitate this activity. One is called SlidesMerge and I think it's written by Bruce McPherson but I didn't see it in the Addon Market Place. – Cooper Feb 17 '20 at 21:39
  • Can you provide the script you have. Is it written in Apps Script? – Aerials Feb 18 '20 at 08:11

1 Answers1

0

A Table from Spreadsheet to Slide

function putTablesIntoSlides() {
  var pr=SlidesApp.openById('Presentation Id');
  var slide1_ObjectId=pr.getSlides()[1].getObjectId();//I was adding the table to slide[1];
  Logger.log(slide1_ObjectId);
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet10');
  var rg=sh.getDataRange();
  var v=rg.getValues();
  //Create a table the size comes from Spreadsheet Data
  var request1={"createTable": {"elementProperties":{"pageObjectId": slide1_ObjectId},"rows": v.length,"columns": v[0].length}};
  var resp1=Slides.Presentations.batchUpdate({requests:request1}, pr.getId());
  //Setting up the data for adding text to the above table.
  var request2=[];
  for(var i=0;i<v.length;i++) {
    for(var j=0;j<v[i].length;j++) {
      request2.push({"insertText":{"objectId":resp1.replies[0].createTable.objectId,"cellLocation": {"rowIndex":i,"columnIndex":j},"text": v[i][j].toString()}});
    }
  }
  //Add test to all of the table cells
  var resp2=Slides.Presentations.batchUpdate({requests:request2}, pr.getId());
  Logger.log(JSON.stringify(resp1));   
}

This is my table data:

HDR1 HDR6 HDR2 HDR5 HDR4 HDR7 HDR8 HDR3
5 7 2 6 4 3 1 0
7 4 8 2 3 1 7 8
5 7 5 0 0 9 1 2
2 4 9 9 7 3 7 2
1 0 3 3 5 3 9 7
3 7 8 3 2 4 1 8
5 8 6 3 1 1 8 4
3 1 7 7 1 5 7 5
9 6 3 6 8 6 9 8
5 4 9 6 2 3 3 4

Google Slides API

SlidesApp

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Is this written in Google App Script? The 11th line "var resp1=Slides.Presentations.batchUpdate" doesn't seem to work. Is this using the SlidesApp? Or is Slides a predefined variable? – Adam A Mar 09 '20 at 20:19
  • You need to enable Slides API using Resources/Advanced Google Services. Check out the references – Cooper Mar 09 '20 at 20:34
  • 1
    Thanks for that tip. That technically works, but the formatting is way off, and the table doesn't come close to fitting on the slide. Is there a way to just make the table in google slides, then pull the values from the speadsheet and just drop them into cells on the slide? That would eliminate the formatting issues. Thanks for the quick response! – Adam A Mar 09 '20 at 20:48
  • @Adam A Tried to use the above on an existing table but got the error: Invalid requests[0].insertText: The object (SLIDES_API1834905201_1) could not be found. This is the id of the slide with the table??? let templatePres = SlidesApp.openById(templatePresId); let slidesArr = templatePres.getSlides(); let templateSlideId = slidesArr[0].getObjectId(); let tblArr = slidesArr[0].getTables(); let theTblId = tblArr[0].getObjectId(); // table page element The code to insertText inclues the table object id and the updateBatch has the presentation id but the slide id is nowhere? – aNewb Jul 19 '21 at 03:08