The Google Developers North America features talks from events, workshops and trainings, as well as updates from our developer communities including Google Developers Groups (GDG), Google Developer Experts (GDE), Women Techmakers (WTM), and Google Developer Student Clubs (GDSC).
This is a community based channel. Join us, support us, speak, and train with us!
Learn more about these developer community programs: developers.google.com/community
If your headers have long names with none-alphabetical characters such as "Name (Last, First)" then the "body.replaceText(`{$header1{{`,row[i]);" will fail because replaceText is using regular expressions to search. Solution is to convert the search string using a function that adds escape characters such as the following: function escapeRegExp(text) { return text.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, '\\$&'); } For example in my version I changed the "body.replaceText(`{$header1{{`,row[i]);" to be the following: const searchHeaderOriginal = '{'+header1+'}'; const searchHeader = escapeRegExp(searchHeaderOriginal); body.replaceText(searchHeader,row[i]); There may be an easier way to do this but this is what I found after researching.
This is a Life saver! One question, if I remove the email function will the if(row[0] == ‘’){ function still work, to check if the date column is empty? As i need the ability do select certain rows to generate the pdf, I have 120 rows, I don’t need all of them to be generated…
I've been working on this for a few months now. I stumbled upon your videos a few months back, Had no clue what gibberish you were speaking! Now it all makes sense. Chat gpt gets all the credit. I've been working on an army of scripts that serve as a client onboarding, client data management and client scheduler, thats 100% automated and operated with a spreadsheet dashboard. Its absolutely nuts and I've been able to increase my client workload noticeably simply because I have more mental space and can keep track of more clients simply and easily.
This was awesome! Thank you! One question: I have URLs to images on my google sheet as well. Is there a way to embed the image into the Google Doc template with this method?
I have a sheet with a table already on it that is updated from info from other sheets all inside one sheet. Hopefully you follow. I want google sheets to automatically just make that one sheet a pdf and save it to my google drive. So probably half of what this script is doing. How can I accomplish that?
AspectRatio: Define using width-to-height ratios (e.g., 16:9). Adjusts either width or height to maintain the specified aspect ratio based on available space. VisualDensity: Modifies the density of UI elements, making touch targets larger and more accessible. Can be injected programmatically to propagate throughout the application’s theme.
Thanks so much. This tutorial really helped me generate invoices from raw data for my wife. Super clear. I put the playback speed to 0.75 and typed out most of your material. I did not need the email functionality and added "file.setTrashed(true);" to remove the doc files, earlier in the script. Here is my code to help anyone who wants to cut, paste, and play around from here. Thanks again! // This code was taken from this RU-vid tutorial: // Unlock the Secrets of PDF Generation with Google Apps Script // ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0sxyB4kUKdY.html const SHEETID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; const DOCID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; const FOLDERID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; function onOpen(){ SpreadsheetApp.getUi() .createMenu('Generate Invoices Workflow') .addItem('Manually Add Month Folder/ Import New Data','xxxx') .addItem('Generate PDF Invoices','sender') .addToUi(); } function sender () { const sheet = SpreadsheetApp.openById(SHEETID).getSheetByName('InvoiceData'); const InvoiceData = sheet.getDataRange().getValues(); const rows = InvoiceData.slice(1); // Logger.log(rows); const temp = DriveApp.getFileById(DOCID); const folder = DriveApp.getFolderById(FOLDERID); //Loop through each spreadsheet row, and for each row, create a new temp document in your drive folder rows.forEach((row,index)=>{ const file = temp.makeCopy(folder); const doc = DocumentApp.openById(file.getId()); const body = doc.getBody(); //Loop through the spreadsheets heading values and populate those values into the temp document InvoiceData[0].forEach((heading,i)=>{ const header1 = heading.toUpperCase(); body.replaceText(`{${header1}}`,row[i]); }) //Set a name for each document using x, y from the data doc.setName('INV-'+row[0]+' '+row[2]+'.doc'); const blob = doc.getAs(MimeType.PDF); doc.saveAndClose(); const pdf = folder.createFile(blob).setName('INV-'+row[0]+' '+row[2]+'.pdf'); //The followinng code removes the temp doc file, leaving just the PDF files. file.setTrashed(true); //TESTING // console.log(header1); //TESTING }) }
@jimrockford1283 You could try creating a test Google Slide file, saving this as a Google Doc file, adding the variables to this as per this tutorial, and then recreating the Google Slide file instead of the PDF in the original script?
Life saver, thanks for the pasted code. One question, how do select certain rows to generate the pdf, I have 120 rows, I don’t need all of them to be generated…
Beautifully done! I'm working with some dates and times. When I get the dates or times, they come in the format like Sat Apr 29 2023 02:00:00 GMT-0400 (Eastern Daylight Time). How can I do in order to get (and show in the pdf generated) just 4/29/23 format for dates and 11:30 PM format for time? tks.
These are not public speakers. These are interns taking part in a program that the bigwigs hand out for pennies on the dollar to make it seem like they give a shit about women.
file:///var/mobile/Library/SMS/Attachments/54/04/E46A5908-6DE8-4D8E-82E8-7C609B31377C/image000000.jpg Please work with this organazion file:///var/mobile/Library/SMS/Attachments/54/04/E46A5908-6DE8-4D8E-82E8-7C609B31377C/image000000.jpg
Great Video! Quick Question, how do you take the value of the spreadsheet classified as dollars/currency/accounting and apply it to the word document to transfer over as same $ format? This video has been beneficial and got everything working except the formatting of the numbers. Example: In excel sheet $240,000.00 but generated in word document --> 240000 and transferred into the PDF same as the word. Do you have any suggestions on that? Thank you again for your time!
@@LaurenceSvekisCourses Yes, I have tested all types in google sheets to see if it's impacted and it is not. The numbers do not generate over as same format as currency or finance. I have also tried to set the word template as well with the incorrect results. Any other suggestions? I have looked online and it seems no one has tried using $ numbers to transfer only whole numbers.
Thank you very much. It it a realy great video and code. Is it posible, to replace an old pdf file with a new one in app script. I have link to pdf file in Google Drive, posted on internet, I just want to create a new pdf file and update the old one to remaine the same link.
I’m just starting out with coding, so I can’t judge the quality of your advice. However, as far as the video production, your background music is so loud compared to your voice that I have real difficulty understanding what you say. Would you consider re-releasing this video with a quieter background, or even none at all?
Excelent tutorial - thank you. All works as per the video except I dont get the option 'Enter verification code' back to the console and therefore cant proceed. Have you seen this before?
Considering attending a GDG group meeting. This is right up my alley. Thank you it helped get me updated with what GDG and this helped solidify the choice. Thank you
Awful music over voice. Why have it. The speaker has a strong accent which is difficult to understand and then this loud, inappropriate music over the top. Whoever produced this video, whoever signed it off hasn't a clue, they are clearly not up to the job and should be sacked. This video is unwatchable. Totally unwatchable. I need this information but this music is unbearable, to whoever thought music over voice was a good idea, you shouldn't be in the job of editing and producing video, you are hopeless. I'll have to find this information somewhere else. You shouldn't have your job, you're no good at it.