• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

UpBuild

Values-Driven Technical Marketing

  • About
    • Mission & Values
    • Meet the Team
    • Be a Builder & Work at UpBuild
    • Offices & Location
  • Expertise & Services
    • SEO
    • Analytics
    • CRO
    • Custom Training & Education
    • Technical Marketing
  • Working With Us
    • Pricing
    • Non-Negotiables
    • Case Study: Zoomdata
    • Case Study: Indow
    • Case Study: Twistlock
  • Blog
  • Contact
automate data in sheets into formatted google slides

How to Inject Data from Google Sheets Into a Google Slides Presentation

December 10, 2019 by Danielle Rohe 82 Comments

Working on websites, we work in rows and rows of data. A lot. But what happens when an executive wants to know more about that project you are notating from a data export? You have to turn your mind-numbing spreadsheet with filters and color coding only you might understand into a more digestible format. Now comes the hours and hours spent copying over information into a presentation. 

Not anymore!

With Google Apps Script, you can inject data from Google Sheets into a template in Google Slides and generate hundreds of pretty slides in just minutes.

Let’s say your executive wants to be able to see how well inbound marketing is working, but doesn’t want to look at a giant table of URLs stacked on top of each other. We will use an export of the Behavior > Landing Pages report from Google Analytics and generate a slide for each page in the report with the metrics bulleted out.

Getting Started In Your Container’s Script Editor

The first thing to understand about the Google Apps Script is that the script needs to be built into a “container,” which is a file of the G Suite Service that will contain the script to be run. The container usually has some initial setup for the script too.

For our case, our container will be a Google Slides file. It will have a title slide and then a slide that will serve as a template. 

Make a copy of this container presentation

Once you’ve got your container set up, just open Tools > Script Editor to start writing your function.

Access Google Slides script editor in the tools menu

While you could just dive right in and run the script (just push play) within this container, let’s go over how it works, so you can start customizing it for your own needs.

run your script using the play button in Apps Script editor

Using the Script Editor

The first time you run your script, you will be required to allow Google the ability to view, edit, etc your apps.

window to give your Apps Script access to your Google apps

If you are running a script from a copy of someone’s container (like this example), you might see a screen before the above approval telling you Google hasn’t verified the source (but we are right now so you know there isn’t any malicious script!). As long as you trust the code, you will need to open the Advanced options and go ahead to the function. 

continue on to unverified app

Once approved, the script should continue running.

When a script is running, you’ll see an alert at the top of the editor. Once it is stopped (and provided that no error message has appeared), your script should have completed!

alert that your Apps script is running

As you update, make sure you are saving! When you have unsaved changes, a little red asterisk will appear.

saving inside Apps Script editor

To see the outputs of your variables, use “Logger.log().” Access the logs from within the View menu.

access logs in Google Apps Script

Understanding the Google Apps Script Classes

For those of you savvy in JavaScript, you may think you are ready to get coding. However, there are a couple of differences. For starters, using Logger.log instead of console.log. The biggest difference, though, is that you won’t be traversing a DOM. Instead, you’ll have to understand the different classes for the various apps. 

**THIS IS THE MOST IMPORTANT ASPECT OF USING GOOGLE APPS SCRIPT!**

Each app has a hierarchy of classes. Once you understand the hierarchy, you can then move about the app and reference the appropriate class documentation (which will be linked each time a new class is referenced here), to determine what properties and methods are available for the class.

Sheets Classes

For Google Sheets, everything begins with the parent class SpreadsheetApp. While there are many classes within, we will be using the Spreadsheet, Sheet, and Range classes. These classes have a nicely defined hierarchy, as pictured below.

Google Sheets App Classes hierararchy example

Slides Classes

The Slides service is SlidesApp, which works with the Presentation class. We will also be using the Slide, Shape, PageElement, and TextRange classes. When reviewing the documentation you’ll see that sometimes these classes can be children or siblings, which makes for a messier hierarchy. The “Return Type” in the methods documentation is key to ensure your output is the type you need and expect.

Accessing Your Apps in The Script

Both the parent classes we are working with can use the following methods for opening them.

  • openByUrl()
  • openById()
  • getActive…()

If your script is contained within a Spreadsheet, you can use getActiveSpreadsheet(). Since we are working out of a Slides container, we will use getActivePresentation().

It will likely be easier for all team members to use the full URL of their Sheets, so we will use that method for Sheets. To more easily switch out spreadsheet URLs, we will put the URL into its own variable. The variable name will then be used within the functions. 

Let’s declare the files we are working with:

You’ll replace “full url here!” with the actual URL of the spreadsheet, including the /edit at the end. Be sure to keep the quotes to ensure your dataSpreadsheetUrl variable is a string. Here’s how it appears in the completed script:

screen of code that includes the Sheets url as a string variable

The actual Spreadsheet we will be working with is this Sheet generated from a Landing Pages report from Google Analytics (GA), but with dummy data. The typical daily index at the bottom is  removed. It is important to retain the structure of the export as the script will be looking for the data range based on the format and mapping them based on the columns. That spreadsheet’s URL has been placed into the dataSpreadsheetUrl variable within the script.

Getting Your Data From Google Sheets

Now that we have declared a Spreadsheet, we need to get the correct Sheet (aka tab) within it and set the range of data we will pass to Slides.

The variable “ss” is now a Spreadsheet type. A method of Spreadsheet is getSheetByName(). 

The default tab name from generating this report from Google Analytics  is “content-landing-pages.” Thus we will declare the sheet with getSheetByName(‘content-landing-pages’). 

screenshot of JavaScript variable to declare correct Google sheet by name

Now we need to declare the range of data within our Sheet. The getRange() method on the Sheet class has several variations that pass different parameters. For the sake of this project, we should always know the exact range, so we can use the A1 Notation version. However, if you need to automate from reports where the range is unknown, the trick is to set a variable for sheet.getLastRow() and sheet.getLastColumn() to put those variables in as the numRows and numColumns parameters, respectively. 

When reviewing our data of just the top 10 Landing Pages, our A1 Notation range, which does NOT include headings, is A8:J17. If you export more results than that, be sure to change the row from 17.

