Google Sheets Triggers Update of Slides - Google Apps Script Tutorial

[@sonic763]

Love the videos. Gives me ideas. Are there any websites or content I can read to learn more about app script from a beginners perspective. I’m just learning how to code so trying to get ramped up

[@dewadurja]

Ok... I started to love this channel 😂😂😘❤️

[@saperis]

Well that's something we like hearing! Could we ask you for a favor? Could you please let others know about this channel? 😀

[@ignaciodelucas6108]

THIS IS AMAZING! Thanks for this great video! I know ZERO, ZIP, NULL, NADA about programing and I managed to make it work with my own values! Thanks a ton for this! Really a time saver! Just a quick q. Is it possible to make the script run on several rows at a time to make several slides in one go? or does it have to be one by one? thanks a ton in advance!

[@saperis]

Thanks for watching! Apps Script runs from top to bottom. So it processes row by row and there is no way of simultaneously doing things.

[@etomeee]

Hi Saperis, how can i capture the google sheet data when new response come in, and google slide auto update the data from google sheet data new response from the new entry only and replace the previous one. i don't want have new slide add in the google slide. thanks for your help

[@saperis]

With the script I used that's not possible. We are working with placeholders in Google Slides to be able to write the data from Google Sheets into those text boxes. Once you add text to the placeholder there is no way of you updating those boxes. So, in my opinion, this script isn't a good match for what you are trying to achieve.

[@minozminoz8829]

It possible an example update auto slide thank you

[@Leover378]

Hello! Do you think there is a way to make an organization chart in Google Slides, each time a form response is submitted and the Google Forms includes a photo of the person. Thus the organization chart would always be updated with the photograph and personal data.

[@saperis]

In theory doable, in practice quite tricky. How would you know where to place the new person in the org chart? Do you have to replace someone or add them? Have fun working on this project. 😀

[@easysolutionsforcomputer.4592]

Maam do you have any type of laptop or computer installed with windows ? if yes then with which windows ? I am asking this because if you have an windows laptop or computer then you would be having knowledge about it so i can ask you questions related to google workspace in windows.

[@saperis]

I sometimes use a Windows computer so I have somewhat knowledge. But Google Workspace should be the pretty much the same on Windows or Mac since it runs in the browser.

[@easysolutionsforcomputer.4592]

Maam which version of windows do you have windows 7,8.1,10 or windows 11.

[@GenNextAnalyst]

Wow! I didn't know it is possible. Thanks a lot!

[@saperis]

You're welcome! 👍

[@khaledalenezi6090]

thank u that's awesome any idea how to insert a qr code on slide from google sheet as image

[@saperis]

There's a getImage() method you can use on a sheet. Maybe like that you could copy it to Slides.

[@josuecontreras7931]

Hi!!! Could you help me understand something? I have created an script similar to yours, but when I want to run it an error appears that I don't understand: "Exception: Esta presentación está cerrada; los contenidos no pueden actualizarse." In english it would be something like: "This presentation is close; the contents can't be modified." I can't understand why this happen. Any tought?

[@josuecontreras7931]

