10 ай бұрын
11 ай бұрын
Жыл бұрын
Жыл бұрын
Жыл бұрын
Пікірлер
@Urappscom Күн бұрын
Excellent! Thank you! Just one question if student cannot scan their QRcode because their phone battery died or forgot the printed version is there a way to manually add their attendance? Thanks again,
@HashAliNZ Күн бұрын
Good question. I didnt build that redundancy into this system. You can edit the spreadsheet data - just pop their name into the spreadsheet on new line and copy the timestamp down. But I'll work on updating this with an easier solution
@user-sq2ip3lj6i Күн бұрын
Wow. Super cool. 👍👍
@dunphyk Күн бұрын
Can you review Index-Match in GS Tables? Scenario: TableA contains two columns [States] and [Capitals] TableB contains the same columns TableB[States] is a drop down list, populated from TableA[States] Requirement: Populate TableB[Capitals], based on selection in TableB[States] I would like to use Index-Match, but I am getting an ambigous result. I am able to get the correct values for the MATCH function, and INDEX functions separately, but when I combine them into a single statement, I get the entire TableB[Capitals] column populated with the result for the first row of TableB[States], regardless of which states are selected in subsequent rows. The full statement I am using is this: =index(TableB[Capitals],(match(TableA[State],TableB[State],0))) Would you have any thoughts on this?
@MarkAnthonyTumbaga-mz4it 2 күн бұрын
Thank you so much sir kind you are so goodlooking on your videos 😅 i learned very fast thanks for your guide and detailed tutorial sir hash ali. More powerrr.❤❤
@supriyapol5336 2 күн бұрын
Hello Hash can you please help us how can we share 800 qr codes to each individuals?
@SCZiful 2 күн бұрын
Awesome, exactly what I needed and well explained, thanks
@yankcisse3527 3 күн бұрын
Thank you for all...
@aarongrubbs5668 4 күн бұрын
I really hope you're still answering comments on this video because I have a problem. I'm making an sheet that is basically calendar for my law school assignments. Across the horizontal axis at the top in line 1 are dates ("Mon 8/19" etc). Along the vertical axis in column A are the titles of my classes. In the boxes are the assignments with divided with borders every 5 lines. 4 of those lines are merged to form one large box to write what the assignment is and in the 5th line is a more specific "due:" message with the time of day the assignment is due. For example B2:B5 are merged and B6 shows the specific due time, and that's all for one class. Beneath that is another class, B7:B10 is the assignment and B11 is the due time. I don't have assignment due for every class every day so only some of the "super-boxes" (merged lines with the assignment and the fifth line with the due time) have any actual text in them. What I want is for the super boxes with actual text in them to turn slightly grey and the text therein to be struck through when today's date is after the date at the top of a given column. I used your formula exactly but weird things are happening. Today is currently Tuesday Aug. 20 (Tue 8/20). My column B is for Monday August 19 (Mon 8/19). In conditional formatting I put apply to range B2:B26. Format rules: Custom formula is: =and($B1<today(),$B1<>"") and the format style set to strike through and turn slightly grey. The problem is that rather it turning range B12:B16 grey and striking through the text in those boxes, the formatting only seems to apply to B2:B5, which is a completely empty range, but not B6, which is the final line in the "super box" and contains the text "Due:". This problem persists in every column. I can change the formatting to only apply to one "super box" at a time (e.g., B12:B16) and then the formatting works for B12:B15, but again as before it still doesn't work for the "Due:" line of the super box (B16 in this case). What is going wrong here?
@HashAliNZ 4 күн бұрын
Hey Aaron, here's the formula you need: =and(B$1<today(),B2<>"",not(B2="Due:")) And I've sent you a video explanation in your email inbox 👍🏼
@anhmong5119 5 күн бұрын
Hi, Thanks for your great content. How can create a QR code which is scanable by specify devices/ email?
@biswajitkarmakar9447 5 күн бұрын
Thank you. Your app video is awesome. But there was a problem that if 1 student scans many times in a day then how to stop it from duplicate? plz help me sir..
@dharmendersinghrajput8139 5 күн бұрын
How to format a table like for currency is in dollar i want yo change it to another currency
@kristenroberts962 6 күн бұрын
Super helpful .. thanks!
@naveed.1234 6 күн бұрын
sir I want that when I scan qr code there is no need to click again in web browser. Directly present attendance
@thrm1991 7 күн бұрын
Great video, really helpful
@thomassand7520 8 күн бұрын
I still is having problems with my simple calculation. But with you it looks so easy. 🙂
@HashAliNZ 8 күн бұрын
Copy and paste the formula and I can try figure out the issue
@thomassand7520 8 күн бұрын
@@HashAliNZ My google sheets is just in this case driving hours for July 2024. So a line per date in July 2024 (31 lines, as we have 31 days in July 2024). I have created the same formula as you have shown for each of the days (some days the total drive time is 0, because I was not working). BUT the issue is when Iwas to SUM up all 31 days to see how many hours I have been working totally per month. When I make the sumup I get the issue, and cannot make it work. I want to make the sum of F3 to F33. Like you in your video (Time stamp 3:45 in your video). Each cell from F3 to F33 (F coloum is the total hours) and is formatted as "Duration" and each cell has this formula [(=E3-D3+(D3>E3)]. D is start time and E is stop time. My sum formula is looking like this (=SUM(F3:F33), i have tried many different versions, but none has worked. Also "arrayformula" ect., but cannot make it work. In this case it come with a result 5,69 total hours, but it should be 148 hours with is the correct number. If you can tell me what my errror is, or how to solve this you will become my Goolge Sheets NINJA 🙂
@thomassand7520 8 күн бұрын
@@HashAliNZ In my sheet the column D is start time and E is ending time: Line 3 to 33 is all the 31 dates of the month (July 2024). Column F is the total daily hours where I have made the formula as per your video: =E3-D3+(D3>E3) My issue is that II am trying to calculate all the hours for the month, here my formula is per your video (timestamp 3:34) =SUM(F3:F33) I have tried many different versions (including arrayformula), but I will not be correct. The correct number is 148 hours, but in this case the result is 5.69. All the cells in between F3 and F33 is all formatted as “Duration” Do you need any other info? If you can solve this you will be my Google Sheet NINJA 🙂
@HashAliNZ 8 күн бұрын
Is the sum formatted as a duration too?
@emilygray9868 9 күн бұрын
Hey, for when you select current date, can you instead ask it to return certain drop downs?So could I chose which drop down I want and then it just return those? Thanks :)
@SCMC_johnuy 9 күн бұрын
What is the conditional formatting custom formula if I want to highlight cells expiring on CURRENT Month regardless on number of days left?
@emilygray9868 10 күн бұрын
Thanks for your videos, I am trying to have a static timestamp, but also with an onEdit that moves the rows, depending on their priority. Is this possible? I have timestamp in column E, dependant on column D having "Completed" then it moves to the bottom. Then anytime I change the date, it moves. I've tried your suggestions alongside: function onEdit(e) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName("To Do"); const range = sheet.getRange("A2:E1000"); range.sort([4, 1]) // ID } but it doesn't seem to like it. Thanks :)
@danielajibola5248 11 күн бұрын
Hello Hash, Can I please get a link to those photo data, so I can use to practices, Please?
@brainonfire2324 11 күн бұрын
Why is in my app the scan button located in Scan_ID box not Student_ID box? Please help
@visualkari6407 11 күн бұрын
check data section click scan id go to the far end and uncheck the scan checkbox then go to student id and check scan
@Battery-Powered 12 күн бұрын
Hi Hash, Thank you for the great video. I may need a bit of help for my particular need. Would you be able to assist me?
@aliiraq4149 13 күн бұрын
Hi and thank you How can I give an order to Google sheet to fill a new row when using a mobile or laptop can I get these sheet rows horizontal cell by cell
@stefania8715 13 күн бұрын
Hi, this is great, thank you, but I'm getting two errors: "TypeError: Cannot read properties of undefined (reading 'source')" with the onEdit function and "TypeError: Cannot read properties of undefined (reading 'length')" with the getNextColumnData function. Can you help?
@HashAliNZ 13 күн бұрын
Hi Stefania onEdit functions aren't made to be run using the Run button in Apps Script. When you make a change within the spreadsheet at your target cells then it should work
@stefania8715 13 күн бұрын
@@HashAliNZ Thank you for your fast reply! I tried the run button because it didn't already work :( I've changed the sheets names, the edited column number and the data range address , the rest of the code is untouched, can't figure why it doesn't work :((
@HashAliNZ 13 күн бұрын
@stefania8715 feel free to share your code here. I'm not near a computer today so won't be able to get to it til later but could have a read through to see if I can spot any issues
@stefania8715 13 күн бұрын
@@HashAliNZ function onEdit(e) { const sheet = e.source.getActiveSheet(); const editedColumn = e.range.columnStart; const editedRow = e.range.rowStart; const dataRangeAddress = 'A2:C'; if (sheet.getName() === 'registrazioni') { const productsSheet = e.source.getSheetByName('dropdownData'); const dataRange = productsSheet.getRange(dataRangeAddress).getValues(); const columnOffset = productsSheet.getRange(dataRangeAddress).getColumn() - 1; const maxColumnIndex = productsSheet.getRange(dataRangeAddress).getLastColumn() - columnOffset; clearDropdowns(sheet, editedRow, editedColumn, maxColumnIndex + 1); if (editedColumn >= 5 && editedColumn < maxColumnIndex + 1) { const selectedValues = sheet.getRange(editedRow, 2, 1, editedColumn - 1).getValues()[0]; const nextColumnData = getNextColumnData(selectedValues, dataRange, editedColumn - 1); if (nextColumnData.length > 0) { const targetCell = e.range.offset(0, 1); var rule = SpreadsheetApp.newDataValidation() .requireValueInList(nextColumnData, true) .build(); targetCell.setDataValidation(rule); } else { e.range.offset(0, 1).clearContent().clearDataValidations(); } } } } function getNextColumnData(selectedValues, dataRange, columnIndex) { let filteredData = dataRange; for (let i = 0; i < selectedValues.length; i++) { filteredData = filteredData.filter(row => row[i] === selectedValues[i]); } const uniqueNextColumnValues = [...new Set(filteredData.map(row => row[columnIndex]))]; return uniqueNextColumnValues; } function clearDropdowns(sheet, row, startColumn, maxColumn) { for (let col = startColumn + 1; col <= maxColumn; col++) { let cell = sheet.getRange(row, col); cell.clearContent().clearDataValidations(); } }
@stefania8715 13 күн бұрын
@@HashAliNZ I really appreciate 🥰
@EricIA-o1h 14 күн бұрын
Thanks, very useful
@BigG-e1v 15 күн бұрын
Hi, pls help me, my checkbox is in column A, and my prices are in column D, what should i do if i want that everytime i click the checkbox, the value in column D will be disregarded?thank you
@HashAliNZ 15 күн бұрын
=if(A2, 0, D2)
@BigG-e1v 15 күн бұрын
i'm sorry sir but where should i type this formula? coz i tried to type this in A2 & it got an error same when i type this in D2.
@HashAliNZ 15 күн бұрын
Type it into E2
@BigG-e1v 15 күн бұрын
Sir Hash I really appreciate your help,actually it's working, but would it be possible that everytime i click the chockbox in column A, the corresponding value in column D will be disregarded without adding another column?like what you've suggested? thank you so much for your help sir Hash
@HashAliNZ 15 күн бұрын
Yes but you would have to 'hardcode' the value in which isn't great because prices might change in the future. But if youre sure you want then in D2 type =if(A2, 0, 15.99) And just change the 15.99 to whatever value you want.
@luisashambo4087 15 күн бұрын
Simplicity and amazingly helpful!
@Kartik.0m 15 күн бұрын
I really very much appeciating your work...But.....The Start and End Time is Updating in every 5 minutes then how come it is a perfect Time Sheet.......Time is changing always...which results whole sheet with errors...please look into it
@HashAliNZ 15 күн бұрын
If you followed the tutorial it shouldn't change in your sheet. The example I showed iny videos might change because I film over several hours and sometimes days and make changes as I go before cutting it down in editing.
@JOELY713 16 күн бұрын
Awesome video! On my 'home' page my drop down starts in column E. How do I modify the code for that difference? I've been trying to figure it but to no avail.
@HashAliNZ 16 күн бұрын
Should be as simple as changing the 2 to a 5 in line 15
@Sonehan 16 күн бұрын
it was incredibly helpful!! you are the best! thanks!!
@BobMiner-u4s 17 күн бұрын
Excellent example and explanation. Thanks.
@christophermanglona5390 17 күн бұрын
My problem is when scanning the QR code, it asks to sign in to an email. How do I fix that setting so that when the code is scanned, it automatically submits the response?
@HashAliNZ 17 күн бұрын
Use the updated system linked in the description. It's better, more secure, and less likelihood of messing things up.
@GoingGreenMom 18 күн бұрын
This would have been awesome when I was in charge of keeping driver files up to date.
@angielewis3196 19 күн бұрын
Help! I went to set this up and my school IT admin will not allow the extensions “document studio” or “Appsheet”. Is there a way to do it without those??? I am really trying to make QR codes for my kids so they can run laps on our recess track and scan every time they run a lap.
@HashAliNZ 19 күн бұрын
Hi Angie, Because you're not using the QR codes for attendance, you might be able to get away with my previous, less secure option. It has one person scanning the QR codes as they come in and uses Google Forms and Google Sheets along with a regular camera app on any modern smartphone. You could print off the QR codes large and place them on the runners' chests alongside their running number or whatever identifier you'll generally have for them. A problem I foresee with this method is if you have many runners reaching the crossing line simultaneously, it could cause a build up and delay if you have only one person scanning. Multiple people can scan at the same time, so that could mitigate the problem slightly. Here's that video: kzbin.info/www/bejne/lWeQi3d9g9mWiMk Another option is to have the person running the event (the scanner person) create a simple spreadsheet system where they just type in the number of the runner as they run past. It's much faster as long as the recorder can type in the numbers fast and accurate enough. Make sure the identifiers are unique but also simple enough for the person recording to prevent delays and mistyping. From there, it's just a simple formula to automatically record the time that the identifier was entered using this video: kzbin.info/www/bejne/bJrbpWCYbblrnpIsi=9xdG58cLkLpk4fSj and then you can use a vlookup or xlookup to get the student's details. Here's an example I've whipped up for you: docs.google.com/spreadsheets/d/1ZzB380YiTaGQF5DnT5sB9bhzHofA1uYCvocMgqKEAbE/copy
@rahulhota5037 19 күн бұрын
Sir I have a question. I had a printout of all my student QR code and when i scanned it , it only shows some number. What will be the solution? Please help me.
@GoingGreenMom 20 күн бұрын
🤯 I think you just fixed a lot of issues with a project I'm working on.
@Зле_Коте 20 күн бұрын
Thank you - it was very helpful video
@WES_5150 21 күн бұрын
The condition should be if the date thats in the due date cell is in the past.
@HashAliNZ 19 күн бұрын
=[date] < today()
@BoardOfView 22 күн бұрын
Hi!! Quick question! After I create this sheet linked to the settings sheets, imagine I want to duplicate it. How can I maintain the link with the settings?
@marenlibrarian 17 күн бұрын
I have the exact same question!
@AndriesduPlessis 22 күн бұрын
Lifesaver!
@gobindakhanal6973 22 күн бұрын
This is what I needed for a long time
@Amphiartstudio 23 күн бұрын
This was incredibly helpful and the first time I didn't hardcore struggle with formulas, thank you!
@muhammadsameer883 25 күн бұрын
excellent
@HexdTwilight 25 күн бұрын
I really appreciate and like your methods of explaining the process. My favorite part was how you would put the keybinds to quickly do that action on screen.
@TanyaCalitz 25 күн бұрын
Kia Ora, I have past dates e.g. 02/02/2023 and I would like the expiry to be a year out so 02/02/2024 to be highlighted red if 1 day from expiry, orange if 1 month and green more than a month out. Can you please help with this formula? Thanks!
@TrườngPhùng-DHOE 25 күн бұрын
If I want to change the initial data fill column from Col2 to Col4 then what value should I adjust in the code you shared.
@Rafaelnin 26 күн бұрын
Great video!! Question: Is it possible to use the qrcode a single time? I want to make sure that I have no gatecrashers in my party
@gyankigudri 27 күн бұрын
❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤
@lachtak42 28 күн бұрын
I'm so fucking grateful to you. Bless you, laddie 💖
@HashAliNZ 28 күн бұрын
Happy to help, lady
@AndriesduPlessis 28 күн бұрын
Would have been awesome if data could be written to Moodle from my Scanned Sheet once I create this app.. Students found a way to cheat with Moodle attendance when using the LMS. I will thus have to spend hours and create this intermediate step