Once you have a Range, you can then use the getValues() method, which generates a two-dimensional array ([][]) with the first index being by row and then by column. I’ll refer to these later as the “rows” array and the “columns” array. Note that hidden rows and columns in the spreadsheet will still be grabbed.

We will get the range and values in one declaration:

screenshot of JavaScript variable to set the Sheet range and get the values within

Using “Logger.log(values)” immediately after that declaration, I was able to see what our data looks like in this two-dimensional array.

two-dimensional array of values from a Sheets Range

Now that we have data, let’s place it into our presentation!

Find the Appropriate Slides Inside the Presentation

As mentioned previously, containers for Apps Script usually have some sort of initial setup. For Slides, you definitely need to set up a template slide or slides. The one we are working from is set up to have a title slide and then a two-column layout slide with placeholder tags that will indicate where to inject the data. We will be duplicating our template slide for each row of data we have.

Our template slide, pictured below, has {{landing page}} in the title with some of our analytics data bulleted out, using curly braces to identify placeholder tags. I’m using curly braces to set these off from other formatting and text that will not be replaced.

template Google Slides slide using curly braces for placeholder text

First, we need to access our template slide. The Presentation we’ve declared in our “deck” variable can now use the getSlides() method to get an array of all of the slides.

screenshot of JavaScript variable to get all slides in Slides presentation

Now we have an array with a Slide at each index. Because we have a title slide before our template slide, we will be using slides[1] to access the template slide, as this is a 0-based index.

screenshot of JavaScript variable declaring the template slide at index 1

Since we will be adding slides to the presentation to create a new slide for each row of data, we need to continuously update our array of slides (as we did when declaring the slides variable) and its length, which we do by:

screenshot of JavaScript code getting the slides.length

Now we need to loop through the “rows” in our two-dimensional array in order to replace the individual text for each Landing Page. Because our values are representative of a webpage, I will set “page” as the element we process in the callback function.

screenshot of forEach loop on values variable

Just for a nice check that we didn’t get an empty row in our data range, I include a conditional to ensure that the Landing Page field, which is now the first in our index of columns, exists.

screenshot of conditional inside for loop checking for data

Within our loop we need to do the following actions:

  1. Map all of our “columns” data to variables
  2. Duplicate our template slide and declare the new slide
  3. Replace our placeholder text on our new slide. This will have to be done inside another loop of all of the shapes within the slide
  4. Move our new slide to the end of the presentation to retain the order

Map All of Our “Columns” Data to Variables

Right now we are in a loop of each row of data from our two-dimensional array. Each row is an array of data from the columns in our range. We need to review our columns headings and map them to variables in our Apps Script.

column headings of a Google Analytics Landing Pages report

We are only going to use the data from Landing Page, Sessions, % New Sessions,  Pages/Session, Goal 1 Conversion Rate, and Goal 1 Value. 

As mentioned with the conditional statement, page[0] is Landing Page, so page[1] is Sessions and so on.

screenshot of variables for each column of data

Those already quite familiar with coding know that numbers can be quite fun irritating. While we want to represent the newSessions and goalRate as percentages, our script has pulled it from the sheet as a plain ‘ol number that is also not neatly rounded. So, we need to do some math to get the output we’d like.

screenshot of JavaScript math to make percentage and round to two decimal places

Duplicate The Template Slide and Declare the New Slide

With our templateSlide variable, we can run the duplicate() method, which will place the duplicate after the templateSlide. We need to then update our slides array to find the duplicate slide and declare it. Since we started with only two pages, the new slide will be the third in the presentation, or second in our index. We do all this because want to retain our template for the next slide in our array.

screenshot of JavaScript to duplicate template slide, update array, find new slide in array

Replace Our Placeholder Text

As mentioned, this step requires another loop. We have to use the getShapes() method on the Slide, which will return an array of all shapes on the Slide. Within the array, each Shape can use the getText() method to return the TextRange, which will then allow us to replaceAllText() for each of our placeholder tags.

script to replace text in a google slides presentation using Apps Script

We’ve gone ahead and closed out that forEach loop on the shapes, but we are still in our “rows” loop from the two-dimensional array, or values variable. Before we close that we want to update the order of slides.

Move the Slide

Update the slides array and length so our current page in the values array will always be the last slide. Use the move() method to move to the Slide to the end of the presentation.

screenshot of JavaScript that updates the slides array and length and moves new slide to the end of presentation

Don’t forget to remove your template slide.

screenshot of JavaScript code to remove the template slide from the presentation

Now save and run your script. Once the alert at the top of the editor is gone (and error free), head back to your container presentation to bask in all your automated glory.

slide with content in place  of the placeholder text and other slides visible in the presentation

While this is just one example, knowing how to inject data from several rows of Sheets into Slides has so many uses in digital marketing. Think of the many reports that need deeper analysis page by page: content audits, technical requirements, mapping event tracking, etc. Hopefully this gets you exploring the possibilities within Google Apps Script!

Related

Filed Under: Analytics

Stay in the Know

Reader Interactions