This is the code that I am using function onOpen(){ const ui = SpreadsheetApp.getUi(); const menu = ui.createMenu('Automatizaciones de App Script'); menu.addItem('Crear Kick Off','CrearNuevosGoogleSlides'); menu.addToUi(); } function CrearNuevosGoogleSlides() { const googleSlideTemplate = DriveApp.getFileById('XXXYYYY'); const destinationFolder = DriveApp.getFolderById('XXXYYY'); const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Datos_Empresas'); const rows = sheet.getDataRange().getValues(); rows.forEach(function (row, index){ if(index === 0) return; if(row[1]) return; const copy = googleSlideTemplate.makeCopy(`${row[0]}- Kick Off`,destinationFolder) const slides = SlidesApp.openById(copy.getId()) const presentation = slides.getSlides(); presentation.forEach(function(slide){ var shapes = slide.getShapes(); shapes.forEach(function(shape){ var textRange = shape.getText(); textRange.replaceAllText('{{Generación de Reportes}}', row[2]); textRange.replaceAllText('{{% Generación de Reportes}}', row[3]); textRange.replaceAllText('{{Flujos Repetitivos}}', row[4]); textRange.replaceAllText('{{% Flujos Repetitivos}}', row[5]); textRange.replaceAllText('{{Recopilar Información}}', row[6]); textRange.replaceAllText('{{% Recopilar Información}}', row[7]); textRange.replaceAllText('{{Compartir Información}}', row[8]); textRange.replaceAllText('{{% Compartir Información}}', row[9]); textRange.replaceAllText('{{Cons| Fe}}', row[10]); textRange.replaceAllText('{{Cons | Hora}}', row[11]); textRange.replaceAllText('{{Q&A | Fecha}}', row[12]); textRange.replaceAllText('{{Q&A | Hora}}', row[13]); textRange.replaceAllText('{{Cierre | Fecha}}', row[14]); textRange.replaceAllText('{{Cierre | Hora}}', row[15]) }) slides.saveAndClose(); const url = slides.getUrl(); sheet.getRange(index+1,2).setValue(url) }) } )}

[@saperis]

No, haven't heard that error message yet. Try to run the script in debugging mode: kzbin.info/www/bejne/gmW5YmiQra6deLM

[@antoinelieke7272]

Thanks you very much for this very good demonstration. I tried to apply this script for my need but i cannot get the good result. Maybe Can you help me please. Instead of having a month in the first column i have the name of a project in my Gsheet because i would like to follow different projects which are set row by row. When i run the script, a new slide is created but without my datas. And i have an error : TypeError : cannot read property '0' of undefine. And i do not have any checkboxes on my Sheet. Thanks you for your help. 🙏🏽

[@saperis]

The only thing I can give you a hint about is the error message. "error : TypeError : cannot read property '0' of undefined" means that you are trying to access the content of a variable that is empty. Check this video to find out how you can debug: kzbin.info/www/bejne/gmW5YmiQra6deLM

[@robinwilliams2633]

I'm hoping you might be able to help me figure out where I went wrong. I'm getting a TypeError: Cannot read properties of null (reading 'duplicate') For full disclosure I'm using this for a VERY different purpose in that I'm trying to take 250 comments entered into a Google Form and turn them into 250 unique slides to avoid copy and pasting for hours but the fundamentals are the same. Take X from Sheet Row 1, Column 2 and put into new slide.

[@saperis]

This error message means that the variable "duplicate" is empty, hence null. See how to find bugs in your code: kzbin.info/www/bejne/gmW5YmiQra6deLM

[@GregQualls]

I might have missed this. If you change any of the data in rows that have already been generated, will it go back and change the data in those slides?

[@saperis]

Nope, that's not the way this script works.

[@minozminoz8829]

Please, it possible change data row and update slide thank you ! Good job 👍

[@pisitch8346]

Thanks very much

[@saperis]

You are welcome

[@jlotica]

Really cool

[@saperis]

Thank you 😀

[@muhammedaadhil5645]

Thank you very much🤗

[@saperis]

You're welcome 😊

[@lauramcooley]

Thank you so much for this tutorial (and others!!) I am struggling to make it work because I keep getting errors like replaceAllText is not a function. I have also experienced other error messages related to methods like batchUpdate that seem like they should just work. After much digging, I am wondering if I should be enabling the Google Slides API in the Google Cloud APIs & Services. This seems to be a big can of worms, one that I am struggling to find usable information about. Before I continue down this path I am hoping you can let me know if I need to do something with credentials and OAuth in order to run scripts to update google slides. (I am able to run scipts for google sheets and google docs, I am also able to run a script to update a linked chart in a google slide but I just cannot get replaceAllText to work. I am just so confused as to why I can update a chart in slides but cannot update text).

[@saperis]

It depends... In all of the tutorials I publish on this channel, we access the different apps, (e.g. Google Sheets, Google Docs, Google Slides, etc.) with Google Apps Script. So you do NOT have to enable any APIs in Google Cloud. If you are accessing the Google APIs, (e.g. the Google Slides API) from your own application (so not with Google Apps Script) in maybe Python, then you may need to enable that API in your Google Cloud project.

[@lauramcooley]

@@saperis Thank you. I finally made it through (I am not sure why but I did). The google developer tutorial on slides is using python instead of showing what happens simply within the Google Apps Script. If it weren't for your videos I would be totally and completely lost. As it is you have helped me make significant progress. Thank you very, very much!

[@CaptPookem]

thanks

[@saperis]

You're welcome! 😀

[@cmohanc]

This is very good. I want to Update google slide without clicking a button . Animate the Slide whenever an update is made on the sheet. Is it possible ?

[@saperis]

Unlike Google Sheets, Google Slides has no trigger that works for changes made to a document. You could have a time-drive trigger running every hour or so. But I think the best would be to click a button whenever you need the update. More about trigger: developers.google.com/apps-script/guides/triggers

[@saperis]

Oh, and if you need to check for changes to the Google Sheets, then you could use the onEdit trigger: developers.google.com/apps-script/guides/triggers#onedite

[@Polwan_anak]

is there a way to have the objectId of my duplicated result use ? because the objectId I got is not recognized

[@saperis]

I'm sorry but I don't understand your question. What "objectId" are you referring to?

[@Polwan_anak]

@@saperis oh sorry my question is not clear me another question how to set text color when replace text ?

[@saperis]

@@Polwan_anak Check the documentation to see how to format the text in Google Slides: developers.google.com/apps-script/reference/slides/text-style

[@viniciusmandrade]

Its possible to create a script where it generates a new google slide presentantion, based on a previosly designed template, thats gets data from the last created row in a spreadsheet, and send it to a e-mail?

[@saperis]

Sure, that you could do with Google Apps Script. If you want to learn about how to auto-generate new files check this video: kzbin.info/www/bejne/fYGsdnV_nNWDaac

[@omenworks]

If I work with larget sheet how do I "cut" the data to 2-3 separate Google Slides?

[@saperis]

You would have to adapt the script to write it to the slides you want to.

[@chiru4969]

how to create a form element, where user can enter a list of items???

[@saperis]

Already answered this comment on the other video.