How I Automated Google Sheets with Apps Script - Automate Your Business

i automated google sheets so now a task that used to take me hours and hours every single week takes two minutes let me walk you through how i did it did you know that you could automate google sheets and other google drive products well i did and so i decided to get rid of some of the manual copy and pasting that i do every single week when it comes to my newsletter and automate that in this video i'm going to walk you through the whole process of how i did it and also provide some links in case if you're looking to do the same and the point of this video is to show how a project like this can be done and give you the next steps in case if you're interested in automating your google sheets or google drive products and also show how you can approach a project where you don't really understand what's going on because while i did work on a project that automated google sheets in the past when i was working at google i didn't do the coding i was working with an amazing colleague of mine who was a developer from deepmind and so she did all the coding and i i never got into the nitty-gritty of it so i had no idea what i'm getting myself into i knew it was possible because i've seen her do it and we worked on this project that ended up you know functioning but i never did it myself so i decided that it was time to learn it and it took a lot of trial and error and a lot of googling and a lot of stack overflow to give you some context i wanted to automate the compilation process for my women in tech weekly newsletter which is a newsletter that celebrates women in technology and shares resources created by women in tech and that can be useful for women in tech it is it comes out every week every tuesday and if you want to find out more i will leave a link to it in the description you can also subscribe to it and submit your news through it and the way it works is it's a google form it's a google submission form because it's community based it's community sourced and all of these submissions go into a spreadsheet this is a test spreadsheet that looks exactly the same all i've done is changed the names of the women to preserve their privacy but this is what it looks like the inputs are added into different fields into different columns and what i do is normally i manually copy and paste this into a different document for every week i create a master document as i call it where i format all of the text so for example the name the celebration a bit more about the celebration etc etc and i combine it i format it and then i use the text from the master document to create the newsletter in the blog posts in the uh in the email newsletter and instagram as well what i wanted to do is for the spreadsheet when i run the program to create a new master document that would be named in the correct convention the way i name it and placed in the correct folder and then i wanted the script to go through every single line of the new submissions of this week's submissions and basically see if they're a celebration or if they're a resource or if they're both and take the relevant fields the the text from the fields that are the ones that have the text uh for that specific um type of input type of submission so for example for for celebrations it has a name field and then it has a celebration field and then it has a body field and then it has a link field so all of those links all of those fields should be combined and taken from the spreadsheet to the master document and i want them to already format it automatically i want the script to format those fields automatically as they go in so basically this reduces the copy pasting and the formatting and all that stuff so the way i did it is first i sat down and i wrote out all of the steps up here um all the steps that i want this automation this program to have and they weren't necessarily going to be like every step is going to be a function i didn't really know how this was going to work out because i had to understand more what i'm working with i know i'm working with javascript but i'm also working with um google apps script which um behaves slightly differently than just normal javascript so i decided to list out all the steps and then modify them if needed but basically this way i knew what i was looking for in terms of functionality and then i did the tutorials google developers website has really great tutorials for that which i will leave a link to in the description so if you're interested to get started and get learning there they are so i am a learner that is visual and learning by doing kind of thing um so for me it was essential to start my exploration phase through these tutorials because they they take you through the process through the very basics and help you understand how things work and um you basically end up copying and pasting a bunch of text code and see what it does and throughout that process i was actually taking notes in my head about the functionalities that i want my program to have because uh there was for example one of the features that i needed is i needed for the code to determine which are the new values um that it needs to go through right and so one of the things that it that the tutorials took me through is selecting a bunch of rows which is pretty intuitive i guess but that's not something that i thought of so i was like okay i'm going to use this and then um there was a for loop somewhere and i was like okay i'm gonna come back to that notation and i actually ended up using those notations and just editing them for myself once i've done three tutorials really in details um i already had a good understanding of what i'm doing and i was just itching to get my hands dirty so i kind of skimmed over tutorials four and five but nevertheless they're amazing and i will probably go back because there is um one of the tutorials that are more advanced you can create a chat bot um with google drive which i'm not quite sure what it's going to help you do but in any case i will go back to that one because i'm super interested and want to build one but yeah once i understood the fundamentals a little bit i decided to get my hands dirty and start working because that motivates me a lot you know like actually trial and error and all that stuff so what i did is i started with creating a menu and that was the easiest thing to do it's just a very simple code and that was the first big win and it kind of motivated me to go forward but it created a custom menu with a custom button essentially that starts running a function which looks pretty cool but it's super easy so the first proper function that i was going to build was creating a new well a master document a new google document in the right folder and it had to be named in a specific convention so i had to google basically how to create a new google doc through the scripts uh through apps scripts and luckily apps scripts uh documentation is super detailed and great so i used that and i also googled a bunch of stuff so ended up on stack overflow probably 70 of the time um and i figured out how to create a new document then with the naming convention i created additional functions uh one of them was finding the date of the next tuesday because every uh the newsletters come out every tuesday and i wanted to figure out what was the date of the next tuesday and then the issue number so the issue number i decided to calculate that by going back to the very first issue and you know looking at today's date subtracting the number of uh days and seeing how many um weeks have gone by add one that's the next issue so yeah the naming convention goes next tuesday's date women in tech weekly uh issue number and then the issue number that took me a while to figure out but when i did i felt like a ninja i felt awesome and then i had to figure out how to because i couldn't i didn't know how i could create a document in a folder straight away but i could find how i could move it to that folder and along the way this is what i've learned when i was working with my colleague at google who is who was fantastic every time she would do something she would she would ask the program to log the result the output so i remember when i was for example trying to allocate the folder by id um i i made the program log the name of the folder that comes up to make sure that it's the right folder and i used that logger function so much i used it a ton because it helped me stay on track to see if the outputs that i'm getting are still correct um so yeah once i figured that out it felt awesome i may have done a little victory dance and showed it to my husband and just felt amazing about myself and took a big break my next big problem was to figure out how to take the text from the document and input it into the um the master doc and format it in the way i want it to be formatted and there are two types of input like i said there's a celebration and then there's a resource so what i did was back in my little document i basically mapped it out and i write wrote down um the cells oh sorry the columns that need to be checked for text to verify whether it's a resource or a celebration and also what um text in which uh columns needs to be inputted and like what's the formatting and all that stuff once i had that it was easier to visualize what i had to do and what i did was because i have already and i forgot to mention this when creating a master document i have managed to input a heading an h1 heading into the document that basically mimic the name of the master document so it was easier and i managed to format it by aligning the text to the center so i was like i was very proud of myself and it felt very good so i felt a little better and i realized that it's not going to be as hard to format it as i maybe thought previously so that gave me the confidence and what i did is i used the for loop that i copy and pasted from one of the tutorials that i did before and modified to my my case and the for loop uh was act was functioning for the selected text again that was one of the things that i also borrowed from the tutorials that idea of selecting the rows um that need to be dealt with and then going one by one through them verifying whether it's a row with celebrations and then if it is then take text from this row save it in this variable and then inject it into the document here's the format blah blah then append the next one it was easier than i thought it would be to be honest uh but it did take me a while i'm not going to lie because um it was the first time i was doing it so the only major issue that i have encountered throughout this whole process i mean there were many little ones that i dealt with um the only major one was that google apps script doesn't seem to support global variables meaning that the value of a variable cannot be updated by a function and if you've defined it outside of a function it's just going to be the same across if if you try to use it in a different function the variable that i did need to be updated was the id of the master document that i was creating because i can define it outside of a function and then when i create a master document that function um obviously creates a new master document saves that value and then i need to be able to access that value with other functions for example the function that takes text and finds that document and inputs it in there so that was the only issue but um stack overflow pointed me in the right direction of um properties i don't remember the exact name for that for for what that was but i will put it here and it basically functions the same way as global variables is just a bit more complicated but yeah once i figured that out it was pretty straightforward had to play with the templates and also some shortcuts didn't quite work out as i thought they would but whatever i ended up doing it and once i have figured out the celebrations part the resources part was just a walk in the park um it was super simple and all i had to do was update the fields for the stuff that i have already outlined and the formatting so it was it was very simple and very straightforward i still need to write one more function and that is to mark the selected text as done and the way i do it is just changing the color so it's super a super simple function and later down the line what i want to do with this automation is basically to line up all the functions one after the other so that you don't have to so the user whoever is doing it doesn't have to go and manually select even though it's so simple create a master document then celebrations then blah blah and maybe i will also automate um automate it so that you don't have to actually select the rows because again that will eliminate a margin for error in case if all of the function all of the rows have not been selected and yeah there's definitely room for improvement but it was so much fun and felt so good and it's going to save me so much time so i highly recommend taking on projects like this even like i don't code professionally right i'm not a professional programmer but this is so fun and it's a great way to train your brain and also create something productive that you will use on a weekly basis or a daily basis and i i'm thinking about all the use cases that i can implement automation with um using google docs so it's it's so fun if you're interested in learning more about how to automate google apps uh google documents google sheets google drive essentially i will leave a link to the tutorials on apps script in the description and of course if you want to subscribe to the women in tech weekly newsletter make sure that you do so using a link in the description and if you have a submission that you want to add whether it's a celebration or a resource again the link is in the description please be my guest go ahead it is meant to celebrate women and their achievements and consume their resources which are amazing let me know what use cases you can think of for automating google documents google sheets anything on google drive i'm super curious to find out and hey maybe i will use your idea as well because this is fun and saves you time and just yeah yeah i think it's pretty self-explanatory at this point share this video with a friend who is struggling with the endless copying and pasting on google drive and who is willing to learn something new again it's a fun project to do together in groups and of course subscribe to my youtube channel and like my videos if you have liked them you can also find me on other social media as coding blonde have a wonderful time of the day you're currently experiencing [Music] bye

Channel: Stereotype Breakers - previously Coding Blonde

Views: 23,335

Keywords: automation, google sheets, google drive, google sheets automation, google docs automation, google drive automation, automate your business, google apps script, apps script, google apps, google sheet automation

Id: 4_fLSrix34k

Length: 17min 40sec (1060 seconds)

Published: Wed Oct 28 2020