Видео

The onEdit trigger doesn't pick up indirect changes like this. Probably the best would be to incorporate the last updated script on that other spreadsheet and then you can do an importrange directly from that cell.

@@SheetsNinja Thanks for the fast reply. The other spreadsheet I am importing from is not mine, and I only have 'View only' for it.

Awesome, so glad this was helpful!

The point of this video (and the @OnlyCurrentDoc method) is to simplify the authorization process in cases where you are only accessing one Google Sheet and no other or external services. Anytime you add scripts to a Google Sheet (or other Google product) and have a user run or try to run any function, it will ask for permissions as requested by the script. So if you are trying to access multiple Google Sheets, you do NOT want to add the @OnlyCurrentDoc, otherwise you will have an error.

So you can actually relink the Google Form back to your spreadsheet. I cover the linking here: ruclips.net/video/5hZ1i7PhPcM/видео.html The only difference is when it's already linked to a different spreadsheet, you'll need to click the 3 dots next to "View in Sheets" to change the response destination.

@@SheetsNinja Really appreciate your quick response. But I want to select specific tab in selected sheet sheet.

@@AnshitaChauhan-t9g In that case, yes, you would need to use a script like the one in the video to route new responses to that tab. In that case, you would add this script to whichever Google Sheet you end up connecting the form to, and then for the target tab, you would use something like: let targetSS = SpreadsheetApp.openById('SPREADSHEET_ID'); let targetSheet = targetSS.getSheetByName('Target Tab'); And then continue using "targetSheet" for the code to add the data.

@@SheetsNinja okay Sir! I'll just go through this video again and try n test. will get back. Thanks a lot.

So you can't include/move/add comments, but you can add notes. For example, if you want to add a note in column D on the new row, you can add this after the .appendRow(data) line: targetSheet.getRange(targetSheet.getLastRow(),4).setNote("My note here");

You can loop this but not to create a single map. What you would need to do in that case is use the actual Places API, or you can download the data and upload it to My Maps in Google Maps to create a map.

Never mind I found it why is my row moving to the bottom of the new tab. How can I make it move to the top like yours?

So if it's not moving to the bottom of the existing data and moving to the very bottom of the tab, it means you have checkboxes or a formula with "" or " " that is showing rows as not being blank. So you can remove the checkboxes and use the script to add them--e.g., if you need checkboxes in columns 4 & 6, after appending the data, you can add: targetSheet.getRange(targetSheet.getLastRow(),4).insertCheckboxes(); targetSheet.getRange(targetSheet.getLastRow(),6).insertCheckboxes(); And then everytime it adds a new row it will also add the checkboxes.

Glad this was helpful for you!

Google Charts can be finnicky at times, and if it gets unreasonable, I end up typing in the ranges to use. The one thing with this template that may be throwing that off is the title of each section is merged, so likely what is happening is that is throwing you off. So let's say you want to do income/expenses/net profit: You could put this in the data range cell C6:O6,C7:O7,C10:O10,C20:O20,C24:O24 Then underneath that, select "Vertically" under "Combine ranges", then "C6:O6" in X-axis, and then scroll tot he bottom and check "Switch rows/columns", "use column C as headers", and "use row 6 as labels", and then you can select the series.

So glad this was helpful! You can right-click on any single column or row number (e.g. the part where it says A,B,C etc. or 1,2,3, etc) and delete that single row or you can select multiple rows or columns and do the same thing. So for example if you want to delete all the rows after row 30, you can click on "31", scroll down, hold down Shift and then click on "1000", right-click and select "Delete 969 rows"

Is it a Google Sheet that you created awhile ago? It's possible if it's an older sheet that for some reason it's blocked for that reason. Otherwise if it's a new or newer sheet, it may just be a glitch. You can check this playlist for access to download different Google Tables templates: ruclips.net/video/fxuW2QO38qA/видео.html

@@SheetsNinja It's a new sheet. Must be a glitch of some kind. Not the end of the world, I can just edit other templates.

So with Google Apps Script, there isn't a way to upload files from C drive natively without user interaction. You would have to have some kind of service on your desktop that the script could access. If you're wanting to upload CSVs from your computer on a regular basis or with automation, what you'll probably want to do is get a python or other local script. Another option is download "Google Drive for Desktop" and then when you're on your computer, you can drop files right into a Google Drive folder shortcut on your desktop: www.google.com/drive/download/

This means somewhere you have a formula referring to another formula referring back to it. Whatever formula you're adding when you get this error, you'll want to check the cells referenced in the formula to see what cells they reference. Usually what it is in this kind of case is you need somewhere to actually set the initial inventory and then you can add/subtract from there.