Comments

  1. Mary says

    January 26, 2020 at 6:58 pm

    This is glorious.

    Reply
    • Danielle Rohe says

      January 27, 2020 at 9:22 am

      Thank you; don’t hesitate to reach out with any questions!

      Reply
      • Kelly Lewis says

        May 8, 2020 at 2:22 pm

        I don’t understand how I tie my variable fields to the API script? I just have two…one called “FullName” and one called “College”

        Reply
        • Danielle Rohe says

          May 11, 2020 at 7:47 am

          Hi Kelly,

          Thanks for reaching out. I’m sorry I don’t quite understand your question. It sounds like you may be wondering where to declare the values within your Sheets into the script? Make sure you updated the correct range of data in the variable declaration for “values” var values = sheet.getRange('{{your data range from your Sheet here}}').getValues. Then from within the loop of “values” values.forEach(function(page){ that is where you would set your variables, which is explained in the Map All of Our “Columns” Data to Variables section of the post. Let me know if you need any additional help.

          All the best!
          -Danielle

          Reply
  2. Thomas says

    February 10, 2020 at 1:52 pm

    Hi!

    Thanks for the tutorial, it’s working nicely!

    How would you amend the script so that the script auto-refreshes the slide where the information is pulled to? What I mean is if I modify the data in the Google Sheet, can the slide show the amended numbers automatically without having me to re-run the script manually?

    Reply
    • Danielle Rohe says

      February 11, 2020 at 2:40 pm

      Glad to hear it is working well for you!

      Because the data overwrites the generic placeholders in the presentation, you’d have to start fresh with a whole new presentation. In that case, you may want to contain the script in the spreadsheet instead of a presentation. This script updates how the “deck” and “ss” variables in my original script get set when contained in a spreadsheet and needing to access a newly created presentation (with a unique name which is set by the current date & time) to access: https://codepen.io/dani_r/pen/zYGvvpp.js. While there are triggers for onChange in Spreadsheets, any time a cell changes it would generate a whole new presentation with the latest date & time stamp in the name, so I don’t really recommend that. Possibly just moving the script into the Spreadsheet will make it more convenient to run it as you finalize edits to the data.

      -Danielle

      Reply
  3. Irwan says

    March 14, 2020 at 8:10 pm

    Nice tutorial, could you share the google slide also? Thanks!

    Reply
    • Danielle Rohe says

      March 16, 2020 at 1:16 pm

      Thanks! You can make a copy of the Google Slide from this link here: https://docs.google.com/presentation/d/1WWvln_EEn9kJaSbbyasBDa-V-HXzy4SYwDdKxCagtRs/copy

      Reply
  4. Vicky says

    April 8, 2020 at 4:55 am

    Thank you so much for the tutorial; its been really easy to follow and exactly what I was after.

    My slide presentation has 2x slide templates, how do I adapt the script to suit?

    Reply
    • Danielle Rohe says

      April 8, 2020 at 8:19 am

      Hi Vicky,
      I appreciate your feedback and am so glad to hear this is working well for you!
      For different slide layouts, start your template with the various layouts as individual slides and the placeholder copy you’d want in them. Then when you initially get your array of slides (var slides = deck.getSlides()), it will have multiples for you to assign, like var titlePage = slides[0], var sectionTitles = slides[1], var twoColLayout = slides[2], and so on.
      Let me know if you have any other questions!
      -Danielle

      Reply
  5. Hash says

    April 12, 2020 at 2:33 pm

    This is one of the best tutorials I’ve seen. Is it possible to create graphs in the Slide from the data in the Sheet?

    Reply
    • Danielle Rohe says

      April 13, 2020 at 10:47 am

      Thanks for your feedback! It looks like you would probably have to create the chart in Sheets and use the SheetsChart class to embed it into your Slides: https://developers.google.com/apps-script/reference/slides/sheets-chart.

      Happy scripting!
      -Danielle

      Reply
  6. Katelyn says

    April 14, 2020 at 8:56 am

    This is wonderful! Thank you so much! We also have a question asking for an uploaded image. Is there a way to have the image that is uploaded insert into the slide as well?

    Reply
    • Danielle Rohe says

      April 14, 2020 at 11:07 am

      Hi Katelyn, I appreciate you reaching out! If you have an image that you’d want as part of every slide, like a logo, I’d include that with your placeholders on the “template slide” or slides (see another comment about having multiple layouts). Otherwise if you need to include it via the Apps Script on certain slides, you can use the Image Class for Slides App: https://developers.google.com/apps-script/reference/slides/image.

      All the best!
      -Danielle

      Reply
  7. Chanel says

    April 20, 2020 at 11:54 am

    Thank you so much for the tutorial – It is very easy to follow! If a row is blank (i.e. page[0] and page[6]) within the data range, I would like to prompt the script to use a second template (which will just be a blank page serving as a section separator). Any advice on how to go about that?

    Reply
    • Danielle Rohe says

      April 21, 2020 at 7:54 am

      Hi Chanel,

      Thanks for your feedback! I want to make sure I answer both parts of your question.
      To set up another template slide:
      Start your template with the various layouts as individual slides and the placeholder copy you’d want in them. Then when you initially get your array of slides (var slides = deck.getSlides()), it will have multiples for you to assign, like var titlePage = slides[0], var sectionTitles = slides[1], var twoColLayout = slides[2], and so on.
      To use the other template while looping through your pages:
      You’ll probably want to use an if statement. In my script, I use one to check to make sure there is data in the row with simply if(page[0]) {//create the slide} so you could add then add an else {//do the other thing} after that, which should catch all rows without data. The else statement is where you would generate your section slide that you defined from deck.getSlides().

      All the best!
      -Danielle

      Reply
  8. April B says

    April 21, 2020 at 11:54 am

    This is an amazing tutorial and I am new to programming! My slides are populating perfectly, except for my images. The image is stored in the sheets as a url. I have tried several different ways to get them to populate. heres a few i’ve tried and have failed.
    //var Img = UrlFetchApp.fetch(Images).getBlob().getAs(‘image/png’);
    //var Cht = UrlFetchApp.fetch(Chart).getBlob().getAs(‘image/png’);

    //var Chartpng = (newSlide.getImages());
    // Chartpng.forEach(function(image){
    // image.getContentUrl(Chart).getAs(‘image/png’);

    // });
    // var Imagepng = (newSlide.getImages());
    // Imagepng.forEach(function(image){
    // image.getContentUrl(Images).getBlob().getAs(‘image/png’);
    //image.getTitle().replace(‘imagepc’,Imagepng);
    // });

    Could you set me in the right direction. what am I missing?

    Reply
    • Danielle Rohe says

      April 22, 2020 at 11:29 am

      Hi April,
      I’m so glad to hear you found this guide helpful in getting you started!

      What I’m not seeing from your example code is where you are pulling in the images from the sheet. These don’t appear to be elements that can be pulled from ranges. Instead, within the Sheets class there are methods for getImages() or getCharts() to use depending on what type of image it is in in the sheet. Once you get the appropriate type, then you’d probably use the .getAs() method as you were to set it for the type of use you need in the Slides.

      All the best!
      -Danielle

      Reply
  9. sgk says

    April 21, 2020 at 1:28 pm

    Super.. Works like a charm!!

    Reply
    • Danielle Rohe says

      April 22, 2020 at 11:09 am

      Thanks for sharing your feedback; it’s great to hear!

      Reply
      • Osvaldo Calzada says

        August 28, 2020 at 10:36 pm

        This was such a great tutorial–Thank you! Everything is populating perfectly for me, but I am having the same issue/similar issue. I am also pretty new to programming, and I am trying to include an image from google sheets that is also stored as a URL. The images are on my drive folder, but the URL is on sheets. I defined

        var images = sheet.getImages();

        and tried using the similar UrlFetchApp code, but I keep getting an ‘Attribute provided with no value’ error.

        Reply
        • Osvaldo Calzada says

          August 28, 2020 at 10:57 pm

          I’ve also tried defining similarly to other comments where we’d define

          var url = page[column with url #];

          then
          newSlide.insertImage(url);

          but I keep getting the error:
          Exception: The image at URL could not be retrieved. Please make sure the image is valid and publicly accessible.” Since the images are in my google drive folder I figured it was a security issue, and changed them all to ‘anyone with the link.’ But still no luck.

          Do you have any suggestions or other steps you think I should try? If you have any advise, I would greatly appreciate it. Thank you
          Osvaldo

          Reply
          • Danielle Rohe says

            August 31, 2020 at 12:33 pm

            Hi Osvaldo,

            This error message does sound like a problem with the image file. Double check that the URL doesn’t need updated since the sharing settings changed. Otherwise, be sure that the image(s) meet these guides: Images must be less than 50MB in size, cannot exceed 25 megapixels, and must be in either in PNG, JPEG, or GIF format.

            All the best!
            -Danielle

        • Danielle Rohe says

          August 31, 2020 at 12:35 pm

          Hello Osvaldo,
          I think you may have already resolved this on your own since you were using the .insertImage(url) method in another question. The sheets.getImages() method will look for actual images, but if you just stored the image URLs then you’d get them as you do all the other values within a range.

          Let me know if you have any other questions!
          -Danielle

          Reply
          • Osvaldo Calzada says

            August 31, 2020 at 8:10 pm

            Danielle,

            Again, thank you for your help, but I unfortunately have not. I’m using ‘google forms’ to generate the sheet, and this is where the image URL is being saved. I tried doing all the other methods but still keep getting the same error: “The image at URL could not be retrieved. Please make sure the image is valid and publicly accessible.” I’m pretty sure it’s due to the image issue, since I am able to import a generic image from google images, but not the images in the sheet. Am I supposed to ask for special permissions, or is there something different that needs to be done if the images are coming from a google form? I’ve tripled checked my security settings and file types, and that doesn’t seem to be the issue.

            Thanks,
            Osvaldo

          • Danielle Rohe says

            September 1, 2020 at 9:52 am

            Hi Osvaldo,
            Take a look at this forum thread about trying to see images from Google Form uploads in the sheet: https://support.google.com/docs/thread/16033957?hl=en You may want to check the sharing settings for the folder those images are saved in. Otherwise, it may be that the URL generated from an image uploaded into Google Forms isn’t the “viewable” URL and so you’d have to update those URLs as described in the thread, which unfortunately sounds like it may take away the possibility for automating those.

            All the best!
            -Danielle

  10. Omer says

    April 27, 2020 at 12:54 pm

    Thank you for this amazing tutorial.
    If we need to put the values in a table , (for example we want to create a table for for just avg. session duration and goal conversion rates (2×1 table)) the code seems that it dont’t recognize the texts in the tables in the template slide. What can you suggest for replacing the tags in the table?
    Could you please upload an example for creating charts in Slides with data from Sheets?
    Best,

    Reply
    • Danielle Rohe says

      April 27, 2020 at 2:26 pm

      Hi Omer,

      Thanks for reaching out! If your table is being generated from the information for one “page” (which it sounds like), then you could try to set your template slide up with a table and use the placeholder text within the appropriate cells. If it can’t find the placeholder text, you may need to look for the Table class and the appropriate cells to then replace the information within the TableCell to then use the getText() method from the cell.
      If you are trying to generate a table from multiple rows from your Sheets range, then you could use the Table class to append rows and columns as you are looping through your values values.forEach(function(page){}.

      All the best!
      -Danielle

      Reply
  11. Jay says

    April 29, 2020 at 5:17 am

    Thank you so much for this tutorial!

    How would I be able to do this, but create a new presentation for each row based on the template? Ideally into a specific Drive folder too?
    I think .makeCopy(slidename) might work, but I am not sure how exactly to add this into my code when looping through the rows. “slidename” would be unique for each row.

    Also, if I want to insert an image in place of a placeholder tag, what should the code be and how could I insert it into the loop?

    Thanks again!

    Reply
    • Danielle Rohe says

      April 29, 2020 at 5:23 pm

      Hi Jay,

      If you want to create new presentations for every row of data in a spreadsheet, then you probably want to have the Apps Script contained in the spreadsheet itself. Another reply asked about something similar, so I built this codepen that shows how you can create a templateDeck variable from inside a Sheet’s script editor. That solution creates new Slides with the date and time appended, which you probably don’t need. You’d just replace the newDeckName variable with whatever you want or just pass that name in the makeCopy("Slide-Name-Here").

      As for an image, the answer depends on if you are pulling your image in from the data in the sheet or not and if it is a chart or an actual image. Within the Sheet class there are methods for getImages() or getCharts() to use depending on what type of image it is in in the sheet. However, in order to place it in your Slide, you’d need to reference the Image Class for Slides.

      Thanks for reaching out!
      -Danielle

      Reply
  12. Spencer says

    May 22, 2020 at 11:38 am

    I don’t normally take the time to comment on tutorials, however I feel the need to voice my opinion on this one:

    This is one of the most thorough and well-made tutorials I have ever come across online. I often find myself having to make tutorial-like documents for internal use to instruct employees on how to do something technical, so I understand the time and effort that went into making this. Seriously, great job and thank you for making it.

    Reply
    • Danielle Rohe says

      May 26, 2020 at 9:18 am

      I really appreciate your feedback! Don’t hesitate to reach out if you have any questions.

      Reply
  13. HJ says

    May 25, 2020 at 9:45 am

    This is great.

    Is there a way i can add a column with image url’s and load them the same way? been searching for a while now and i can’t seem to find a solution.

    Thanks again.

    Reply
    • Danielle Rohe says

      May 26, 2020 at 9:33 am

      Thanks for reading and responding! It sounds like if you have the image URL in your data then you can use insertImage(imageUrl) from the Slide or Page Class. Since this a method on the Slide class, you’d do that within the loop of our data on the newSlide being created values.forEach(function(page){//declare your imageUrl as page[{{index of image url column}}] and then insert image in here using newSlide.insertImage(imageUrl)}

      All the best!
      -Danielle

      Reply
      • Justin says

        May 30, 2020 at 4:01 pm

        Hi Danielle,

        I found this article incredibly helpful. Thank you for taking the time to put this together! I am trying to use the insertImage(imageUrl) method from the Slide class, but for some reason it keeps saying “slides.insertImage is not a function”. I believe that I have the same goal as HJ. I would like to insert an image into Slides from a url in the google sheet.

        I would appreciate any additional guidance you can provide.

        Reply
        • Danielle Rohe says

          June 1, 2020 at 7:40 am

          Hello Justin,

          Thank you for your feedback!

          The slides variable, when it is declared using .getSlides(), is an array, so the insertImage method won’t work on that class. You’ll need to be on a Slide or Page class type in order to use the insertImage(imageUrl) method. You can do that inside the loop of the slides, like slides.forEach(function(slide){slide.insertImage(imageUrl)}. Or you could do it if you have identified just one slide of the array, like slides[2].insertImage(imageUrl).

          All the best!
          -Danielle

          Reply
          • Gretchen says

            June 8, 2020 at 3:49 pm

            This is incredibly helpful. I put the image call just above the var shapes group. I found it initially put every single image on every single slide (including the cover). I changed the call slightly to be:
            slides.forEach(function(slide){newSlide.insertImage(ImageUrl)}

            This sort of works – it puts the correct image on each slide, but it puts it there 3 times. Why 3? How do I get it to maybe just do that once?

          • Danielle Rohe says

            June 9, 2020 at 7:38 am

            Hi Gretchen,
            I’m glad to hear you were able to get the images inserted.

            If I am understanding correctly, you are inside of the values.forEach(function(page){ loop. Within that you should have all of your columns mapped to variables, including one for var ImageUrl. This should represent the current row of data to be inputted into a slide. For this row, we are creating a newSlide variable to pass all of this row’s data only into. So you don’t need to use a loop for slides here. You can just use that newSlide variable and do newSlide.insertImage(ImageUrl).

            All the best!
            -Danielle

  14. Gretchen Scheiman says

    June 9, 2020 at 9:58 am

    Thanks Danielle! That did the trick. You’re fantastic and thank you so much for this.

    Reply
    • Danielle Rohe says

      June 9, 2020 at 10:46 am

      Great to hear!

      Reply
    • Maggie says

      June 9, 2020 at 1:27 pm

      Do you have to handle urls stored in the spreadsheet, that are to images on google drive differently? I’ve read where I would have to save them to a website, but that’s not an option as this is part of a project for my work. I keep getting the following error: Exception: The image at URL https://drive.google.com/open?id=12Zdpih0j0ulr6ZZMT41nu7Z (deleted part of it) could not be retrieved. Please make sure the image is valid and publicly accessible. (line 34, file “Code”)

      Reply
      • Danielle Rohe says

        June 9, 2020 at 2:17 pm

        Hi Maggie,

        I’m not aware of any difference in using Google Drive URLs other than making sure the sharing settings for the Drive file/image make it available to be accessed by anyone.

        All the best!
        -Danielle

        Reply
    • Tom says

      July 9, 2020 at 1:04 am

      Love this tutorial! Thanks.

      I am a complete novice and just can’t get the newSlide.insertImage(); to work. There will be a different photo on each slide and I have defined var Photo = page[10]. This column holds the google drive links.

      Can I insert newSlide.insertImage(Photo); just after the var statements?
      I want the image to go in at a {{photo}} place holder.

      Can you help or point me in the right direction? Many thanks again

      Reply
      • Danielle Rohe says

        July 9, 2020 at 10:04 am

        Hi Tom,

        Is your var Photo declaration in the values.forEach(function(page){} loop where you have mapped all of your other columns as well? You can’t put the newSlide.insertImage(Photo) method there since we don’t update the newSlide variable until further down in the script. Once you have declared your “newSlide” variable then you can try adding that.

        As for inserting the image in a text placeholder, I don’t believe that will be possible since replaceText() is only available on a TextRange class. Instead, you could try including a placeholder image. In this case, you’d use something like var imagePlaceholder = newSlide.getImages()[0] (the 0 index is assuming you only have one placeholder image on the page) and then imagePlaceholder.replace(Photo). Again, this would be after you’ve declared your newSlide. Here’s more information on the getImages() method and the replace() an Image method.

        All the best!
        -Danielle

        Reply
        • Tom says

          July 9, 2020 at 4:38 pm

          Thanks! Really appreciate your time. I will have a read and edit later today.

          Reply
  15. Thom says

    June 22, 2020 at 2:32 pm

    This has been very helpful, many thanks. I have got my basic script working, which, as a non-programmer, I’m dead chuffed about! Very well written and easy to understand.

    Reply
    • Danielle Rohe says

      June 30, 2020 at 12:01 pm

      Thanks for your feedback, Thom! I’m so glad you found the tutorial easy to follow. Maybe this will spark a new interest in programming for you; I know how that excitement over seeing your code running and working can lead to trying more. Let me know if you have any questions as you add to/customize this.

      Reply
  16. Craig Earl says

    July 1, 2020 at 5:56 am

    What a brilliant and easy to follow tutorial. It’s made my life a ton easier! Thank you!

    I do have one question I can’t seem to figure out an answer to. This is great for pulling data content into the slide deck but how would I pull a cell’s data AND the same cells background/fill colour into the slide too?

    Reply
    • Danielle Rohe says

      July 1, 2020 at 9:42 am

      Hi Craig,

      I’m so glad to hear that this has helped make life easier!

      For the background color, you will have to change the way you are pulling information from the Sheet. The getRange() method used is looking at several cells at once to bulk “export” the values within. The getBackground() method for a Range only pulls the top-left most cell’s color, so you will need to find a way to get down to each individual cell within your range to check it with getBackground(). Then once you have that information you will have to pass it over into your Slide and set it using the TextStyle Class on the appropriate variable. That variable will have to be a TextRange Class, which aside from replacing specific text I’ve found difficult to use. If you can figure out how to define the appropriate copy within your TextRange, you could apply a conditional within your script, like if(goalRate < 1) {//do the text styling here}.

      Best of luck!
      -Danielle

      Reply
  17. Anna says

    July 1, 2020 at 11:16 pm

    This is amazing and has really helped me out! Thank you for the great tutorial.

    Like others in the comment section I’m trying to work with 2 template slides, always needing both copied and updated but I can’t figure out how to make it work. Do you have a tip on how do that?

    Reply
    • Danielle Rohe says

      July 6, 2020 at 8:19 am

      Hi Anna!

      I’m glad this has been helpful! As for having two template slides, let’s assume you have a title slide so make your two templates slides slides 2 & 3 in your presentation and declare them then using the 0-based indexing as templateOne = slide[1] and templateTwo = slide[2]. Then you will need to duplicate each of those. When you duplicate a slide it adds the copy immediately after it so that means your “newSlide” for templateOne is in slide[2] and the “newSlide” for templateTwo is in slide[4]. Here’s a copy of the container with Sessions data on one slide and Conversions data on another. Take a look at the updates to the script there. https://docs.google.com/presentation/d/1nFgEfQO_2ZgT7nkTnqFjmOjeUyfyKifsJW26S4whsD8/copy

      All the best!
      -Danielle

      Reply
  18. Roberto says

    August 1, 2020 at 9:08 pm

    Thank you so much for the script and the explanation. Great job!

    Reply
  19. Sergio says

    August 5, 2020 at 12:46 pm

    Thank you for sharing this information!
    How can I trigger the script every time the spreadsheet changes? (on edit or on form submit)

    I want to generate new slides each time my spreadsheet receives a form submition. Is that posible?

    Reply
    • Danielle Rohe says

      August 5, 2020 at 2:23 pm

      Hi Sergio,

      In order to have a trigger on the data, you’d need to have the Apps Script contained inside of the Sheets file instead of a Slides file. I responded to an earlier comment on how to get started with running the script inside of Sheets instead. In that instance, they wanted to create new copies of the Slides file, but you could just directly link to one specific file to update. Then you have to use the appropriate triggers within Sheets. Refer to this resource, in which you’ll see that the onchange and onedit triggers probably won’t work for your needs. Instead, you’ll want to focus on the onsubmit event for the Sheets file generated from a Google Form. You’ll need to ensure that you add a new field to your sheets that notes if a row of data is already in your presentation, much like this example improvement for sending emails from form submissions and checking to make sure an email hasn’t already been sent first: https://developers.google.com/apps-script/articles/sending_emails#section_2_improvements

      Happy scripting!
      -Danielle

      Reply
      • Sergio says

        August 9, 2020 at 8:06 pm

        Thank you Danielle for your help!

        Sergio

        Reply
  20. DD says

    August 16, 2020 at 7:59 am

    Wow!
    I found all the other “App Scripts” samples on the internet too time consuming to be worth investing on the initial learning curve (and I actually code for a living).
    Your example was so optimal, it helped me accomplish what I needed with a very shallow learning curve.
    And all my time was spent on my requirements, not on a learning curve.
    Thanks Danielle!

    Reply
    • Danielle Rohe says

      August 17, 2020 at 8:28 am

      I’m so glad to hear this got you what you needed; thanks for sharing!

      Reply
  21. Ricardo Campo says

    October 29, 2020 at 3:31 pm

    Hola.. Muy buen tutorial, me fue de mucha ayuda.

    Sabes si hay forma por medio de script hacer la descarga en .pdf solo de la segunda diapositiva?. Si tengo en la primera diapositiva la plantilla, me gustaría descargar solo la nueva diapositiva en formato .pdf.

    Muchas gracias.

    Reply
    • Danielle Rohe says

      November 2, 2020 at 8:29 am

      Hi Ricardo,

      Thanks for reading and commenting! There is a .getDownloadUrl() method for the File class (in this case it would be applied to our “deck” variable, deck.getDownloadUrl()). However, that method can’t be used by the URLFetchApp, so you can’t run the script to fetch it. You’d have to open the download link manually. You could do that by inserting an HTML window/dialog with the link embedded, like this example.

      In order to not include the template slide, be sure to add templateSlide.remove() before you start the PDF portion.

      Thanks again!
      -Danielle

      Reply
  22. Nicolas says

    October 29, 2020 at 3:41 pm

    Best tutorial ever!

    Thanks for sharing.

    I’m not a developer an was able to follow and adapt to my needs.

    Question, what if I’d like to create a new slides file instead of replacing the info in the source?

    Reply
    • Danielle Rohe says

      November 2, 2020 at 8:46 am

      Nicolas,

      I’m so glad to hear you were able to adapt this to your needs! To create a new slides file, you will want to change out the variable for “deck.” Instead of using the active presentation you will want to declare the active presentation as a blob (data file) and then create a new file from that blob. Something like,
      var blob = slidesApp.getActivePresentation().getBlob();
      var newDeck = DriveApp.createFile(blob);

      Then you would continue on using “newDeck” in replace of the “deck” variable from the original example. I haven’t tried this yet myself, but I imagine the script should still run on the new presentation file even though it is contained inside of the template presentation. Once the script is complete, you’ll have to open the new presentation file from your My Drive unless you do something like:
      newDeckUrl = newDeck.getUrl();
      SpreadsheetApp.openByUrl(newDeckUrl);

      All the best!
      -Danielle

      Reply
  23. Jhay says

    November 4, 2020 at 11:13 pm

    Cool.

    Thanks for this tutorial.

    Just a question, is it possible to have the slides display the data for multiple rows on the sheets instead of creating a new slides per rows without using the link to spreadsheet option?

    Reply
    • Danielle Rohe says

      November 5, 2020 at 10:19 am

      Hello and thanks for your feedback!

      If you wanted more than one row of data per slide, you could set the range differently in the “values” variable. Keep in mind that the getValues() method on a range returns a two-dimensional array. From there, I’d just log what data is pulled when using different indexes on the “values” variable to determine how best to capture and display the specific values within.

      Best of luck!
      -Danielle

      Reply
  24. Alex Yaroslavsky says

    December 3, 2020 at 7:26 am

    Danielle,

    Thanks for your tutorial. I tried running the demo script, but had a problem signing on:

    Sign in with Google temporarily disabled for this app.
    This app has not been verified yet by Google in order to use Google Sign In.

    Can you suggest a solution?
    Thank you,
    Alex

    Reply
    • Danielle Rohe says

      December 3, 2020 at 8:32 am

      Hi Alex,

      Try signing into your Google Account and then make a copy of the container file here to save in your Drive: https://docs.google.com/presentation/d/1WWvln_EEn9kJaSbbyasBDa-V-HXzy4SYwDdKxCagtRs/copy
      When you try to run the script from your copy, you may be prompted as shown in the “Using the Script Editor” section of the post.

      All the best!
      -Danielle

      Reply
    • Alex Yaroslavsky says

      December 4, 2020 at 7:52 am

      Thank you, Danielle. The script is working now.

      I will be pulling data from Google Forms -> Google Sheets, so will need to make the data range dynamic. I created two new variables above the ‘values’ variable:

      var lastColumn = sheet.getLastColumn()
      var lastRow = sheet.getLastRow()

      This is the old ‘static’ example.
      var values = sheet.getRange(‘A8:J17’).getValues();

      I understand I will have to replace ‘J17’ with something else.
      What’s the syntax for the new range? I understand programming logic, but have never written a Google script before.

      Thanks again,
      Alex

      Reply
      • Danielle Rohe says

        December 4, 2020 at 3:01 pm

        Hi Alex,
        The getRange() method has a few ways to pass parameters. The one using ‘A8:J17’ is the A1Notation. When you have a variable for lastRow and/or lastColumn, you will need to pass your lastRow and lastColumn variables as the numRows and numColumns parameters as described in this getRange() method on the Sheet class.

        All the best!
        -Danielle

        Reply
  25. Aldrin Morales says

    December 16, 2020 at 2:58 am

    Hello Danielle, Great tutorial :). I just have a few questions.
    1. How can I just update instead of adding a new slide after running the code?

    2. How also I can do autorun on slide load?

    Keep it up and thanks for this tutorial.

    Reply
    • Danielle Rohe says

      December 21, 2020 at 2:40 pm

      Hi Aldrin,

      Thanks for your feedback! Are you trying to update the presentation with new data? Once the placeholder copy is replaced, we can’t update the presentation. Instead, you might want to have the script contained in your Sheets file and have it create a new presentation deck when you run it (after you’ve updated the data in Sheets). Someone had asked about this type of solution in another comment, so I already have some code updates to get you started with that. This script updates how we declare the files and then has the script generate a new presentation (using the date/time stamp to differentiate the file name each time).

      All the best!
      -Danielle

      Reply
  26. Chris A says

    January 26, 2021 at 2:40 pm

    Thank you so much for this! However, when running the script with my own spreadsheet I am getting the error “TypeError: Cannot read property ‘getRange’ of null” on the line “var values = sheet.getRange(‘A2:H6’).getValues();”

    Reply
    • Danielle Rohe says

      January 27, 2021 at 1:11 pm

      Hi Chris,
      That error message indicates that the “sheet” variable that we are attempting to get the range from does not exist. Did you define your “sheet” variable: var sheet = ss.getSheetByName(‘{{the name of the sheet/tab inside your Sheets file}}’) If that is correct, make sure the way you are declaring your Sheets file is correct too. Did you replace the Sheets file URL in the dataSpreadSheetUrl variable? If so, you may have an issue with the file not being accessible, so check the sharing settings.

      All the best!
      -Danielle

      Reply
  27. Yossi says

    February 26, 2021 at 7:47 am

    Tried creating a copy and running. I got:
    “This app is blocked
    This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.

    Reply
    • Danielle Rohe says

      March 1, 2021 at 8:52 am

      Hello Yossi,

      Try checking the Advanced Protection settings for your account or seeing if you can force the OAuth screen the next time you load this by creating a new project, as described in the comments and answers to this Stackoverflow question.

      Best of luck!
      -Danielle

      Reply
  28. Robb says

    March 1, 2021 at 4:20 am

    Hi Danielle thanks for this!
    Could you tell me if these changes would launch the script from google sheets? (I’m very green to all things coding)

    function generateLandingPagesReport() {

    //delete this
    var dataSpreadsheetUrl = “https://docs.google.com/spreadsheets/d/1hCHYnddlqvE5dZqW9_b3GBRnUXkbvu6Xe4ymCUJuZMk/edit”;

    // change this
    var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
    // to this
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    //change this
    var deck = SlidesApp.getActivePresentation();
    // to this
    var presentationId = “1-PIIbYujo1Rm3CSq8Ao6YLALZEZ16-_9IE8UKB2D9Pg”;
    var deck = SlidesApp.openById(presentationId);

    // change this
    var sheet = ss.getSheetByName(‘content-landing-pages’);
    //to this
    SpreadsheetApp.getActiveSpreadsheet();

    var values = sheet.getRange(‘A8:J17’).getValues();
    var slides = deck.getSlides();
    var templateSlide = slides[1];
    var presLength = slides.length;

    values.forEach(function(page){
    if(page[0]){

    var landingPage = page[0];
    var sessions = page[1];
    var newSessions = page[2];
    var pagesPer = page[5];
    var goalRate = page[7];
    var goalValue = page[9];

    //Do some math for those percentages and rounding
    newSessions = newSessions * 100
    newSessions = Math.round(newSessions * 100) / 100
    goalRate = goalRate * 100
    goalRate = Math.round(goalRate * 100) / 100

    templateSlide.duplicate(); //duplicate the template page
    slides = deck.getSlides(); //update the slides array for indexes and length
    newSlide = slides[2]; // declare the new page to update

    var shapes = (newSlide.getShapes());
    shapes.forEach(function(shape){
    shape.getText().replaceAllText(‘{{landing page}}’,landingPage);
    shape.getText().replaceAllText(‘{{sessions}}’,sessions);
    shape.getText().replaceAllText(‘{{new sessions}}’,newSessions);
    shape.getText().replaceAllText(‘{{pages per session}}’,pagesPer);
    shape.getText().replaceAllText(‘{{goal rate}}’,goalRate);
    shape.getText().replaceAllText(‘{{goal value}}’,goalValue);
    });
    presLength = slides.length;
    newSlide.move(presLength);
    } // end our conditional statement
    }); //close our loop of values

    //Remove the template slide
    templateSlide.remove();

    }

    Is there anything i’m missing with this?

    Reply
    • Danielle Rohe says

      March 1, 2021 at 9:04 am

      Hi Robb,

      The “sheet” variable needs to use a getSheet…() method. Right now it is calling a SpreadSheet class instead, which you’ve already declared in the “ss” variable. What “tab” within your spreadsheet do you want to set the range on? You can call it by the name of the tab.

      Best of luck!
      -Danielle

      Reply
  29. Jonathan says

    March 2, 2021 at 12:44 am

    Hello Danielle,

    this is an amazing tutorial. Thank you for the time and effort you put in it!

    Now, after I successfully managed to get it running, I wondered if I could customize it to fit my needs a bit more. I already thought about it but do not really know where to start.

    In my table, I have a column “Category” with 4 different values (A, B, C, D). I would like the script to only create 4 slides (each for the categories A, B, C & D). Within the slide for e.g. category a, I would like to get only data of rows that ha. For the slide for category B, I would like to get only data of rows that were tagged with B, and so on.

    What would be your approach to tackle this?

    Thank you so much for your time in advance 🙂 It is really appreciated.

    Best,
    Jonathan

    Reply
    • Danielle Rohe says

      March 2, 2021 at 7:49 am

      Hi Jonathan,

      To get started, you’ll need to change a few of the variables.

        category

      • var dataSpreadsheetUrl will need to be updated to the link for your new Sheets file
      • var sheet will need to be updated to the name of the tab in your new file
      • var values will need to be updated to include the appropriate range. Say you have only your data in the spreadsheet, so you’d start with A1 and end with the letter for your last column and “4” for the final row (i.e., A1:D4)
      • Within the loop of “values,” you will need to map your new columns. Because these were webpages, I passed in “page” to the loop function, but you could change it to “category.” page[0] would be category[0] and then so on for any other columns you have. You can update those variable names to reflect your data, so var landingPage would be changed to var category, making that full variable declaration var category = category[0]

      Then you will need to update your placeholder text in your template Slides deck. Whatever you replace there will need to be updated in the shape.getText().replaceAllText() method. The first parameter to pass in the replaceAllText is the text to find and then the next will be the variable you are replacing it with, like “category” if you updated var landingPagecategory = pagecategory[0].

      Happy coding!
      -Danielle

      Reply
      • Jonathan says

        March 2, 2021 at 11:55 am

        Hi Danielle,

        thank you so much for your fast response. I really appreciate your effort 🙂

        I already managed to customize the script in terms of updating the variables as you described it. However, I wondered if it is possible to customize the script a bit more.

        For example, I have three rows of data: row 1, row 2 and row 3. There are several columns. One of the colums is called “Category”. The content of the cell of column “Category” is A for row 1 and B for row 2 and row 3. I do not want the script to create 3 slides (for row 1, 2, 3). Actually it should create only 2 slides, one for category A and one for category B. After that, on slide A only the values of row 1 should be injected (because it has “A” in column category). Consequently, on slide B the values of row 2 and 3 should be injected (because they are marked with “B” in column category). The script somehow should check through an “if” function I guess. Is something like that possible?

        I hope I did not think too complicated. Anyways, I am already excited for your thoughts 🙂

        Best regards from Germany,
        Jonathan

        Reply
        • Danielle Rohe says

          March 3, 2021 at 12:01 pm

          Hi Jonathan,

          You are correct that you will need to add a conditional statement to stop the third page from being created. Your condition would be inside of the for loop and would be something like, if (category[0] == "A") {//copy your template and replace all of the necessary text}. Since you are inside of a loop of each row, you’ll need to have the values of the cells in each row pushed to an array and then use that array to replace the text outside of the loop. Something like, if (category[0] == "B") {//map all your variables and push them to an array} //close the for loop and then map the variables in your new two-rows array to replace the text in another copy of your template slide. If that is a little too complicated, you could remove the for loop entirely and set different variables for your two ranges of values.

          All the best!
          -Danielle

          Reply
  30. Daishi says

    March 10, 2021 at 10:29 pm

    This is amazing! Thank you!

    Reply
  31. TomJ says

    March 12, 2021 at 2:56 am

    How would one insert data into a notes page associated with each slide?

    Reply
    • Danielle Rohe says

      March 12, 2021 at 7:36 am

      Hi Tom,
      Within the Slide class there is a getNotesPage() method to access the notes section for the slide. Once you have that NotesPage class, you can use the replaceAllText() method to switch out FPO text in the notes.

      All the best!
      -Danielle

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Get Our Weekly Newsletter

Technical marketing news made simple, from UpBuild's expert team straight to your inbox.

* indicates required

Browse previous newsletters. It's good stuff.

Recent Thoughts

10 Steps to Maximize the SEO Value of Your Press Section [Guide]

April 15, 2021 By Mike Arnesen

3 Ways to Build Authority Online Without Link Outreach

April 2, 2021 By Gaby Gebo

Meta Description Length Testing Tool

March 9, 2021 By James McNulty

How to Vet a Link Builder

March 4, 2021 By Alex Ramadan

How To Find & Fix Layout Shifts with Chrome DevTools

February 25, 2021 By Danielle Rohe

In the Spotlight

Engagements

You don't have time to worry about technical marketing and SEO. We do, and what's more, we love doing it. If … [keep reading] about Engagements

Stay Connected

  • Facebook
  • LinkedIn
  • Twitter

Footer

Who We Are

We're geeks. Unashamed and idealistic. We're here to do innovative work that gets results for clients we're proud to partner with.

Learn more about us

Featured Blog Posts

10 Steps to Maximize the SEO Value of Your Press Section [Guide]

3 Ways to Build Authority Online Without Link Outreach

Meta Description Testing Tool

Meta Description Length Testing Tool

Newsletter

Sign up for our updates.

  • Home
  • About
  • Offices & Location
  • Contact

© Copyright 2021 UpBuild, LLC · All Rights Reserved · Proudly powered by WordPress