Group rows in google sheets and create google slides via appscript

Question

I have this Google sheet table. I want to send this data to Google slides

I would like to group rows in one slide. For instance, A to C can be grouped in one slide and D to F can be grouped in another slide. Or maybe group D and F together in one slide and A to C in individual slides. So I want a code that accounts for these conditions.

Currently, I have the below script mentioned here. This script creates slides for each row and works perfectly fine.

function newPresentation() {
  let templateId = "XXX";
  let presentationTemplate = DriveApp.getFileById(templateId);
  let copy = presentationTemplate.makeCopy().getId();
  let deck = SlidesApp.openById(copy);
  let slides = deck.getSlides();
  let dataRange = SpreadsheetApp.getActive().getDataRange();


  
  const ar = ["{{item}}", "{{results}}"]; // Sample object for replacing the placeholders.
  const [, ...sheetContents] = dataRange.getDisplayValues();
  slides.forEach((slide, i) => ar.forEach((f, j) => slide.replaceAllText(f, sheetContents[i][j])));
}

Here is the final result sample that I want - https://docs.google.com/presentation/d/1jKXnvhINF2Qz1CYn9BpzHW0Xuj_cZ_Q1IULZpe8Sjp4/edit?usp=sharing

Here is the Master Template - https://docs.google.com/presentation/d/1NM1gUIkiOk7bRA2VhDScoa-8ph-6ike6Fuyrv4ngcuw/edit?usp=sharing

It seems that your provided Google Slide is your expected result. Also, can you provide your template Google Slide? — Tanaike
– Tanaike, Commented Nov 21, 2023 at 6:01
Thank you for replying. From your provided template Slide, I proposed an answer. Please confirm it. If that was not useful, I apologize. — Tanaike
– Tanaike, Commented Nov 21, 2023 at 6:28

Tanaike · Accepted Answer · 2023-11-21 06:27:54Z

1

When I saw your provided template Google Slide, it seems that pages 2 and 3 have the placeholders. But, in that case, only one kind of placeholder is used. It's {{item}} - {{results}}. And, {{item}} - {{results}} is used 3 times on every page. In this case, I would like to propose the modification of your placeholder of {{item}} - {{results}} for being a simple script. It's as follows.

Page 2
- Please modify 3 placeholders of {{item}} - {{results}} as follows.
- {{item1}} - {{results1}}, {{item2}} - {{results2}} and {{item3}} - {{results3}}.
Page 3
- Please modify 3 placeholders of {{item}} - {{results}} as follows.
- {{item1}} - {{results1}}, {{item2}} - {{results2}} and {{item3}} - {{results3}}.

In this answer, I would like to propose a modified script using these modified placeholders. So, before you test my script, please do the above modification of your placeholder.

By the way, when I saw your expected result, it seems that the result of the 3rd page is D - This is item A, E - This is item B, and F - This is item F. From your showing Spreadsheet, I guessed that those might be D - This is item D, E - This is item E, and F - This is item F. Please be careful about this.

The modified script is as follows.

Modified script.

function newPresentation() {
  let templateId = "XXX";
  let presentationTemplate = DriveApp.getFileById(templateId);
  let copy = presentationTemplate.makeCopy().getId();
  let deck = SlidesApp.openById(copy);
  let slides = deck.getSlides();
  let dataRange = SpreadsheetApp.getActive().getDataRange();

  // I modified the below script.
  const ar = ["{{item#}}", "{{results#}}"];
  const [, ...sheetContents] = dataRange.getDisplayValues();
  const values = [...Array(Math.ceil(sheetContents.length / 3))].map((_) => sheetContents.splice(0, 3)); // ref: https://github.com/tanaikech/UtlApp#splitarray
  slides.shift();
  slides.forEach((slide, i) =>
    values[i].forEach((g, k) =>
      ar.forEach((f, j) => slide.replaceAllText(f.replace(/#/, k + 1), g[j]))
    )
  );
}

When this script is run to the modified template Google Slide,
- On page 2, {{item1}} - {{results1}}, {{item2}} - {{results2}} are replaced with A - This is item A., B - This is item B. and C - This is item C.
- On page 3, {{item1}} - {{results1}}, {{item2}} - {{results2}} are replaced with D - This is item D., E - This is item E. and F - This is item F.

answered Nov 21, 2023 at 6:27

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Arif Ahmed Over a year ago

Thanks for this. It works perfectly fine. Can you explain this part ...Array(Math.ceil(array.length / size))].map((_) => array.splice(0, size)

Tanaike Over a year ago

@Arif Ahmed Thank you for replying. I'm glad your issue was resolved. About [...Array(Math.ceil(sheetContents.length / 3))].map((_) => sheetContents.splice(0, 3));, the array of sheetContents is split every 3 rows. Because in your situation, 3 rows are used on a page. So, I split the rows of the Spreadsheet every 3 rows. The details of this can be also seen at my repository.

Arif Ahmed Over a year ago

Thanks for the explanation. I went over your utility tool box. It's quite helpful. Thanks once again.

Arif Ahmed Over a year ago

The above solution works well, how can we group in uneven chunks? The above solution works for size 3. How can we group in uneven chunks 2,3,2,3,4,2,2,1,9 ?

Tanaike Over a year ago

@Arif Ahmed About your new question, I would like to support you. But that is different from your question. So can you post it as new question? Because when your initial question is changed by comment, other users who see your question are confused. By posting it as new question, users including me can think of it. If you can cooperate to resolve your new issue, I'm glad. Can you cooperate to resolve your new question?

|

Collectives™ on Stack Overflow

Group rows in google sheets and create google slides via appscript

1 Answer 1

Modified script.

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script.

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related