I've downloaded and trying to add another item to the price list with the same description (1kg) but I need it as a different price as to the "honey 1kg £25.00. Is there a way to have the same description but for it to come out as different price. Any help would be great. As when i input into the item sheet it will come up with honey price Thanks
Yes, you need to add another column that combines the two, e.g. if in C1, type =A1&" "&B1 - this will be value in column A then add a space (in quotations), then adds whats in B1. You'll then obviously need to look up this new column. This video has more about the "&", so might be useful: th-cam.com/video/b5aU54dL1ho/w-d-xo.html
@@bloomfieldanalysis Where do I need to put the formula in the pricelist page? That's where I tried but it didn't work. It just copied the price and the weight so I ended up with 8 1kg in the column D. Would the formula need to change on the items sheet column E? Sorry Very confused
Thank you so much for the template! I have a few suggestions to make the process more complete (just point 1 would do). If any of you can help, it would be so appreciated! I am not an expert, so I am not aware of Google limitations, but I’ll try anyway: 1. Automate the invoice sending process with Gmail. And for those of us that need to send the invoice/order in xlsx format to the warehouse by email: 2. Convert the invoice to xlsx and save it to another Drive folder. 3. When payment is done, trigger the script that send the xlsx to the warehouse email address. 1. I need to attach the pdf invoice to Gmail with a preset title + body (title: invoice number + body: “Attached please find your invoice. Kind Regards and my signature”) and send it to the customer email address based on a cell value (the email address being found either in the Database sheet or, in my case, also in the Invoice sheet itself, in the "Invoice For" section). 2. Ideally, I’d also like for the script to simultaneously create an xlsx copy of the invoice and save it to another Drive folder (without sending it just yet). Only after the payment has been done by the client, I’d need to send the xlsx invoice to the warehouse email address, with the title as the number of invoice + the body as a simple “Attached please find the order in xlsx”. 3. It would be convenient to send the xlsx by going to my Database sheet, simply select “send to warehouse” in my “status” column with a drop down list and somehow the script gets activated. Ideally, if the script has been successful, the script itself would update the drop down status column to “SENT to the warehouse”. So if any error happens, I am notified by the fact that the status doesn’t get updated. Many many thanks in advance!
Hello! For 1, try this video: th-cam.com/video/nu06D2Qj8No/w-d-xo.htmlsi=zNTPMipWfZyZMxwJ You may need to adapt the code, but it will send emails for you. Its not got many views though, so I'd be interested in opinions on why that is, as I find the code really useful. Its not my original code, its from the google developer website. There are other videos that give a top level view, but I try to show people how they can adapt, and I adapt the code for the invoice template (only a couple of small changes, so dont worry if you aren't an expert). For 2, it should be doable! For sending emails, again, I'd point you to the video i've linked for no. 1. For saving as xlxs, I did originally try to download pdfs but I had issues, which is why I save onto the google drive. So i have a feeling this could be tricky. Give this a go, but I haven't tested it: function exportAsXlsx() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); // Convert the spreadsheet to an Excel file var excelFile = DriveApp.createFile(spreadsheet.getName() + ".xlsx", spreadsheet.getBlob(), MimeType.MICROSOFT_EXCEL); // Provide the download URL Logger.log("Download Excel file from: " + excelFile.getUrl()); } 3. Yes, nice idea. You probably wouldn't to run ithe code from the dropdown in the status column, but like you said, you have a dropdown in status, select "send to warehouse" for any you want to send. Then create a menu option from the top, which will send to warehouse for any with that status, then once complete it can change the status to "sent warehouse" (maybe a different colour so clearer to see! Apart from sending emails, we dont have an off the shelf solutions. We do create bespoke solutions, if that is something that you are interested in, to find out more go to bloomfieldanalysis.com/sting Or straight to booking: bloomfieldanalysis.com/booking
Thank you so much! What about if I have just number "1, 2, 3, 4..." instead of letters and numbers (INV-0001) when I try with only numbers the code doesnt work :(
Hi - thank you for your comment, and email too! Hopefully my email reply has helped. For anyone who may be experiencing a similar issue, the print all invoices was working fine but the select invoices didn't like numbers. If you also experience this issue, try adding a letter /character to the invoice number. Let me know if there are any other issues, thanks all!
HI Thanks so much for the videos! I thought I had it all set up correctly but I'm getting the following error when I hit either select invoices or print all invoices "Exception: Cannot retrieve the next object: iterator has reached the end." appreciate any help!
check the name of the folder in your google drive, or access to it. This line: var downloadsFolder = DriveApp.getRootFolder().getFoldersByName('pdf_invoices').next(); Also - Chat GPT can be really helpful for debugging code. Copy and paste the code with your amendments and it might spot something! It could be something tiny a human would find hard to find like a missing or extra bracket. You can also try downloading again and making changes. Good luck - hope you find the issue
Hi there. Thank you for the video. Great job! But there is a problem when I press the Print All Invoices button I have this message that appears"Exception: Cannot retrieve the next object: iterator has reached the end."
Hello@@bloomfieldanalysis ! Thanks for this amazing tutorial. However, I am not able to print selected/ all to pdfs. The message above still appeared despite having the same folder name created...What can I do?
@@yayadanceacademy-rad Have you made any changes to the script or the google sheet? maybe try downloading again and try using the example with no changes, see if that works. You should only have to create the folder. Then start adding in your data, and see if it still works.
Good question! Up to about 15 items can fit pretty comfortably on it with a bit of reformatting - obviously making the items part bigger and condensing some of the header information. More than this, I would have this front page as a summary (invoice to, from, net amount, vat, gross), then have a second page with the itemised list. The beauty of learning to do this yourself is you can create a solution to fit you. Or if this will take too much of your time, I can create bespoke solutions.
In the Apps Script, there is this line, this names the file. valueToPrint is the invoice number: var fileName = 'Invoice_' + valueToPrint + '.pdf'; you will need to add another variable to this: var fileName = 'Invoice_' + valueToPrint + clientName +'.pdf'; Then further up is where we set valueToPrint: var valueToPrint = data[i][0]; the i is the ith row, the 0 is the 0th (1st) column, which is the invoice number. If your client name is in column D, that will the 4th column, 3rd index. so below that you can add a line like this: var clientName = data[i][3]; Have a play with that :)
sounds like there is no data to read, the range should pick up all the data in the active sheet. Feel free to share with me and i can have a very quick look :) bloomfield.analysis @ gmail dot com (dont want any bots picking up the email address!)
@@Hamza-pd6oq to be clear I need automated reports generated from my existing data table and have them saved to a user specified Google drive folder, if the door agent exist it should create it. Also have auto generated emails to a different recipient for each report originating from my Google email.
For more videos: th-cam.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html Download FREE templates BloomfieldAnalysis.com/sting OR Subscribe and access ALL TEMPLATES in the Community tab: www.youtube.com/@bloomfieldanalysis?sub_confirmation=1 How to create an automated invoice is here: th-cam.com/video/Xd9r2LzLbMU/w-d-xo.html Create an automated invoice: th-cam.com/video/Xd9r2LzLbMU/w-d-xo.html See Description for timings if you'd like to skip to certain parts
Yes, you can :) Head over to bloomfieldanalysis.com/sting where you can download. As this one has got apps script, so you will get a warning and may want to view the apps scripts first (just good practice!)
I was here for the "printing" part. But sadly, i found out it was just a clickbait. You only guide upto pdf creation. Sad indeed. I needed to automate printing to reduce manual efforts.
I'm very sorry to hear that, I thought it was clear that it was "print to PDF" - it does say "Printing PDF invoices" on the thumbnail, the picture also shows PDF files, and the description says "Google Sheets to PDF". I think you'll struggle to code printing directly to the printer, it will likely to depend on the printer, its setup and numerous other factors. And will always be manual to have printed paper versions. You can automate printing to PDF and emailing out. Thank you for your feedback. Good luck finding what you are searching for. If you do find it, maybe post on here so others can also find it in case they are also looking for the same thing.
I try to add a failure column in datasheet to lodge the invoice number that failed to generate the pdf. I would like the code to copy the invoice number to that column if the pdf failed to generate, where should I put the following code? "invoiceSheet.getRange('M11').copyTo(databaseSheet.getRange('C1').getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);"
Hi there, I had to think about this one, you can have a play and try different places, but I think that because when the code fails, it stops running, it may never run that code! You will probably be better of signalling when it has succeeded (it might be doing this anyway). Sorry I dont have time to look at this in detail right now, but if you have the time and interest, maybe look at the code for sending emails, because that has some code that pastes whether an email has been sent. Its an adapted code that works on the invoice system: th-cam.com/video/nu06D2Qj8No/w-d-xo.htmlsi=si-ih0q34wOTmOT5
I've downloaded and trying to add another item to the price list with the same description (1kg) but I need it as a different price as to the "honey 1kg £25.00. Is there a way to have the same description but for it to come out as different price. Any help would be great. As when i input into the item sheet it will come up with honey price
Thanks
Yes, you need to add another column that combines the two, e.g. if in C1, type =A1&" "&B1 - this will be value in column A then add a space (in quotations), then adds whats in B1. You'll then obviously need to look up this new column. This video has more about the "&", so might be useful: th-cam.com/video/b5aU54dL1ho/w-d-xo.html
@@bloomfieldanalysis Where do I need to put the formula in the pricelist page? That's where I tried but it didn't work. It just copied the price and the weight so I ended up with 8 1kg in the column D.
Would the formula need to change on the items sheet column E?
Sorry Very confused
Share your copy with me and I can make the change quick, view only will do and I can make a copy, Bloomfield.analysis@gmail
Thank you so much for the template!
I have a few suggestions to make the process more complete (just point 1 would do). If any of you can help, it would be so appreciated!
I am not an expert, so I am not aware of Google limitations, but I’ll try anyway:
1. Automate the invoice sending process with Gmail.
And for those of us that need to send the invoice/order in xlsx format to the warehouse by email:
2. Convert the invoice to xlsx and save it to another Drive folder.
3. When payment is done, trigger the script that send the xlsx to the warehouse email address.
1. I need to attach the pdf invoice to Gmail with a preset title + body (title: invoice number + body: “Attached please find your invoice. Kind Regards and my signature”) and send it to the customer email address based on a cell value (the email address being found either in the Database sheet or, in my case, also in the Invoice sheet itself, in the "Invoice For" section).
2. Ideally, I’d also like for the script to simultaneously create an xlsx copy of the invoice and save it to another Drive folder (without sending it just yet).
Only after the payment has been done by the client, I’d need to send the xlsx invoice to the warehouse email address, with the title as the number of invoice + the body as a simple “Attached please find the order in xlsx”.
3. It would be convenient to send the xlsx by going to my Database sheet, simply select “send to warehouse” in my “status” column with a drop down list and somehow the script gets activated.
Ideally, if the script has been successful, the script itself would update the drop down status column to “SENT to the warehouse”. So if any error happens, I am notified by the fact that the status doesn’t get updated.
Many many thanks in advance!
Hello!
For 1, try this video: th-cam.com/video/nu06D2Qj8No/w-d-xo.htmlsi=zNTPMipWfZyZMxwJ
You may need to adapt the code, but it will send emails for you.
Its not got many views though, so I'd be interested in opinions on why that is, as I find the code really useful. Its not my original code, its from the google developer website. There are other videos that give a top level view, but I try to show people how they can adapt, and I adapt the code for the invoice template (only a couple of small changes, so dont worry if you aren't an expert).
For 2, it should be doable!
For sending emails, again, I'd point you to the video i've linked for no. 1.
For saving as xlxs, I did originally try to download pdfs but I had issues, which is why I save onto the google drive. So i have a feeling this could be tricky. Give this a go, but I haven't tested it:
function exportAsXlsx() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
// Convert the spreadsheet to an Excel file
var excelFile = DriveApp.createFile(spreadsheet.getName() + ".xlsx", spreadsheet.getBlob(), MimeType.MICROSOFT_EXCEL);
// Provide the download URL
Logger.log("Download Excel file from: " + excelFile.getUrl());
}
3. Yes, nice idea. You probably wouldn't to run ithe code from the dropdown in the status column, but like you said, you have a dropdown in status, select "send to warehouse" for any you want to send. Then create a menu option from the top, which will send to warehouse for any with that status, then once complete it can change the status to "sent warehouse" (maybe a different colour so clearer to see!
Apart from sending emails, we dont have an off the shelf solutions. We do create bespoke solutions, if that is something that you are interested in, to find out more go to bloomfieldanalysis.com/sting Or straight to booking: bloomfieldanalysis.com/booking
Thank you so much! What about if I have just number "1, 2, 3, 4..." instead of letters and numbers (INV-0001) when I try with only numbers the code doesnt work :(
Hi - thank you for your comment, and email too! Hopefully my email reply has helped. For anyone who may be experiencing a similar issue, the print all invoices was working fine but the select invoices didn't like numbers. If you also experience this issue, try adding a letter /character to the invoice number. Let me know if there are any other issues, thanks all!
HI Thanks so much for the videos! I thought I had it all set up correctly but I'm getting the following error when I hit either select invoices or print all invoices "Exception: Cannot retrieve the next object: iterator has reached the end." appreciate any help!
Hmm... does it work if you just put one invoice number in?
@@bloomfieldanalysis no sadly I get the same message.
check the name of the folder in your google drive, or access to it. This line: var downloadsFolder = DriveApp.getRootFolder().getFoldersByName('pdf_invoices').next(); Also - Chat GPT can be really helpful for debugging code. Copy and paste the code with your amendments and it might spot something! It could be something tiny a human would find hard to find like a missing or extra bracket. You can also try downloading again and making changes. Good luck - hope you find the issue
@@bloomfieldanalysis it was the name of the folder!!! Thank you SO much! this is so incredibly helpful!
@@tinastriebel8100 Brilliant! Glad to be of help 🐝
Hi there. Thank you for the video. Great job! But there is a problem when I press the Print All Invoices button I have this message that appears"Exception: Cannot retrieve the next object: iterator has reached the end."
does the print selected option work?
same message appears
Glad you got it working ! For anyone who also comes across this problem, please check the name of your google drive where the pdfs are trying to save
Hello@@bloomfieldanalysis ! Thanks for this amazing tutorial. However, I am not able to print selected/ all to pdfs. The message above still appeared despite having the same folder name created...What can I do?
@@yayadanceacademy-rad Have you made any changes to the script or the google sheet? maybe try downloading again and try using the example with no changes, see if that works. You should only have to create the folder. Then start adding in your data, and see if it still works.
thanks a lot for the tutorial. however, i wonder how would the printed invoice look like when you have like 20 items in it.
Good question! Up to about 15 items can fit pretty comfortably on it with a bit of reformatting - obviously making the items part bigger and condensing some of the header information. More than this, I would have this front page as a summary (invoice to, from, net amount, vat, gross), then have a second page with the itemised list. The beauty of learning to do this yourself is you can create a solution to fit you. Or if this will take too much of your time, I can create bespoke solutions.
Thank you! How can I add the client name field in the file title line?
In the Apps Script, there is this line, this names the file. valueToPrint is the invoice number: var fileName = 'Invoice_' + valueToPrint + '.pdf';
you will need to add another variable to this: var fileName = 'Invoice_' + valueToPrint + clientName +'.pdf';
Then further up is where we set valueToPrint: var valueToPrint = data[i][0];
the i is the ith row, the 0 is the 0th (1st) column, which is the invoice number. If your client name is in column D, that will the 4th column, 3rd index. so below that you can add a line like this:
var clientName = data[i][3];
Have a play with that :)
Ok thank you! Much appreciate your videos and responses 👏👏
Hello when I press the Print All Invoices button I have this message that appears TypeError: Cannot read properties of null (reading 'getRange')
sounds like there is no data to read, the range should pick up all the data in the active sheet. Feel free to share with me and i can have a very quick look :) bloomfield.analysis @ gmail dot com (dont want any bots picking up the email address!)
would you pliz share with us the code
The code is available to download from bloomfieldanalysis.com/sting
Can you be hired to create similar functionality to this in my existing Google sheet?
Hi @bob.bobman, I am a junior software engineer and have a years experience in google appscript I can help you out
do you have number i can contact you on
@@Hamza-pd6oq send me a link to your LinkedIn or similar so I can verify your identity and credentials please.
@@Hamza-pd6oq to be clear I need automated reports generated from my existing data table and have them saved to a user specified Google drive folder, if the door agent exist it should create it. Also have auto generated emails to a different recipient for each report originating from my Google email.
@@bob.bobman cool I can have a look for you
For more videos: th-cam.com/channels/yqHAMnFt2yTyYgYsxQMCjQ.html
Download FREE templates BloomfieldAnalysis.com/sting
OR Subscribe and access ALL TEMPLATES in the Community tab: www.youtube.com/@bloomfieldanalysis?sub_confirmation=1
How to create an automated invoice is here: th-cam.com/video/Xd9r2LzLbMU/w-d-xo.html
Create an automated invoice: th-cam.com/video/Xd9r2LzLbMU/w-d-xo.html
See Description for timings if you'd like to skip to certain parts
Interested teacher. Can I have the file here, sir?
Yes, you can :) Head over to bloomfieldanalysis.com/sting where you can download. As this one has got apps script, so you will get a warning and may want to view the apps scripts first (just good practice!)
@@bloomfieldanalysis thank you very much teacher
I was here for the "printing" part. But sadly, i found out it was just a clickbait. You only guide upto pdf creation. Sad indeed. I needed to automate printing to reduce manual efforts.
I'm very sorry to hear that, I thought it was clear that it was "print to PDF" - it does say "Printing PDF invoices" on the thumbnail, the picture also shows PDF files, and the description says "Google Sheets to PDF". I think you'll struggle to code printing directly to the printer, it will likely to depend on the printer, its setup and numerous other factors. And will always be manual to have printed paper versions. You can automate printing to PDF and emailing out. Thank you for your feedback. Good luck finding what you are searching for. If you do find it, maybe post on here so others can also find it in case they are also looking for the same thing.
I try to add a failure column in datasheet to lodge the invoice number that failed to generate the pdf.
I would like the code to copy the invoice number to that column if the pdf failed to generate, where should I put the following code?
"invoiceSheet.getRange('M11').copyTo(databaseSheet.getRange('C1').getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);"
Hi there, I had to think about this one, you can have a play and try different places, but I think that because when the code fails, it stops running, it may never run that code! You will probably be better of signalling when it has succeeded (it might be doing this anyway). Sorry I dont have time to look at this in detail right now, but if you have the time and interest, maybe look at the code for sending emails, because that has some code that pastes whether an email has been sent. Its an adapted code that works on the invoice system: th-cam.com/video/nu06D2Qj8No/w-d-xo.htmlsi=si-ih0q34wOTmOT5