description: "Learn about running a quick automation that turns rows in your Google Sheets and plugs it into a Google Slide template to easily share more attractive content."
Ignoring any design from my screenshots - all credit goes to my much more creative wife - the setup for the sheet is fairly
simple. You need various headings in Row A of the sheet which we will be using to reference data. In this tutorial, our
headings are `Date, Topic 1, Topic 2, Topic 3, Additional Notes`. Whether you start on Row 1 or after that doesn't matter too
much.
For the date column, we'll be formatting our date like this: "February 26, 2024". You'll see why in a little bit.
On top of the actual data in the Sheet, the Apps Script is going to live in this document and just push data to the Slide. To
access your App Scripts, click Extensions > Apps Scripts. A new tab will open with a blank IDE style interface and an empty
`myFunction`.
## Slides Setup
Don't worry too much about the design for Slides, you can change that later. But the important step is creating the empty
text boxes. After you create your text boxes (4 will be used in this tutorial), right click one of them and select "Format
Options". A panel on the left hand-side should slide out. Click the "Alt Text" drop down, and then "Advanced Options". That
little text box is the title for your text box; it is not used in the visual representation of the box, we will just be using
it as a reference point.
For ease of this tutorial, make the Title of the text box the same as the Header row from when we set up the Sheet, above.
Once you've added title to each of the text boxes, let's head into the code.
## onOpen Function
The first function you need for creating a UI change in the Google Sheet is an `onOpen` function that will setup the UI when
the Sheet is open.
```js {linenos=true}
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Create Presentation')
.addItem('Create Presentation', 'askDate' )
.addToUi();
}
```
In this function (which we'll later trigger in the script settings), when someone opens up the Sheet, it will create the UI
button in the top level menu. Here's what's happening:
*`SpreadsheetApp.getUi()` --> Instantiate class and get available methods for `getUi()`.
*`createMenu()` --> Creates a menu item called "Create Presentation".
*`addItem()` --> Adds an item to that menu that when pressed, calls the `askDate` function.
*`addtoUi()` --> Add it! Now people can see and click on it.
## askDate Function
This is the main function and a bit long, so I'll split it up into a few sections.
### Section 1
```js {linenos=true}
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var dates = sheet.getRange(2,2,lastRow,1).getDisplayValues();
// get today's epoch time for easier calculations
const epochDate = new Date().getTime()
```
This first section we're just getting the sheet ready for analysis. With the `dates` variable, we're just looking at the
second column. The reason we're using the `getDisplayValues()` method is because Google will automatically convert the dates
to include time zone, time, etc. We want to keep the date in the same format for a better user experience.
For that last line, we're getting the current date & time in epoch time so we can run a comparison further down the script.
For this use case, we don't need to include any dates in the past.
### Section 2
```javascript {linenos=true}
menuOptions = []
const re = new RegExp("^[A-Za-z]{3,15}\\s\\d{1,2},\\s\\d{2,4}")
for (let i = 0; i <dates.length;i++){
var date = dates[i][0];
var epochSheetDate = new Date(date).getTime();
if (date != "") {
var dateMatch = re.test(date)
if (dateMatch == true) {
if (epochDate <epochSheetDate){
menuOptions.push(date)
}
}
}
}
```
In this section we're creating an empty array and instantiating a RegExp to ensure we have an actual date in the cell. See
[Regex101](https://regex101.com) to learn more about Regex and test different regex syntax.
{{<boxtip>}}
Something that threw me off when first writing this regex function was the way the Google IDE manages escape character and
slashes. If you take the second line above and input it into [Regex101](https://regex101.com) you'll see the `\s` or `\d`
become dark gray, basically skipping over that token. However, for Google, you'll need an additional backslash to escape and
make the token become used by the function.
Here's the "correct" RegExp string for Regex101.com: `^[A-Za-z]{3,15}\s\d{1,2},\s\d{2,4}`
{{</box>}}
Next, we dive into a for loop, looping through the `dates` column of values (which we just called earlier). After attributing
each value to the `var date` variable, we also convert that same value into epoch time (by creating a new `Date().getTime()`)
so that we can compare it with today's date.
After the variables are setup we need to check that the date isn't empty; we don't need any rows where a date hasn't been assigned to it
yet. If we have a non-empty date value, let's compare it using the regex string. All we're doing here is asking "Is this date
in the format I'm expecting it?" If true, let's keep the value and continue using it. If not, just ignore it.
So we've now found a value that's in the date format we expect, let's now take that same value in epoch time (referenced by
the `epochSheetDate` variable) and compare it to today's epoch time date. If today's date is less than the value in the
sheet, that means the date in the sheet is in the future.
So now we have a date in the correct format and that is at some future date from today. Fantastic! Once we've gone through
those checks, we're ready to add the date to the array we created at the top of this section. Push on!
### Section 3
```javascript {linenos=true}
if (menuOptions.length > 0) {
let stringList = menuOptions.toString();
var formattedDates = stringList.replaceAll("2024,", "2024 \n ");
var response = ui.prompt('Which date would you like to create a presentation for? Please copy and paste the date exactly as you see it in the options below. Options: \n \n'+formattedDates);
if (response.getSelectedButton() == ui.Button.OK) {
var dateChosen = response.getResponseText();
if (dateChosen != "") {
ui.alert("Woohoo! Let's make a presentation!")
for(var y = 0; y<dates.length;y++){
if (dates[y][0] == dateChosen) {
// Logger.log("Row:"+(y+2));
var presRow = y+2
var presData = sheet.getRange(presRow,3,1,4).getValues();
This function may be the shortest to explain. All we are doing is taking those variables from the previous function (the
variables that contain each topic's text value) and doing the following:
1. Opening the Presentation (line 2)
2. Getting the first slide (line 3)
3. Grabbing all the [page elements](https://developers.google.com/apps-script/reference/slides/slides/page-element) (line 4)
4. Looping through the page elements & grabbing all the titles that we set during [setup]({{<relref "#slides-setup">}}) (line
5 & 6)
5. Then we compare the title of the Slide text box to the expected string (Topic 1-3), and if we get a match, retrieve the text
of the box as a variable, clear the text, and input our new text with the variables we created in the previous function.
And just like that, you should have all your boxes filled in with the data you input to the Google Sheet!
{{<boxtip>}}
**Bonus exercises:**
* Instead of overwriting this presentation each time, how can you add a new slide with the same text boxes? How would you
find that slide since we're right now grabbing the first slide?
* How can you change the name of this Slide Presentation to the date from the Sheet?
* Could you automatically export this Slide as a PDF and save it to a user's Drive?
{{</box>}}
{{<details"##FullScript:">}}
```js {linenos=true}
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Create Presentation')
.addItem('Create Presentation', 'askDate' )
.addToUi();
}
function askDate() {
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var dates = sheet.getRange(2,2,lastRow,1).getDisplayValues();
// get today's epoch time for easier calculations
const epochDate = new Date().getTime()
menuOptions = []
const re = new RegExp("^[A-Za-z]{3,15}\\s\\d{1,2},\\s\\d{2,4}")
for (let i = 0; i <dates.length;i++){
var date = dates[i][0];
var epochSheetDate = new Date(date).getTime();
if (date != "") {
var dateMatch = re.test(date)
if (dateMatch == true) {
if (epochDate <epochSheetDate){
menuOptions.push(date)
}
}
}
}
if (menuOptions.length > 0) {
let stringList = menuOptions.toString();
var stringlist = stringList.replaceAll("2024,", "2024 \n ");
var response = ui.prompt('Which date would you like to create a presentation for? Please copy and paste the date exactly as you see it in the options below. Options: \n \n'+stringlist);
if (response.getSelectedButton() == ui.Button.OK) {
var dateChosen = response.getResponseText();
if (dateChosen != "") {
ui.alert("Woohoo! Let's make a presentation!")
for(var y = 0; y<dates.length;y++){
if (dates[y][0] == dateChosen) {
// Logger.log("Row:"+(y+2));
var presRow = y+2
var presData = sheet.getRange(presRow,3,1,4).getValues();
