This is awesome, using this script to build a fantasy baseball trade app. Clicking the button submits the trade to manager then that manager can accept the trade. Amazing
That is a fantastic video. I learned a lot from it. Thank you. I have a question though, some of the data I want to save is automatically generated by other calculations in other cells. How can I adjust my code to erase those cells (that causes the calculations)?
The current tutorial only retrieves the values but not the formulas from the data. So this shouldn't be an issue in your scenario. You can use the getRange() method on the calculation range and use either the clear() or clearContent() methods: developers.google.com/apps-script/reference/spreadsheet/range#clearoptions
Thanks for the video! Noob asking: Instead of validating if all cells are empty (line 11), I simply want to validate if the last cell in my 12 cell array is blank. Please aid.
Hi ash let's say that your array is called "cells". If you know the length of the array then you could say: if(cells[12] === ""){ // Do something. } If you don't know the length of the last item in the array. if(cells[cells.length -1] === ""){ // Do something }
Thank you for this excellent content, this is very helpful! When I enter in 0 in distance or charge used I get an error even with validation, is it the code? Also, the date and time stamp is Australia’s and I am in North America, how do you change that? Thanks!
You can change the locale from File > Settings : ruclips.net/video/0qntbVHJHuI/видео.html The validation error should be a result of the conditional formatting. Righ-click the cell an select the conditional formatting. You might also want to update the script file to head to Project Settings and update the locale there: ruclips.net/video/nKIJchyAOus/видео.html
HI. THANK YOU FOR YOUR GREAT VIDEO. I have a question, how to activate authorization required because there are no warning sign if i submit the form that i create. Only me can fill the form because i create apps script using my email. Btw sorry for my broken english and i hope u can understand.
In this tutorial, your app can be used by other editors. Later in this series, I show you how editors who don't have access to the cells can input data.
Good morning. What script (with button) can I use, IF I have 3 buttons, BREAKFAST, LUNCH, DINNER each button, once pressed will add a data log on another google sheet?
This is definitely achievable. Check out my Hire Me page to make an enquiry on building custom code for your project. yagisanatode.com/google-workspace-developer/
This is an amazing Video series!! For some reason my Input data range transfers all but one of my cells information. It will also not clear the data on my input sheet for that cell only. I have checked to make sure all the cells are included in the Data Range.
Fantastic. This was so helpful. Is it possible to add the data to specific tabs/Sheets based on conditions? for example if one of the values was X, then it would go to Sheet X, but if the value was Y then the data would go to Sheet Y
Absolutely, you could use a conditional statement that would allow you to determine what sheet tab you want to add your data to. Check our JavaScript 'if'-statements and 'swtich'-statements.
Good morning, its me again. what if, the data log, is to be put on a descending manner on the sheet? instead of ascending? meaning it will only put data log on the row directly beneath the tabloe header?
Hi. Does google sheets allow a sheet to be unaccessible ? For example, I don't want any other person to access the code sheet and modify the code. Thanks
Hi TessView. No, not unless you published the script as an editor-addon internally within your Google Workspace Domain, or externally in the Google Workspace Marketplace. However, you could create a web app with apps script to do the same thing. Check our this tutorial for an introductory guide: yagisanatode.com/2020/11/25/creating-an-embedded-interactive-chain-story-app-with-google-apps-script-and-google-sheets/
@@yagisanatode 8 months later but I can't find Part 5 to the series that answers this question? I'd love to find out as I'm in the middle of a project!
thank you, my question is how can I change from column B to any other column and add more columns in the sheet input data? how can append the data to follow columns instead of rows?
You can use the getRange() method to select you desired range and then use the setValues() method on that range. Check out the links in the video description for more information on this.
i have a formula in one of my cell while collecting data but when I run the scripts it deletes the formula and formatting? your help is much appreciated.
Yes. You can call the Sheet class (e.g. getSheetByName()) on multiple sheets. Just like we did when getting the input data from one sheet and then setting it in another.
@@yagisanatode i tried adding ==0 to anyEmtyCell. But right now i just thought maybe i should try !==0?? Just need help sir, im still trying to learn Javascripting 😅
@@GoldnRule You can do this in the sheet with Data Validation > Criteria > Is Equal to and then add 0. You can learn more about Google Sheets Data Validation in this series: ruclips.net/p/PLSDEbLgMgqvoVQA73Erj_Gky2K11v-CHh
Is there a way to create a self updating input sheet? To account for unknown variables, and when they are found, to incorporate them into the data log. My current thought is to have a checkbox that, if it is set to TRUE, it will then prompt the user upon hitting submit to type in the relevant text. It then still updates the primary datasheet with the information inputted as is, but then updates the namedrange list in order to incorporate the new variable into the relevant dropdown menu.
Unfortunately, not with the mobile app. You can, however, user a checkbox to do the same. Here are two examples of checkboxes and the onEdit() simple trigger in action: yagisanatode.com/google-apps-script-create-a-select-all-checkbox-to-tick-checkboxes-in-any-desired-range-in-google-sheets/ yagisanatode.com/add-the-editors-email-when-they-tick-the-check-box-in-google-sheets-with-apps-script/
You certainly could. I have built a number of sheets that provide a data entry point using just a sheet tab. In other instances, I use dialogues and sidebar, forms or AppSheet.
@@yagisanatode Hi Scott, I did it! I create my first google sheet forms. I really like this! I am creating a form which has multiple entries at once, but when it tries to append the data, it just appended in one row. can you please help me with this? what code should I add to the script? thank you!
Hey there, you can't use buttons and AppScript-generated menu items on the mobile app, unforunately. One workaround is to trigger your script from a checkbox.
We're starting from scratch here with this starter sheet. So there should not be any script here except for the name of the first function. I've added a comment to the script to explain this for future viewers but you will also not this is at 01:34 .
This is awesome, using this script to build a fantasy baseball trade app. Clicking the button submits the trade to manager then that manager can accept the trade. Amazing
That is an awesome use case. You would be surprised about how many fantasy sports enthusiasts use my code snippets. Always great folks.
Perfect!
But is there a way to have data transferred to a specific row/column instead of all below each other?
This pleasee. We need this option
Thank you! This video helped me a lot.
Awesome to hear!
Really great, thank you !
You're welcome
This was truly amazing! Thank you so much for this perfect tutorial!
Glad it was helpful!
That is a fantastic video. I learned a lot from it. Thank you.
I have a question though, some of the data I want to save is automatically generated by other calculations in other cells. How can I adjust my code to erase those cells (that causes the calculations)?
The current tutorial only retrieves the values but not the formulas from the data. So this shouldn't be an issue in your scenario.
You can use the getRange() method on the calculation range and use either the clear() or clearContent() methods: developers.google.com/apps-script/reference/spreadsheet/range#clearoptions
is there a way to not collect the date and time along with the email? I just want the inputs. thanks for the tutorial!
Just omit the date time inclusion.
Thanks for the video!
Noob asking: Instead of validating if all cells are empty (line 11), I simply want to validate if the last cell in my 12 cell array is blank. Please aid.
Hi ash let's say that your array is called "cells". If you know the length of the array then you could say:
if(cells[12] === ""){
// Do something.
}
If you don't know the length of the last item in the array.
if(cells[cells.length -1] === ""){
// Do something
}
Hi you are super! A question: on mobile the button doesn't work! How I can fix it? For example with a checkbox
Buttons unfortunately don't work on mobile. The common workaround, as you've discovered, is to use a checkbox that often programmatically resets.
Thank you for this excellent content, this is very helpful! When I enter in 0 in distance or charge used I get an error even with validation, is it the code? Also, the date and time stamp is Australia’s and I am in North America, how do you change that? Thanks!
You can change the locale from File > Settings : ruclips.net/video/0qntbVHJHuI/видео.html
The validation error should be a result of the conditional formatting. Righ-click the cell an select the conditional formatting.
You might also want to update the script file to head to Project Settings and update the locale there:
ruclips.net/video/nKIJchyAOus/видео.html
HI. THANK YOU FOR YOUR GREAT VIDEO.
I have a question, how to activate authorization required because there are no warning sign if i submit the form that i create. Only me can fill the form because i create apps script using my email.
Btw sorry for my broken english and i hope u can understand.
In this tutorial, your app can be used by other editors. Later in this series, I show you how editors who don't have access to the cells can input data.
Good morning. What script (with button) can I use, IF
I have 3 buttons, BREAKFAST, LUNCH, DINNER
each button, once pressed will add a data log on another google sheet?
This is definitely achievable. Check out my Hire Me page to make an enquiry on building custom code for your project. yagisanatode.com/google-workspace-developer/
This is an amazing Video series!! For some reason my Input data range transfers all but one of my cells information. It will also not clear the data on my input sheet for that cell only. I have checked to make sure all the cells are included in the Data Range.
Could it be something with this? const data = [...sourceVals];
Thanks for the kind words. Are you getting any errors in the event log?
Fantastic. This was so helpful. Is it possible to add the data to specific tabs/Sheets based on conditions? for example if one of the values was X, then it would go to Sheet X, but if the value was Y then the data would go to Sheet Y
Absolutely, you could use a conditional statement that would allow you to determine what sheet tab you want to add your data to. Check our JavaScript 'if'-statements and 'swtich'-statements.
Good morning, its me again. what if, the data log, is to be put on a descending manner on the sheet? instead of ascending? meaning it will only put data log on the row directly beneath the tabloe header?
Hi! This video should help answer your question: ruclips.net/video/6pugLx-ORU4/видео.html
Hi. Does google sheets allow a sheet to be unaccessible ? For example, I don't want any other person to access the code sheet and modify the code. Thanks
Hi TessView. No, not unless you published the script as an editor-addon internally within your Google Workspace Domain, or externally in the Google Workspace Marketplace. However, you could create a web app with apps script to do the same thing. Check our this tutorial for an introductory guide: yagisanatode.com/2020/11/25/creating-an-embedded-interactive-chain-story-app-with-google-apps-script-and-google-sheets/
Good Job, but does this apply to multiple users using the form at the same time or it's for a single user or one at a time?
Stay tuned. I'll be cover this in part 5 of the series when we migrate this over to a sidebar app.
@@yagisanatode 8 months later but I can't find Part 5 to the series that answers this question? I'd love to find out as I'm in the middle of a project!
@@lemonyfresh8595 Hi Lemony Fresh you can find it in the playlist here: ruclips.net/video/JyZzWLVpmmU/видео.html
Thank you
You're welcome
thank you, my question is how can I change from column B to any other column and add more columns in the sheet input data?
how can append the data to follow columns instead of rows?
You can use the getRange() method to select you desired range and then use the setValues() method on that range. Check out the links in the video description for more information on this.
i have a formula in one of my cell while collecting data but when I run the scripts it deletes the formula and formatting?
your help is much appreciated.
Take a look at the range that clear is being applied to. What would be a better range that would exclude the cell containing the formula?
Thanks, is there a way that the data log can go to a different spreadsheet and not just the same spreadsheet?
Yes. You can call the Sheet class (e.g. getSheetByName()) on multiple sheets. Just like we did when getting the input data from one sheet and then setting it in another.
Amazing Video
but can we upload a document in input sheet and save it
Do you mean uploading the contents of a doc and displaying in the Sheet?
Very Great tutorial!
Can you help us in validation of cells to accept 0(zero) as input please 😁
What have you tried so far?
@@yagisanatode i tried adding ==0 to anyEmtyCell. But right now i just thought maybe i should try !==0??
Just need help sir, im still trying to learn Javascripting 😅
@@GoldnRule You can do this in the sheet with Data Validation > Criteria > Is Equal to and then add 0. You can learn more about Google Sheets Data Validation in this series: ruclips.net/p/PLSDEbLgMgqvoVQA73Erj_Gky2K11v-CHh
Is there a way to create a self updating input sheet? To account for unknown variables, and when they are found, to incorporate them into the data log. My current thought is to have a checkbox that, if it is set to TRUE, it will then prompt the user upon hitting submit to type in the relevant text. It then still updates the primary datasheet with the information inputted as is, but then updates the namedrange list in order to incorporate the new variable into the relevant dropdown menu.
Yes you can validate the sheet this way. Check out the next tutorial in the series: ruclips.net/video/JyZzWLVpmmU/видео.html
Hello!! Is there a way that the button also works on mobile version?
Unfortunately, not with the mobile app. You can, however, user a checkbox to do the same.
Here are two examples of checkboxes and the onEdit() simple trigger in action:
yagisanatode.com/google-apps-script-create-a-select-all-checkbox-to-tick-checkboxes-in-any-desired-range-in-google-sheets/
yagisanatode.com/add-the-editors-email-when-they-tick-the-check-box-in-google-sheets-with-apps-script/
hello so i did the some thing for point checkbox and i have the last calom have the total point i need when i do submit i don't want the sum to delete
Don't clear the range that the sum is on. It will stay in the cell.
This what i did but i want the sum to be transferred with rest of the numbers.? Can i do something
@@samer_Alkhafaji Yes, so when you clear the sheet. Create a new range that is one row less than the current one.
Can you submit like a weekly data entry (multiple rows) from this?
You certainly could. I have built a number of sheets that provide a data entry point using just a sheet tab. In other instances, I use dialogues and sidebar, forms or AppSheet.
@@yagisanatode I'm very new to AppSheet do you have any reference I can read?
@@BagasKJ Nothing specifically to what you are looking for but the links in the description should point you in the right direction.
@@yagisanatode Hi Scott, I did it! I create my first google sheet forms. I really like this! I am creating a form which has multiple entries at once, but when it tries to append the data, it just appended in one row. can you please help me with this? what code should I add to the script? thank you!
I asked chatgpt now it works lmao, thank you btw, Scott!
I keep getting an error stating "ReferenceError: SpeadsheetApp is not defined" but I have my code exactly how you have it.
Hi, Make sure you add an 'r' to 'SpreadsheetApp' .
Is there a way if I add another data it will be inserted above the old data?
Yes, there is. Check out this video for a guide: ruclips.net/video/6pugLx-ORU4/видео.html
can you do a tutorial to edit a row in google sheet? thanks
Maybe, time permitted. What do you want to know?
how to clear without clearing the formula?
Don't capture the range with the formula.
Does this work for Mobile?
Hey there, you can't use buttons and AppScript-generated menu items on the mobile app, unforunately. One workaround is to trigger your script from a checkbox.
There's no script
what happened?
Nothing. This is where we start for this part of the series.
Pls reply where is script😢
The starter sheet for the tutorial is linked in the description.
Script is gone
We're starting from scratch here with this starter sheet. So there should not be any script here except for the name of the first function.
I've added a comment to the script to explain this for future viewers but you will also not this is at 01:34 .