@@SheetsNinja Okay I fixed that but now when I made the report, it's 0's all across. for some reason it's not pulling the data from the log

Oh, thank you! I do not know why it doesn't work in mobile! I just tried it myself and sure enough, it does not work on the mobile app at the moment! I'm assuming the current UI in the mobile app doesn't support the multi-select and that's what they will have to update. So maybe in the next Sheets app update?

I got it sheet.getRange('A1').setValue(timestamp);

You need to make sure the tab name matches between the Google Sheet and the script (sometimes a blank space at the beginning of end of a tab name can throw you off in the script)

Glad this was helpful!

Unfortunately, you can't do this with the non-scripted method. You would have to set up a helper tab for each sheet you want this to work on. The other options are setting up the scripted version of this: ruclips.net/video/5Yysv-QouTQ/видео.html Or a different version of this that does not require a dedicated helper tab: ruclips.net/video/rFiFxoO407M/видео.html

You bet!

Glad it was helpful!

You're very welcome!

Awesome, so glad this was helpful!

lifesaver, thank you so much!. Much simpler than other video, great video. Wish you had more subs.

Awesome, so glad this was helpful for you!

Awesome, so glad this was helpful for you!

So if the data is coming from another sheet, you would either have to not delete using the script, or you would have to use the script to go search the source sheet for the row and delete it there.

Awesome, so glad this was helpful for you!

@@SheetsNinja YW! ⭐️

Glad this was helpful for you!

Glad this video was helpful, and thanks for the idea, I'll keep it in mind.

Glad it was helpful!

You would want to split that into two "if" blocks... e.g. if (tabName == 'tab 1') { sheet.getRange(tab1Row,tab1Col).setValue(today); } if (tabName == 'tab 2') { sheet.getRange(tab2Row,tab2Col).setValue(today); }

Glad it was helpful!

Are you talking about smart chips? If so, Google has not updated apps script to be able to pull data from smart chips. Otherwise if you're talking about links that you added, you can switch out .getValues() for .getRichTextValues(), so something like this: **replace the .getValues() and .appendRow() lines with these: let data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getRichTextValues(); targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, data[0].length).setRichTextValues(data);

Yes, so you could set up another helper tab and do the same thing again. I walk through the logic (just sub in the new formula I use in this new video) in this older video on regular dependent dropdowns: ruclips.net/video/xzzDNDNkO6Y/видео.html

@@SheetsNinja I find that I'm struggling to get it to work with the columns in my second helper sheet

If you have not authorized the script, that would be the #1 reason why this wouldn't work. Other than that, what you would want to check is when you pull up the Script Editor, on the left-hand side, check the icon that looks like three lines and an arrow, labeled "Executions", and see if there are any executions happening for the script, and if they completed successfully or if there are errors.

@@SheetsNinja Got it thanks! I will look into those.

Hey there, from what it sounds like, you may not have restrictive enough if statements, or you may have two onEdit triggers. This can happen if you either add a trigger multiple times or your main script is onEdit(e) and then you also add a trigger for that function. If you're in the script editor, double-check your function name, and then click on the clock icon on the left-hand side, and see if there are any triggers listed. If there is any triggers listed and your function name in the script is onEdit, then you should remove any triggers tied to it. Otherwise, if your function name is something like checkMySheet(), then just ensure there is only one listed. If this isn't your issue, then my guess would be that your if conditions to determine where the row goes both allow the row to go through, so you need to make them restrictive enough that only one will be true... e.g.: // name sheet if (col == 12 && val != ''){ // rest of name sheet part of the function } // weekday sheet if (col == 16 && val != '') { // rest of the weekday target part of the function }

So if you have a lot of options, you could get that error because Google will automatically add more rows, but not always more columns. So in this case, you just need to add more columns to the right of your formulas in column A. If you select multiple columns (click on "B" for example, hold down shift and then click on "Z", then right-click and select "Insert __ Columns" (left or right), and do that until you have approx 101 more columns, then it should populate.

@@SheetsNinja YES that was exactly it. thank you!! not to be dramatic but this changed my life

Thank you! Cheers!

Add-ons can make your life easier if the standard configuration provides what you need. Usually it's the customization to fit your exact needs is where those fall short, or when the cost exceeds the value it provides. If it works for you, then awesome! Otherwise you may look for a compromise of using an add-on and customizing it in the Google Sheet, or building something from scratch like the solution I demo-ed.

Glad this was helpful!

Glad it was helpful!

I just added a new video on how to add dependent dropdowns for multi-select dropdown fields: ruclips.net/video/AFPasThu9sE/видео.html

So glad this was helpful!