Google Sheets Web App th-cam.com/play/PLv9Pf9aNgemt82hBENyneRyHnD-zORB3l.html Google Sheets Sidebar Userform th-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html Google Sheets CRUD Userform th-cam.com/play/PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx.html
This is fantastic. I followed this in a real use case and it totally works! My next step is dealing with a second data list that is tied to the first by that ID with a 1-many relationship. Excited to see where I can take this!
Thank you thank you thank you!! I've been watching your video for at least two weeks because I work long hours and I watched it over and over and I finally paid the patreon membership and was able to complete our new form. I was doing something wrong some how but I just copied and pasted everything in the end and modified it to our form's needs. lol. Keep up the great work!! Looking forward to your other videos.
I love when you "make mistakes" since we then get to see how you fix them! I have been able to automate quite a few spreadsheets and workbooks thanks to your lessons!!!!
Or people like me in a large business looking to simplify their job. Honestly at this point though I’ve gotten good enough at manual entry it might not be too much faster
I am so glad I found your video, very informative easy to understand and the logic you present behind the scripting very helpful. I watched the video to do this following it step by step and came out with a better understanding that when I started,. I have subscribed to your videos and am excited to learn more
thank you so much for this video in particular. With this and a few rabbit hole references to chase down the other functionality I need, I've been able to get a new RMA system up and running in three days with nothing but a spreadsheet and some fancy JavaScript, even importing the old data from the previous system on a dying XP box. I appreciate this.
This is an excellent video! Thank you for providing it. I have a question. Is there a way include instructions to clear the fields upon saving? I've been playing with it to see if I can make it work and it's just not working.
Your series of videos are extremely useful. They are so simple and properly explained that the tips given by you can be used at other places. Questions: How can the user jump from one field to next without much hastle or using tab. How do you ensure that entry to a field is completed before a script is executed.
@How can the user jump from one field to next without much hastle or using tab. Don't really have a good solution for this. @How do you ensure that entry to a field is completed before a script is executed. You would need to validate everything in the script itself before actually making any changes to the spreadsheet.
@@ExcelGoogleSheets what if you protect unused cells and disable clicking on same, i think this will jump protected cells and go directly to enabled once
I learned so much from you. Thank you. I am building a production output database and this is exactly what i need. Where can i reach this file to make a copy?
Thank you very much, I'm trying to incorporate this form in my spreadsheet, but I don't have the ID column in the data, can I use a second column with formula to create some sort of id for each row or maybe I can use the same search column as ID for the saveRecord? thank you
Great video, as always. I wish we had the same functionality as Excel, when you protect one cell and set it in a way that the user cannot select it. This way the user could navigate only through the fields of the form. I've built great forms in Excel like this. When I try to do the same in Google Sheets, it just looks terrible...
This tutorial was super useful. Thank you very much for sharing. Is there any chance that you could advise how can we adjust clearing content while leaving formulas in certain cells? It clears everything including formulas..
Great instructions. I have a question..... How do you amend the Search Function to allow for multiple responses, getting the Form to move from each response to the next? I am trying to use this for a database, for inventory, where it is possible that the search (text box with 80 characters) will return multiple records. I need a way to move from one response to the next.
Hi there! Thanks for the useful videos! Question: what if we have more than 4 cells to read? A column of 50 for example. And we need to store that data in a row (transpose)?
Hi, thank you for all your tutorials; I´ve started coding with your help, still a beginner but I´m so thankful for all your guidance. I have a question: will this entry form be something that any user that the google sheets has been shared with can use? Also, do you have any tutorials on how to automate getting the last row from a database to fill a google docs?
Hi, thanks for the wonderful information you share here, I follow you step by step on a google sheet that I want to key in and store data on. and the when I reach to the console.log part, The Info there show [Function], what do this mean?
Thank you!! But I'm trying to figure out when editing existing data, if I want to let if(id == "A:A") (which means compare to all the values in A:A, if not then create a new record), what should I do?
Hi, thank you for the helpful video! I was wondering if you could help me figure out how I can automatically add a timestamp as well to the Data sheet when saving a record.
Hi .... 🙋♂🙋♂ thank you for sharing the valuable Edu. video for us. I learn lots from that video. but I have one quotation, if the file share with another person and i want to only he update the data but not delete or edit after save the changes then how I can do, its possible ??
can we add some options of manual entries also please? For example id my marketing team member has to fill this they would have the fixed product listed by=ut i they have to mentioned but it one has to fill prices manually it was sold for for multiple product. Please if you could help on this?
Quick question with this, could it be configured to reject any of the inputs(not add the entry to the data set) if they already exist in the data set, when you click the save button? Additional question: Can this be done across workbooks or just across sheets in the workbook where the form exists?
Thank you so much for your great tutorials. May I know how can my data entry be converted to all capitals when it enters the database? Thanks in advance, keep up the great work.
Thanks so much for this great tutorial. I have a data entry form with several cells. When I use the cost getRange = [""], the command enters the next line due to the several cells in the data entry form. Which command can I use to make the command run as one line without breaking? Thanks
This video was so awesome, thank you so much! I took this idea and ran with it for something at work. My issue now is whenever I share with another person they get this scary message that they are sharing data with an unverified app. Do I need to verify this application with google or is there a way for me to modify something so that I can easily share this with a few other coworkers.
Love your videos. Great help. I did the entries and all worked as planned. Then something went wrong. I add some setFormula's to a different function and now the saveRecords function will not append my entries to my data. I checked and triple check the entries for the function saveRecords. Would you be able to help?
Hello ,Thanks for these amazing videos prepared to foster good and clearer ways to learn app script. I can't get the OnEdit function to work. It issued out the following: TypeError: Cannot read property 'range' of undefined Is there any change in the appscript related to the class (range)?
Also make sure to not manually run this function, otherwise you will get that error. The function should run when you make changes to the spreadsheet automatically.
@@ExcelGoogleSheets Thanks for answering asap. This is the same line you have shown: I have put a button to execute the search the function(busquedautil())anyway... function onEdit(e) { console.log(e.range.getA1Notation()) console.log(e.source.getSheetName()) if(e.range.getA1Notation() !== 'C13') return if(e.source.getSheetName() !=="DATOSNOTAS") return busquedautil() }
Thanks so much for your videos, I've seen them before and they are very concise. I've subscribed. I built this as described and modified to my needs works great!. One question I would like to have the deleted items go to a record ("deleted"). tab. I would like when the delete button is activated to have "CopyTo" function activate and send a copy to the "deleted" tab. What script do I need to add to the deleteRecord function to make this possible.
Until your video I didn't know what a script was. Fantastic and easy to understand. But, like always a question. My data sheet contains 8 columns including currency, numbers and dates. When I feed info in, all are ok, numbers with 2 decimal points, amounts with $ symbol and correct decimal places however, my dates show on my data sheet as "month/day/year". No matter what I format or what I enter the format is always the same. Help!
great tutorial! is there any way to do like go to the next textbox upon entering the data like tab index? so that no need to click each row just to enter the data. thanks
@@ExcelGoogleSheets i actually did that on my previous webapp which i learned also from your channel. its just so happened that i have a simple spreadsheet form that i want to apply that tab index upon hitting the enter and will automatically go to the specific cell. Im actually trying to figure it out using your onEdit tutorial. hoping that i can find the answer on that video. Thanks anyway man you are a legend!
@@ExcelGoogleSheets yeah or I should have the same format all the way down so the code would be as is and wont give an error.. thanks man! Im actually trying my best understand everything specially about the webapp. Please do not stop uploading this kind of tutorials especially in appscript and webapp. 😅You are the best man! if its not too much for you kindly do an approval system webapp😅
This was awesome! Using it to create an inventory of my home bar and it is working great! Quick question: I have one dropdown on the Form tab called "Bottle Status" with options of Unopened, Opened, and Empty. Is there a way when "Empty" is selected the data can be moved from the Inventory tab to a "Past Bottles" tab? Trying to keep track of all of my previous bottles, but don't want them on my Inventory tab once I finish them. I found a couple of examples, but they use onEdit which I cannot figure out how to do since this script already uses onEdit. Hope that makes sense!
Your tutorial is awesome! I follow this guide and adjusted according to my needs. It works like a charm. For the onEdit(), I have to make it an installable trigger instead of simple trigger in order to make openbyID() to work. BTW, how can 2 or 3 users use the same Page to enter record at the same time? Make 3 duplicated data entry pages and each of them use different page? will it work? Thank you so much for your great work
I love your tutorials! In trying to modify an existing dataset, I have run into a bit of an issue I don't understand. The Form is simple ID Search Serial Number Brand Location Model Version Asset Tag Comment In the Inventory data their is a field Brand that is using a RegexMatch to autofill this field. If I search to find a record, then modify say location and save, the form is filling in this value from the querried valued. How can I skip that field on the saveRecord function. The Brand is determined by the starting 3 characters of the Serial Number.
Hi, if you read my comments, can you help me how I can create the "ID" like UPR 0001 (then become UPR 0002, UPR 0003 and so on) instead of just single number? Thank you so very much in advance for help and this knowledge.
i have a question regarding a file like this is their any way we can have pictures be put as well like you have names and ages states is their a way to have a picture for their like profile aswell?
Question for you, we're looking to keep track of our members and this script is fantastic, however we were also wondering if there was a way to create a another tab like data named "LOGS" and individually track changes to data inputs. In example, the most current information for that member would be displayed in "DATA", however the overall slew of changes to all members would be listed line by line in "LOGS" so we can see what was done to it?
SIR YOUR VIDEO SO MUCH HELPFUL FOR ME WHO DOESNT KNOW ANYTHING REGARDING THIS..TQ SO MUCH..BUT CAN I ASK U SIR?..HOW CAN WE USE SAME SCRIPT AND SAME SHEET BUT ACCESSED BY MULTIPLE USER IN A TIME..?
What would the code be if I had some cells in the form which auto updated ( such as today() ) and I did not want them to get erased when I press the new Record button ? However they all still get transferred/ saved to the Data sheet .
This is fantastic. only had a few snags... 1) why does the phone number on my sheet, get saved on the data form without the leading 0 ? I have set the format of all the relevant cells to be plane text but it still just drops the 0 at the beginning of the phone number? 2) I added the search column, and it broke my saveRecord button?? it ran through the motions and even updated the id cell, but no data was saved? I removed the coloumn and it still didnt fix it, I had to do a rollback?
Have you tried to make both the entry cell and the data column formatted as plain text? When you have errors, check under Executions panel and see if you have hailed scripts. It should give you some clue where to look.
Your videos are very educational and have raised my awareness and skills tremendously! Thank you for that. My question: do you also share the code that you create during the video? Where can we find your sample code? It would help in learning even more.
I too would like a copy of the code so I can follow along with the video and once I have a working example I can edit to fit my needs. I don’t see a copy of the code in any of the links I clicked on. Is there another way to get it besides watching the video … stopping every few mins and writing it down?
Hello I built a Data Form following your videos, I'm not sure if it was this one or a search one, but you had a code within it - =QUERY(Data!A1:J,"SELECT * WHERE LOWER(E)= LOWER("""&B2&""")", 1) - what do I need to change if the column holds numerical answers as well as alphabetical, where the answer is definitely a numerical one and not a alphabetical one. favourtie TV series etc, 24 not Twenty Four :D
Hi. Tq for the tutorial. I just have a tiny problem with the script. The error I got was on the onEdit script. It said that the e.range was not defined
Dude Just have one last question - I had put up a formula which sums a total amount when I click on the submit button it clears the formula, Can you please suggest a solution for this.
Thank you for the video! May I ask how to filter if data column 2 is date? I have tried const recordFound = data.filter(r => r[1] == searchValue), but [ ] nothing can be found in the Logger.log(searchValue)
Hi pls advise I follow the steps up till “search” got 2 formula running failed 1) if(recordsFound.length === 0) return 2) idCell.setValue(recordsFound[0][0]) error msg 1) the “.” Problem and “===“ 2) is the “[“ “]” problem and “(“ “)” I am stuck at this point not sure how to do. Kindly advise Thanks
Hi, great video, was very helpful! I created my own database using this as an example but i am however, having an issue with saving a field "Mobile Number" as the number being saved is loosing the leading 0. Doesn't matter if the field on the "form" page or the working spreadsheet is formatted as text. It appears the process of extracting the data from the "form" page is treating it as numerical and thus the leading 0 is being removed. Any suggestions on how I can correct this?
Thank you very much! Your tutorials are really informative and clear but I have a questions: Can I use Google spreadsheet as cloud date base to be accessed from Android Application? If this is possible, please guide me how to do it. Thanks
Hi. i have been making my version of data entry form by looking at your video. i had a few questions. One, is it possible to do what we are doing when the data in the cell is a photo. (This is on the form sheet) Second, the data sheet, where the data will be saved, is it possible to have additional columns after the data columns? These additional columns will have formula stored in them.
wanted to know badly weather you can actually place the Data Tab in the same tab in Form? just right underneath those assigned scripted button...please enlight us if thats possible to do...Many many thanks!!
@@ExcelGoogleSheets i would appreciate your guidance on this particular case, i'm so dying to resolve this issue, appreciate your replies, many many many thanks, im from Indonesia btw, good to connect and found this channel, much oblique!!
I love this video which is exactly an aid to my task, but i'm having some issue with the submit button. with this error message "TypeError: Cannot read properties of null (reading 'appendRow')" . Please i need your help. Thank you Sir
aht I just need, now i need to figure out how to make a data entry for intructors scheduling, and that will be anble to tell me which date and time my instructor is available to assign the class to him/her
This shows data entry form for text. How can we use the same form to include uploading one or more documents or photos? More importantly, how can all entries go through an approval by a manager before it can be officially part of the database?
Google Sheets Web App th-cam.com/play/PLv9Pf9aNgemt82hBENyneRyHnD-zORB3l.html
Google Sheets Sidebar Userform th-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
Google Sheets CRUD Userform th-cam.com/play/PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx.html
please send the code
sir i got right my first level of data thank you very much appreciated your help
unction myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const formWS = ss.getSheetByName("Form")
const settingsWS = ss.getSheetByName("Settings")
const dataWS = ss.getSheetByName("Data")
const idValue = formWS.getRange("B3").getValue()
const fieldRange = ["B8","B10","B12","B14","G8","G10","G12","G14"]
const fieldvalues = fieldRange.map(f => formWS.getRange(f).getValue())
const nextID = SettingsWS.getRange("A2").getValue()
fieldvalues.unshift(nextID)
console.log(fieldvalues)
}
2:41:47 PM Notice Execution started
12:41:50 PM Error
ReferenceError: SettingsWS is not defined
myFunction @ Code.gs:14
I copied everything in your script but run button is not available and it says error.
fieldRange.map is not a function
This is fantastic. I followed this in a real use case and it totally works! My next step is dealing with a second data list that is tied to the first by that ID with a 1-many relationship. Excited to see where I can take this!
Thank you thank you thank you!! I've been watching your video for at least two weeks because I work long hours and I watched it over and over and I finally paid the patreon membership and was able to complete our new form. I was doing something wrong some how but I just copied and pasted everything in the end and modified it to our form's needs. lol. Keep up the great work!! Looking forward to your other videos.
Thanks for supporting on Patreon!
I love when you "make mistakes" since we then get to see how you fix them! I have been able to automate quite a few spreadsheets and workbooks thanks to your lessons!!!!
Always a pleasure to watch your tut´s. Espacially the ones from Apps Script. Thank you, sir!
Thank You!
@@ExcelGoogleSheets ยินดีต้อนรับยังคง
This was great! It took me all day to do but I got it done and my form works!!
Thank you. I used this to right my first Script. Works great
Excellent!
Just extraordinary explanation. You explained everything according to necessity. First problem then the solution.❤❤❤
Thanks
Thank You!
I am really grateful for this video . I could apply all these tips in a new form in my job. Thank you
👍
This will help small-time businesses that don't have enough budget for a system.
Or people like me in a large business looking to simplify their job. Honestly at this point though I’ve gotten good enough at manual entry it might not be too much faster
Dude I had to stop watching, you're really good with how you explain!
I am so glad I found your video, very informative easy to understand and the logic you present behind the scripting very helpful. I watched the video to do this following it step by step and came out with a better understanding that when I started,. I have subscribed to your videos and am excited to learn more
thank you so much for this video in particular. With this and a few rabbit hole references to chase down the other functionality I need, I've been able to get a new RMA system up and running in three days with nothing but a spreadsheet and some fancy JavaScript, even importing the old data from the previous system on a dying XP box. I appreciate this.
👍
This is an excellent video! Thank you for providing it. I have a question. Is there a way include instructions to clear the fields upon saving? I've been playing with it to see if I can make it work and it's just not working.
Your videos are the greatest, always so insightful. Thank you
Thanks!
Your series of videos are extremely useful. They are so simple and properly explained that the tips given by you can be used at other places.
Questions:
How can the user jump from one field to next without much hastle or using tab.
How do you ensure that entry to a field is completed before a script is executed.
@How can the user jump from one field to next without much hastle or using tab.
Don't really have a good solution for this.
@How do you ensure that entry to a field is completed before a script is executed.
You would need to validate everything in the script itself before actually making any changes to the spreadsheet.
@@ExcelGoogleSheets what if you protect unused cells and disable clicking on same, i think this will jump protected cells and go directly to enabled once
I learned so much from you. Thank you. I am building a production output database and this is exactly what i need. Where can i reach this file to make a copy?
Thank you very much, I'm trying to incorporate this form in my spreadsheet, but I don't have the ID column in the data, can I use a second column with formula to create some sort of id for each row or maybe I can use the same search column as ID for the saveRecord? thank you
Such an amazing video. Thanks a lot for sharing your knowledge!
I am doing something similar, but in a much more complicated way, with arrays. This is simple and clean code, thank you very much.
:)
You are doing very great work bro.
love from india.
i like your all videos.
Thank you 😀
Very informative, wish you included a wa of creating PDF's of the data recorded to serve as invoicing system.
Great video, as always.
I wish we had the same functionality as Excel, when you protect one cell and set it in a way that the user cannot select it. This way the user could navigate only through the fields of the form. I've built great forms in Excel like this. When I try to do the same in Google Sheets, it just looks terrible...
same here too
This tutorial was super useful. Thank you very much for sharing. Is there any chance that you could advise how can we adjust clearing content while leaving formulas in certain cells? It clears everything including formulas..
This was AWESOME!!!!! THANK YOU SO MUCH!!! You have made my day!!!!
Great instructions. I have a question..... How do you amend the Search Function to allow for multiple responses, getting the Form to move from each response to the next? I am trying to use this for a database, for inventory, where it is possible that the search (text box with 80 characters) will return multiple records. I need a way to move from one response to the next.
This was an amazing video, if i could like this a thousand times i definitely would
:)
Hi there! Thanks for the useful videos! Question: what if we have more than 4 cells to read? A column of 50 for example. And we need to store that data in a row (transpose)?
You are my hero in google scripts
:)
Hi, thank you for all your tutorials; I´ve started coding with your help, still a beginner but I´m so thankful for all your guidance. I have a question: will this entry form be something that any user that the google sheets has been shared with can use?
Also, do you have any tutorials on how to automate getting the last row from a database to fill a google docs?
Thank you for your insights. And I would like to know how this APP Script can work on Mobile phone or Tablets?
Hi, thanks for the wonderful information you share here, I follow you step by step on a google sheet that I want to key in and store data on. and the when I reach to the console.log part, The Info there show [Function], what do this mean?
Thank you!! But I'm trying to figure out when editing existing data, if I want to let if(id == "A:A") (which means compare to all the values in A:A, if not then create a new record), what should I do?
Thank you! It is a great lesson for me, But this is enter data one by one, can it enter several duplicate data one time?
Hi, thank you for the helpful video! I was wondering if you could help me figure out how I can automatically add a timestamp as well to the Data sheet when saving a record.
Hi .... 🙋♂🙋♂ thank you for sharing the valuable Edu. video for us. I learn lots from that video. but I have one quotation, if the file share with another person and i want to only he update the data but not delete or edit after save the changes then how I can do, its possible ??
can we add some options of manual entries also please? For example id my marketing team member has to fill this they would have the fixed product listed by=ut i they have to mentioned but it one has to fill prices manually it was sold for for multiple product. Please if you could help on this?
Hello! Excellent video! I would like to set some required fields, how would you do that? Thank you very much!
Quick question with this, could it be configured to reject any of the inputs(not add the entry to the data set) if they already exist in the data set, when you click the save button? Additional question: Can this be done across workbooks or just across sheets in the workbook where the form exists?
thank for the video, but,..in 30:29 when i use "data.filter " then message error : "typeError : data.filer is not function", please help me,..thank's
Thank you so much for your great tutorials. May I know how can my data entry be converted to all capitals when it enters the database? Thanks in advance, keep up the great work.
This is wonderful learning from the video. Is it possible to download the script and try myself?
Iam Almost a Layman in scripts, Buy Enjoyed what we can do with them , THANKS A LOT !
Aprendo muito com seus vídeos, muito obrigado por compartilhar seus conhecimentos de forma brilhante e didática. Continue assim.
Thanks so much for this great tutorial. I have a data entry form with several cells. When I use the cost getRange = [""], the command enters the next line due to the several cells in the data entry form. Which command can I use to make the command run as one line without breaking? Thanks
Is there a way to add data to a spcific column using a pull down to pick what column it goes in?
WOW! This is fantastic. Thank you!
This video was so awesome, thank you so much! I took this idea and ran with it for something at work. My issue now is whenever I share with another person they get this scary message that they are sharing data with an unverified app. Do I need to verify this application with google or is there a way for me to modify something so that I can easily share this with a few other coworkers.
Love your videos. Great help. I did the entries and all worked as planned. Then something went wrong. I add some setFormula's to a different function and now the saveRecords function will not append my entries to my data. I checked and triple check the entries for the function saveRecords. Would you be able to help?
Hi! Is there a way to prevent submission if certain fields are not filled out? Or if it's empty?
Hi, Thanks for doing this.
Can this Also be Done On Google Forms via App Scripts?
No, but you can do a web app.
Hello ,Thanks for these amazing videos prepared to foster good and clearer ways to learn app script.
I can't get the OnEdit function to work. It issued out the following: TypeError: Cannot read property 'range' of undefined
Is there any change in the appscript related to the class (range)?
send him the script so he can check it for you!
OnEdit should be onEdit
Also make sure to not manually run this function, otherwise you will get that error.
The function should run when you make changes to the spreadsheet automatically.
@@ExcelGoogleSheets Thanks for answering asap. This is the same line you have shown:
I have put a button to execute the search the function(busquedautil())anyway...
function onEdit(e) {
console.log(e.range.getA1Notation())
console.log(e.source.getSheetName())
if(e.range.getA1Notation() !== 'C13') return
if(e.source.getSheetName() !=="DATOSNOTAS") return
busquedautil()
}
This was really helpful! Thank you!
Great video again. Anyway to do a form approval process from sheet?
thank you so much for saving our time. i'm having an issue while writing onEdit. it says e is undefined. pl guide
Thanks so much for your videos, I've seen them before and they are very concise. I've subscribed. I built this as described and modified to my needs works great!. One question I would like to have the deleted items go to a record ("deleted"). tab. I would like when the delete button is activated to have "CopyTo" function activate and send a copy to the "deleted" tab. What script do I need to add to the deleteRecord function to make this possible.
Until your video I didn't know what a script was. Fantastic and easy to understand. But, like always a question. My data sheet contains 8 columns including currency, numbers and dates. When I feed info in, all are ok, numbers with 2 decimal points, amounts with $ symbol and correct decimal places however, my dates show on my data sheet as "month/day/year". No matter what I format or what I enter the format is always the same. Help!
great tutorial! is there any way to do like go to the next textbox upon entering the data like tab index? so that no need to click each row just to enter the data. thanks
You'll need to use a Web App form instead.
@@ExcelGoogleSheets i actually did that on my previous webapp which i learned also from your channel. its just so happened that i have a simple spreadsheet form that i want to apply that tab index upon hitting the enter and will automatically go to the specific cell. Im actually trying to figure it out using your onEdit tutorial. hoping that i can find the answer on that video. Thanks anyway man you are a legend!
@@ExcelGoogleSheets I figured it out using your onEdit tutorial. Hahaha it might not be as clean as the webapp but i can deal with it.
My advice would be just place all input boxes on top of each other in the next row, that way when you hit enter it will go to the next box.
@@ExcelGoogleSheets yeah or I should have the same format all the way down so the code would be as is and wont give an error.. thanks man! Im actually trying my best understand everything specially about the webapp. Please do not stop uploading this kind of tutorials especially in appscript and webapp. 😅You are the best man! if its not too much for you kindly do an approval system webapp😅
This was awesome! Using it to create an inventory of my home bar and it is working great! Quick question: I have one dropdown on the Form tab called "Bottle Status" with options of Unopened, Opened, and Empty. Is there a way when "Empty" is selected the data can be moved from the Inventory tab to a "Past Bottles" tab? Trying to keep track of all of my previous bottles, but don't want them on my Inventory tab once I finish them.
I found a couple of examples, but they use onEdit which I cannot figure out how to do since this script already uses onEdit. Hope that makes sense!
Your tutorial is awesome! I follow this guide and adjusted according to my needs. It works like a charm. For the onEdit(), I have to make it an installable trigger instead of simple trigger in order to make openbyID() to work. BTW, how can 2 or 3 users use the same Page to enter record at the same time? Make 3 duplicated data entry pages and each of them use different page? will it work? Thank you so much for your great work
Thanks for making this great tutorial. Just wondering if I can get the link to scripts please?
I love your tutorials! In trying to modify an existing dataset, I have run into a bit of an issue I don't understand.
The Form is simple
ID
Search
Serial Number Brand
Location Model
Version Asset Tag
Comment
In the Inventory data their is a field Brand that is using a RegexMatch to autofill this field.
If I search to find a record, then modify say location and save, the form is filling in this value from the querried valued.
How can I skip that field on the saveRecord function.
The Brand is determined by the starting 3 characters of the Serial Number.
Hi, if you read my comments, can you help me how I can create the "ID" like UPR 0001 (then become UPR 0002, UPR 0003 and so on) instead of just single number?
Thank you so very much in advance for help and this knowledge.
Is there a way through which multiple people can fill the form at the same time?
Exactly what I was looking for, Thank you man
:)
i have a question regarding a file like this is their any way we can have pictures be put as well like you have names and ages states is their a way to have a picture for their like profile aswell?
Thank you for sharing. I learned so much🥰.
:)
Do you have a video where you discuss the pros and cons of using a Userform vs making a data entry form as in this video?
I don't think I do.
Question for you, we're looking to keep track of our members and this script is fantastic, however we were also wondering if there was a way to create a another tab like data named "LOGS" and individually track changes to data inputs. In example, the most current information for that member would be displayed in "DATA", however the overall slew of changes to all members would be listed line by line in "LOGS" so we can see what was done to it?
The short answer is, it's possible.
@@ExcelGoogleSheets could u make a vid on it?
This is very educative, thanks for the tutorial. How can one get the scripts/codes?
SIR YOUR VIDEO SO MUCH HELPFUL FOR ME WHO DOESNT KNOW ANYTHING REGARDING THIS..TQ SO MUCH..BUT CAN I ASK U SIR?..HOW CAN WE USE SAME SCRIPT AND SAME SHEET BUT ACCESSED BY MULTIPLE USER IN A TIME..?
What would the code be if I had some cells in the form which auto updated ( such as today() ) and I did not want them to get erased when I press the new Record button ? However they all still get transferred/ saved to the Data sheet .
This is fantastic. only had a few snags... 1) why does the phone number on my sheet, get saved on the data form without the leading 0 ? I have set the format of all the relevant cells to be plane text but it still just drops the 0 at the beginning of the phone number?
2) I added the search column, and it broke my saveRecord button?? it ran through the motions and even updated the id cell, but no data was saved? I removed the coloumn and it still didnt fix it, I had to do a rollback?
Have you tried to make both the entry cell and the data column formatted as plain text?
When you have errors, check under Executions panel and see if you have hailed scripts. It should give you some clue where to look.
Your videos are very educational and have raised my awareness and skills tremendously! Thank you for that.
My question: do you also share the code that you create during the video? Where can we find your sample code? It would help in learning even more.
on patreon
@@ExcelGoogleSheets I’m not familiar with Patreon. Do you have a link?
@@HaroldHorsman I see the link in the description above, there also is a link for some code snippets.
I too would like a copy of the code so I can follow along with the video and once I have a working example I can edit to fit my needs.
I don’t see a copy of the code in any of the links I clicked on.
Is there another way to get it besides watching the video … stopping every few mins and writing it down?
bare and in the mouth! check in the video itself
Wow it's Great Tutorial. Thank You So much
Hello I built a Data Form following your videos, I'm not sure if it was this one or a search one, but you had a code within it - =QUERY(Data!A1:J,"SELECT * WHERE LOWER(E)= LOWER("""&B2&""")", 1) - what do I need to change if the column holds numerical answers as well as alphabetical, where the answer is definitely a numerical one and not a alphabetical one.
favourtie TV series etc, 24 not Twenty Four :D
Hi. Tq for the tutorial. I just have a tiny problem with the script. The error I got was on the onEdit script. It said that the e.range was not defined
Dude Just have one last question - I had put up a formula which sums a total amount when I click on the submit button it clears the formula, Can you please suggest a solution for this.
Thank you for the video! May I ask how to filter if data column 2 is date? I have tried const recordFound = data.filter(r => r[1] == searchValue), but [ ] nothing can be found in the Logger.log(searchValue)
try
data.filter(r => r[1].getTime() == searchValue.getTime())
Really good thank you. Is there a place where we can copy the code to try ourselves? thx
I have a question and can't find a video. if a check box in column I if false return values from column A:D
Great tutorials, thank you....
Can you please make a video on How script and set triggers work in a copied spreadsheet
are you able to protect the fields?
Hi pls advise
I follow the steps up till “search”
got 2 formula running failed
1) if(recordsFound.length === 0) return
2) idCell.setValue(recordsFound[0][0])
error msg
1) the “.” Problem and “===“
2) is the “[“ “]” problem and “(“ “)”
I am stuck at this point not sure how to do. Kindly advise
Thanks
what if the form sheet is separated in another spreadsheet, do you have a video about that? I'm very grateful if you have
Lovely tutorial. I have a question. Can you help me capture user signature with appscript?
th-cam.com/video/SB1n7gxM73w/w-d-xo.html
Hi, great video, was very helpful! I created my own database using this as an example but i am however, having an issue with saving a field "Mobile Number" as the number being saved is loosing the leading 0. Doesn't matter if the field on the "form" page or the working spreadsheet is formatted as text. It appears the process of extracting the data from the "form" page is treating it as numerical and thus the leading 0 is being removed. Any suggestions on how I can correct this?
That may have to do with the formatting of the field. If it is a generic number field "001" will display as "1", for example
Thank you very much! Your tutorials are really informative and clear but I have a questions:
Can I use Google spreadsheet as cloud date base to be accessed from Android Application?
If this is possible, please guide me how to do it. Thanks
Can you: yes
Should you: NO
@@ExcelGoogleSheets
Thank you for your answer.
Is there any free alternative better than Google spreadsheet?
Try Firebase free version.
Hi. i have been making my version of data entry form by looking at your video. i had a few questions. One, is it possible to do what we are doing when the data in the cell is a photo. (This is on the form sheet) Second, the data sheet, where the data will be saved, is it possible to have additional columns after the data columns? These additional columns will have formula stored in them.
1) no
2) yes
anyway to get a copy of just the coding part so I can see it by it's self?
wanted to know badly weather you can actually place the Data Tab in the same tab in Form? just right underneath those assigned scripted button...please enlight us if thats possible to do...Many many thanks!!
Yes, of course you can.
@@ExcelGoogleSheets i would appreciate your guidance on this particular case, i'm so dying to resolve this issue, appreciate your replies, many many many thanks, im from Indonesia btw, good to connect and found this channel, much oblique!!
I don't remember the code I wrote here, but I don't think you have to change anything other than pointing to the right sheet for your data.
@@ExcelGoogleSheets thankyou for the feedback, im just using 1 sheet for my form, while your form using 3 sheet for setting and data, so frustrated!
Verry helpfull for beginer like me, thanks for share
😊
Excellent and helpful video. Thanks!
Very helpful! Thank you!!
I love this video which is exactly an aid to my task, but i'm having some issue with the submit button. with this error message "TypeError: Cannot read properties of null (reading 'appendRow')"
. Please i need your help. Thank you Sir
How do we add a confirmation message/step prior to saving and deleting a record? Thanks.
th-cam.com/video/SR6BfgoFC-Q/w-d-xo.html
Thank you, this was helpful.
aht I just need, now i need to figure out how to make a data entry for intructors scheduling, and that will be anble to tell me which date and time my instructor is available to assign the class to him/her
:)
This shows data entry form for text. How can we use the same form to include uploading one or more documents or photos? More importantly, how can all entries go through an approval by a manager before it can be officially part of the database?