My work has a Google Form where different information is inputted. All this information is then automatically put into a Google Sheet. We will call it "All data sheet". "All data sheet" has multiple sheets in it, one with all of the data and then one for each type of data which needs to be given to different people. One of those is called 'Caretaker'.
I then have a second Google Sheet (example called 'Sorting') that pulls everything from the 'Caretaker' sheet onto 'Import_range'.
This allows the caretaker to see everything that needs fixing with the oldest thing at the top and the newest at the bottom.
The caretaker would like the newest at the top. I can easily make another sheet (see 'Sorted') that uses the sort function to put the newest at the top.
However, he would also like another column where he can either check a box to show it's done or write an x.
When new data appears, the checked box does not move down.
Is there any way to sort the data but also have an input column which stays with the sorted data?
Updating Import_range sheet, clicking checkboxes and updating Sorted Sheet:
Implementing Time Driven Triggers:
- Open your Apps Script project.
- At the left, click Triggers alarm.
- At the bottom right, click Add Trigger.
- Select and configure the type of trigger you want to create.
- Click Save.
For time driven, your trigger setup should somehow look like this:
The example trigger above will update the sheet every minute. The down side of using time driven trigger is the quota limit. Gmail accounts are limited to 90 minutes of total runtime. Exceeding quotas will result to errors. See Quotas for Google Services.
Using Buttons: Alternative to Custom Menu
To create a user friendly button in your Sheet
- Go to Insert -> Drawing
- Draw the desired button shape and style.
- Click Save and Close
- Move the button to your desired position
- Right click, click the 3 vertical dots and select Assign script
- Type updateSorted and click OK
- Click the button to run
Note: If you decided to use buttons and want to remove the Custom Menu in your sheet, just remove the onOpen function in your Apps Script.