How to Automate Invoice Creation with Google Sheets, Docs and Forms
ฝัง
- เผยแพร่เมื่อ 24 พ.ค. 2024
- Automate invoice (and other document) creation using Google Forms, Sheets and Docs.We’ll use Apps Script to create a workflow that automatically populates a document template using responses from a Google Form.
Template: docs.google.com/document/d/1Z...
Google Forms Phone Regular Expression Pattern: \+?\(?\d{2,4}\)?[\d\s-]{3,}
Google Forms Email Regular Expression Pattern: [a-zA-Z0-9_\.\+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-\.]+
Apps Script Code: gist.github.com/automagictv/4... - แนวปฏิบัติและการใช้ชีวิต
![เกิดใหม่ทั้งทีก็เป็นสไลม์ไปซะแล้ว ซีซั่น 3 - ตอนที่ 56 [ซับไทย]](http://i.ytimg.com/vi/cT05Arhf_q8/mqdefault.jpg)
I'm still in awe behind how this really did come out perfectly for me....thank you for your time spent making this video.
please can you help m to know how to past the script code ?
i pasted but nothing come out
Very helpful and appears pretty straightforward. Will be adopting this tonight. Thank you for sharing.
Thank you for this, I have managed to adjust it to be both my invoice and now a quote form too, it took me a little figuring out but both work perfectly now. Thanks for the really helpful video and additional info in the description. (Australia based)
Thank you for sharing your knowledge! more videos and tutorials about automation like this
The best tutorial on automatic template creation.
It's really magic! It makes it seem so easy to follow. Thanks
Thanks man. You're a star. Awesome video well presented. Kudos.
Great tutorial, very easy to follow, nice and simple for a beginner
Brilliant. This all works for me. Well done, Thanks
Thanks a lot! Looking forward to your new contents / videos.
This is very helpful. Hoping to see more. Thank you.
what is the script code
Thank you so much .. This is exactly what I was looking for.
Absolutely fantastic video!!!!! thanks very much for sharing. I subscribed after the first 2min :). Ps at 06:10 I couldn't find Script editor "App script" under the Tools Tab drop down list in my Google sheets, might be a google update...I found it under the Extensions Tab so I was able to follow along. thanks once again!
Hey there, its awesome video i made it for my own little company... Thank you very much for the learning video...
Fantastic work brother!!!
Great content-thanks! Would you know what the script edit would look to factor in tax? I'm thinking of just making the calculations manually then adding it as a line-item as a workaround. in all, this was tremendously helpful. 🙂
Brilliant work.. amazed me.. You explained it easily.. The best video 2022
what is the script code
great stuff beautifully explained! thanks
Absolutely amazing tut
wow..nice work.. thank you for sharing..
Wow! This will transform our way if doing business! Thank you so much! I made some small adjustments and made it my quote. I had one question. Do you know a way to add HST tax of 13% tax? Or I could easily adjust the "total Balance" to say "subtotal plus tax"
Big thanks bro!
its really helpful
Lol. It's true when you said don't get scared about coding. I was really procrastinating to learn this because of the Apps Script part.
Thank you this was super insightful!
Could you also make a version where you are the one that fills out the invoice and sends it off to clients already filled out for them? And in the way that when you input the prices they still calculate themselves at the end for you too? Thank you!
OK this is sort of cool... noticed the Blank rows still had a 0,00$ value so I guess it needs some tweaking or simply deleting the lines... I was hoping for a Sheets solution to the problem where you fill in the data in a sheet and use that to propagate the invoice and then print as .pdf and send the invoice. But there's a lot of this which is good. I'll SUBSCRIBE and head off to find similar content on your channel
Very helpful Thank you so much
Super clear!!
lovely lesson
Thanks a lot..it was very helpful...
Great video! thanks @automagic. Question, is there a way to automatically send the generated invoice to the email in the form?
hello, i just wanna ask if the script code provided on your description is I can used to all kind of automation that I can do? Or this script code is for only automation like what shown in the video? It's my first time watch a tutorial like this, hoping for your answer
Thank you, Worked well for me, I had to change the currency symbol from dollar to Indian rupee by changing the constant, it worked well for me.
Can you explain in detail how to do currency change?
@@alanaheaney2000 I just replaced the currency symbol in the apps script, under design edits you can see Const currency as $ in the third line, just replace it with your currency symbol. that's it, hope this helps you.
in the app script part the third line down just change the $ sign to your currency .. at least that workled for me, Then de bug that line. @@alanaheaney2000
you saved my life man
thank you helped me !!
Thank you very much for your video, very explanatory, is there a way that the final result can be sent to print instead of being stored, that is to say that after the submit you open the print option? Thanks
This is a great solution. Thanks
You're welcome!
Hi, compliment for the tutoria, clear and useful. I would like to know if it’s possible to save the new file even in PDF
Thank you, very informative. Is there any way to create a cart from from Google Forms? I have 3 options on the form Option 1 - $5, Option 2 - $5, Option 3 - $7... It can only be selected once but they must select one or the options. and I will like the value to show in the summary or confirmation email.
This video was really useful bro!! We need more videos like this one. Automation on Google apps..
thanks so much!
Thanks so much for this!
I have managed to tweak it and is helping me massively. I do have two tiny questions however if you would be so kind as to help with them...
Is there any way at all that you can change the "description1" and "price1" fields to something a bit more customisable for the Google Form?
Also, i have noticed something very strange in that the Invoice Date seems to always appear a day earlier than the date i have used. EG if i input 01/02/2022 - it will appear as 31/01/2022 on the invoice. Very odd, but i hope you can help!
You should be able to change the variable names to anything you want as long as you make the change on the doc template, form and in the code. As for the date.. that is strange! I wonder if it has something to do with the time zone settings vs. when the script is executing? Or maybe some date formatting? I can't actually reproduce the issue on my end so I'm not sure what's causing it.
.
This is great. But if I need to have two prices say materials and labour then I need to add them together for a sub total on multiple lines would it work with the same code?
Hi there,
First off thank you for sharing your knowledge and splendid explanation. I do have a request (googling at the same time): do you have a suggestion on how to not print the template placeholders when they are not used?
i am trying something like this:
var match_text = `{{${key}}}`;
var value = response_data[key];
// replace template holder with white space if values are not set
if(typeof match_text === "undefined") {
var value = ' -- ';// just to check --
}
and also tried
// Check empty template vars
else if(key.toLowerCase().includes("{{")){
value = "";
}
Any suggestions?
Gee, thanks for this wonderful video. However, i've already my google form and have collected the orders in a google sheet. How can I use your script to generate from the already populated google sheet? Appreciate your help. Thanks a zillion.
Thank you so much for the great explanation. May I ask you how can I make the invoice-Nr to do the count by itself. i meant that the invoice-nr starts with 0 and counts +1 every time
great content
Thank you.
Hello!
Your video helped me a lot.
I didn´t use the google form but the functioning its the same, do you know how can I pass an image url as a variable to use on the docs template? eg. the picture of the product.
Right now its passing it as an url only.
This is a great vid. So I have about 4 or 5 regular clients who I invoice regularly. Is it possible to adapt this so that when it comes to enter the client's details, rather than have to type in first name, last name, address etc, I can just type "Bob" and all of Bob's details will be auto-filled? Or maybe just choose the client from a drop down with their details pre-loaded?
This great 👍
Hi Just a question, if I am to add more items, how do I do that? I add adding more items in the form but on the invoice generated, only those 5 items were shown. But on the responses, I have all 10 items. Hope you can help :)
Thank you
Hi Automagic, thanks for the video it is great just what I needed. 1 little issue, the spreadsheet populates fine. The invoice template does not, the only part in the video I could not follow was where you clicked "my function " and it revealed a dropdown menu "create doc from form". I do not have this choice my dropdown only includes "my function". Have you any idea what may be the issue. Many thanks, Tony.
Make sure you copy and pasted the script from the github link into your apps script editor. That function was one that I defined. The "my function" is default.
I deleted the myFunction at the top and then delete the vary last bracket at the end of the script. Then all the function options were available
@@AutomagicTV I have done that but still only have 'my function'
Waiting for more magic. Come back
Thank you very much on this tutorial!
May I ask that in 11:00 there are 3 rows with $0.00 that looks redundant, any method to make it blank? Thanks in advance!
I have a template file for my invoice with a table with sample row, but I want to add more rows dynamically based on a given array size, and write the cells values from the array...
I've been struggling for almost 3 days now.
Is there any easy way to accomplish that?
A very valuable video. May I know if I have to add the Facebook profile link to the google form, how to write the regular expression for it?
Hello and thank you for the information.
I have a Google Sheets estimating tool that I built and would like to have the information in a form format to present to the client. Is this function possible?
My Google Form contains a question in multiple check box format so that translates to it inputting the column title to "Example [1st choice]" in Google Sheets. How do I put in the var function since it doesn't recognize the brackets and also because I can't change the titles for the columns in Sheets? Thanks in advance!
Is there a way that sales tax could be added as a replacement for discount?
This is great. I'm having an issue with the format of how dates and times are populating the doc. Dates look like Sat Dec 30 2023 (when I want them to be MM/DD/YYYY). Time is populating with 08:32:11 GMT-500 (Eastern Standard Time), when I want them to look like 8:32 AM. Can you help me?
Hi im using almost the same functions as yours.. but I just need to change the discount into ship fee then subtotal will sum up to the ship fee... thank you
Can you add drop down multiple choice answer options for different product prices?
This was really helpful, can you help on how can get data from Gmail to sheets with identifying information for the email body
Hi, thanks for this video.. I believe I did everything correctly, but where do we link that specific form to the invoice template? When I submit the form, the answers go to the responses spreadsheet but no new invoice is created with that info..
Hi! Your code is working for me. The only thing I'd like to change is for the Unit Name to be "Quantity" and this to be multiplied by the "Price" or what I want to call "Cost per item" to get the actual "price" for those item(s) since sometimes my company may sell or purchase more than one of the same item. We use the description as the unit name / description on our invoices. Do you have any tips here to make it a multiplier for pricing and to change "price" to the "Cost per item" and for "price" to be the Quantity * Cost per item?
It's possible. You may be able to do this with something similar to this line [1]. You can set a final price and update it to price * quantity as you loop through. And for each new loop you would reset it to 0.
[1]: github.com/automagictv/Apps-Scripts/blob/d77c7f8c959ce4a94c06680a108e33c7061463f5/automatic_invoice.gs#L50
@@AutomagicTV I have little to no background to appscript. Would you be able to show how this is done?
I need help. I can't select the "createdocfromform" function because it is not an option for me on the script. What do I do?
thank you1 how can you add in product options... ie. colour options and even surcharges on certain options and comment sections for buyer to specify customization such as names to be added to products, etc? Images for each item & option choices as well.
Hi, your video is very helpful. I need help I have working for one plastic sheet making company. I have made finish stock form and response google sheet with QR code. I want to make dispatch invoice ( example 5 items with QR code ) , just to scan 5 QR codes with take entry in Google sheet and make dispatch invoices for a single supplier. Can it be possible?
Thank you for sharing , can I include terms and conditions ?
What if we want to increase or decrease the items in invoice dynamically can we do that in form or atleast if the details are not filled in the form then the empty row should also not display there in the pdf
Hi there, any chance you have a video showing how to do this with the custom Invoice template I created for our business?
how do you do that for nominal numbers there are separators with commas or dots?
Hi I like to know if you can possibly help me with this for my own business. I have made it in forms but I need help how to have it automatically in a custom estimate sheet
woahh!!! Thank you ! Is it possible to put the same invoice (customer) data into multiple templates? Is is possible to trigger it without filling a form, for example with a ticked checkbox in a cell? Than you!
This is wonderful! How would you change it so that people can order multiple numbers of each product? I sell books. Also can the discount be done in a percentage? Thanks!
Great idea. I wish to have the forms on my website where it cN stored to my workspace drive. i am new to the business and needs your advice plz.
When I try to select the function I want to run (7:45 in your video) I don't have that drop down list of options. Instead, I have just one option which says MyFunction. Have I done something wrong before this?
Hello. Thank you. I would like a 3-part tutorial for Google Workspace: 1) a customer gives email, address, number (that generates an incomplete estimate invoice on website (via Google Forms) 2) an email is generated to admin and admin is able to access that incomplete estimate invoice and add prices for an estimate 3) customer gets complete estimate invoice. Thank you so so so much.
Perfect!! Is there a way(formula) to match the name and date from the sheet and generate ONE invoice for the same client per date? ! I place one item from the same client many times a day.i dont want to generate so many invoices. Thanks
Is it possible to select different invoice template for different entries
Really great; thanks for the information. But I keep getting 'Exception: Unexpected error while getting the method or property getFileById on object DriveApp' as n error; where did I go wrong?
This is great...Do you have a video that automates grouped data i.e from a database I need everyone between the age 18 to 35?
Please Automagic,
What will be the regular expression pattern for an 11 digit local phone number?
Thanks.
Best video I've found as a beginner. I am having trouble, in Apps Script the only function option is MyFunction, not createdocfromform. Is there something I need to do to make this accessible?
Why does my sheet, after I click on responses and create new sheet for the form, not show script editor? Did I do something wrong?
If we use different Invoice or order template, can the script works the same way?
AutomagicTV, the new doc showed up in the correct Drive (yay!), but it's showing all the original template information, not the Form responses. Is there a way to use/modify your code with a template that I created based on (but not using) your template link?
Hey Automagic this code is working for changing the filename, but the {{Company Name}} etc. fields are not getting replaced. Please help
Hi, Awesome video! Can you make a video about automate email invoice. Invoice automated and send to customers?
@ 6:11 (TOOLS - SCRIPT EDITOR) I do not have that option. Script editor doesn't exist - any suggestions would be great !
IN THE END OF THE WHOLE EXERCISE THERE WAS NO SUBMIT BUTTON ON THE FORM?
I have item list and price to select from in the line items how do I do that?
Hello there, I have followed the same steps, everything is going smooth, the form is created, and is working too but there is no invoice made. Could you please let me know where I did the mistake.
When I submit, no new document appears. What might I have done wrong?
Hello.. I have added all the scripts as directed by your video.. But when i submit form file gets generated but the variables dont get changed.. Request if you can help
I just created a template in sheets and tried to launch with same script, but unfortunately unsuccessful. Is there any possibility to create a script for sheets template?
I have got this working and have added a column that autogenerates the invoice number. I cannot figure out how to get this number to be included on the pdf, any ideas? I would also like the pdf to be named with the invoice number. Great video though thank you so much
Is there something wrong with line 100 ? every time i hit debug it highlights line 100 and when i proceed the document always fails with an error message "cannot read property ' getLastRow' of null.
Hi sir, i want to use the Spread sheet Invoice template instead of Doc template. please help me