Відео

It is possible to do custom things like you're mentioning, but requires more complexity and understanding of how things work. There are ways to reset filters to include new data, and ways to set it up with conditional formatting and data validation, and even merge cells if needed, it's just more complex and finnicky than could be quickly explained in full. A quick overview of the approach however is as follows: Filter: 1) Remove old filter. (sheet.removeFilter() ) 2) Select range containing old & new data (sheet.getRange() -- make sure to include header or title row) 3) Create filter (sheet.getRange().createFilter() ) Conditional Formatting & Data validation Typically the best approach here is to have the conditionally formatting and data validation run down to the end of the tab. If that is not possible, then if you can always have it applied to at least one row after the end of the data, then to add new data, if you can insert rows, it will keep any conditional formatting & data validation already applied to the range. If neither of these is possible, then you may need to actually build & apply conditional formatting & data validation in the script (which is a whole tutorial in and of itself). Merged cells: Merged cells can be more challenging to deal with, as if cells are already merged, you will run into errors if you try to merge in a way that doesn't work. If you are just doing a simple merge (e.g. merge across), you can select the range in the script and then do .mergeAcross()

@@SheetsNinjatysm

@@SheetsNinja Can I have a button that adds a row and prompts manual info into the new row every time, to the same table?

A script that is meant to run from a trigger (e.g. onEdit or onFormSubmit), passes information about that event (aka form responses) through the parameter (in this case, "e"), and so the "e is undefined" when you authorize the script means it couldn't find any information about the event, which when you authorize it, is fine.

Glad to hear that

For sure! Instead of the line with .appendRow(), we would do this: targetSheet.getRange(targetSheet.getLastRow()+1,3,1,data[0].length).setValues(data);

There is a similar feature using VBA code: answers.microsoft.com/en-us/msoffice/forum/all/move-a-row-based-on-drop-down-list-to-another/5b0a19e8-e753-4538-ae15-e6b3d5e349b6

Awesome, so glad this was helpful!

Unfortunately that is a restricted scope, and the only way to get around it is using a simple onEdit trigger and have each person using the sheet to authorize the script.

So before your "price" line of code, add this code in: // Format the price to USD using the locale, style, and currency. let USDollar = new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD', }); then change your script line to: <strong>Price</strong>: ${USDollar.format(data[2])}<br />

Glad I could help!

@@SheetsNinja yup. Just finished setting it up. Now I gotta xfer all the data

I've done that before. Typically I will use an if statement to determine when this happens. Let's say the parent dropdown is in column 2 (B) and the dependent dropdown in column 3 (C): if (col == 2 && val == '') { sheet.getRange(row,3).clearDataValidations() } That will reset the dependent data validation when you clear out the data in column 2.

So you have to use one trigger, and just use if statements to handle the distinctions between different tabs, or if the logic and columns are the same, the if statement can just make sure the script only runs for those two tabs.

Awesome, so glad this was helpful!

What issue are you having?

All you have to do is remove the line in the code that goes like this: data[0][0] = '';

It works. Initially had the same issue. Please make sure you clicked the "button" Save Allowlist in order to save your changes on the options form...

@@panagiotiskapsiotis2263 Yes, I installed and removed the extension multiple times and now it works.

Glad you figured it out! It was a little finnicky for me at times, but once you get it working, it's SO helpful and timesaving!

Glad to hear it!

That is the error you get when you authorize the script, does the script work now when you edit something in the Google Sheet?

So it's just copying the text. You can either copy the data validations as well (a little more complicated), or you can add the dropdowns and checkbox validation on the target sheets and then it will fill in that way.

So the big offender here is checkboxes, so typically I will set up dropdowns and other data validation, but then use the script to add checkboxes. You can do this by something like: targetSheet.getRange(targetSheet.getLastRow(),8).insertCheckboxes() That's what I would use after adding the new row of data.

Yes, you would need to count the hidden columns as well.

If the Google Sheet registers a cell update, it will trigger the last updated timestamp functionality.

@@SheetsNinja i mean if i uncheck the checkbox, the cell for date/timestamp should be cleared. how to code for that?

@@JosephJericoMarino So you'll just want to add a new section in the script that looks something like this (assuming column 8 is where the checkbox is that you want to use to clear the timestamp and column 12 is where the timestamp goes): if (col == 8 && val == false) { let sheet = e.source.getActiveSheet(); sheet.getRange(row,12).clearContent() } And I would drop this either before or after the current if statement (remember that an if statement starts and ends with a curly bracket, so make sure to keep this out of the current if statement).

How to add last user updated? I want to track the user as well

The users are member of my google workspace who authorized to edit the file.

If it's a shared sheet, then either everyone using the sheet has to authorize the script (and this will be necessary if you're using macros or a script triggered from a button or a menu). If you're using something like an onEdit trigger, you can have one user authorize the script if you use an installed trigger (add it manually) versus using the onEdit function name to use the simple trigger functionality.

Yes, you have to do .getRichTextValues() and .setRichTextValues() to copy hyperlinked text. It can be a little more complicated to layer that in with the .getValues() and .setValues(), which is why I didn't cover it in this video.

So this is actually pretty simple, you can use an indirect formula to dynamically reference data within a tab. So let's say the tab names start on the tab in B5 on your sheet. In C5, I would a formula like this: =if(C5="","",indirect(C5&"!C4")) And then you can drag this formula down. I use the initial if statement to keep it from erroring out if you drag it past where you have tab names. And then what the indirect will do is take the tab names from C5 and make a cell reference something like =TabName!C4

Nevermind - figured it out. I had a syntax error in line 16 so project wasn't saving. Thank you again! This is so helpful.

I found the solution for this issue, by a another video you made earlier, is to make an array of forumlas in the first row, so it automatically applies it to the new row inserted

Awesome, glad you figured this out!

So it looks like you have a typo wherever you have .getColumn() in the script. Do you have something like this in the script? let range = e.range; let col = range.getColumn();

@@SheetsNinja Yes, these two lines are there. If you have time .. I want to share the file with you and see the problem .. give me your email Please

Thank you very much I figured out where the problem was. It works great 🤩👍👍👍

Awesome! So glad this was helpful!

I just made another video with a simpler version of this script that doesn't require authorization--meaning it will automatically work for anyone who opens the Google Sheet: ua-cam.com/video/i-qd8vXUoEA/v-deo.html

Awesome! Thank you!

I just made a new version of this video using an onEdit script, which makes this functionality available for mobile phones or tablets: ua-cam.com/video/K5uOSI5i8ME/v-deo.html

I'd hate to be your coworker at the moment! 😉

I don't think there's a way to reply to Fiverr messages without logging into Fiverr. You could potentially set up a way to forward the body of Fiverr emails but that still wouldn't give your team member to reply to the email directly

Awesome! So glad this was helpful!

Hey, so you can search for my video on importrange and that shows you how to take data from one Google Sheets and display it in another so you can combine or compare.

Hey, sorry, I'm not a bookkeeper so I don't have any specific for bookkeeping.

Awesome, so glad this was helpful!

You can use checkboxes to select multiple items or names in Google Forms.

So this script isn't meant to run from the script editor, the onEdit trigger runs whenever the Google Sheet is modified. So if you make an edit in the Google sheet, it should run successfully.

@@SheetsNinja Wow, thank you for the quick reply. When I go to the sheet and make an edit, the updated field remains blank. Would it make any difference if the sheet has multiple tabs, as mine does? Thank you again for this help. I really appreciate it.

I'm not sure what you mean by substituting or replacing the native function. You can use this method instead of the native sharing function, or you can use it alongside the native sharing. It does not disable the share button, and you can still manage access completely separately from this method. The script only adds or removes emails in the tab, so it won't affect anything if you have separately added other people to the Google Sheet.

@@SheetsNinja Ok, thanks. All clear, thank you very much for your work, the videos are very helpful!

If you want to show "Present" or "Absent" instead of a number, all you have to do is where it currently has a countifs to count if they show up on a day, you can put that into an if statement. E.g.: =IF(countifs('Form Responses 1'!$B:$B,$C7,'Form Responses 1'!$A:$A,">="&D$6,'Form Responses 1'!$A:$A,"<"&D$6+1)=1,"Present,"Absent") So basically if it finds that name on a certain day, it returns "Present", otherwise "Absent".

@@SheetsNinja I was trying to freeze up to column C for easier scrolling, but I continue to get error message saying merged cells. Could you please help. Thanks so much

@@helenat7468 This one is a little confusing, but you can't freeze columns or rows if you have any merged cells that merge across where you want to freeze. So for example, on your sheet, you have one or more cells that are merged starting in A, B, or C and going to D or beyond. You'll just have to look through and unmerge any rows that do that so you can freeze where you need to

Awesome! So glad this was helpful!

You can just type what you want in the htmlBody part of the sendEmail section, and don't need to include any of the template literals: ${}

All you need to do is put "col == 2" in the if statement. So something like this: if (col == 2) { sheet.getRange(row,5).set value(today); }

@@SheetsNinja sorry I'm terrible at all this, would I still need to specify that it's sheet1 somewhere? Or do I just replace the entire if statement with what you said

@@zackreynolds thank you that worked. I thought that might work before but I didn't do == in my troubleshooting I only did =

So in the example if statement, I exclude a column with "col != 12", so if you just replace that with "col == 2" then it will work the way you're hoping.

@@SheetsNinja thank you so much for the help!

Yes, unfortunately with this hack, it does mess it up if you add new rows in the sheet. The only way to compensate for this is going to a scripted method. Good news is I have a great tutorial on a couple ways to do that here: ua-cam.com/video/5Yysv-QouTQ/v-deo.html