Re: [Apps-Script] Digest for google-apps-script-community@googlegroups.com - 3 updates in 1 topic
Kildere S Irineu
Peter makes a very good point.
Keith's solution solves the immediate synchronization problem while keeping the existing spreadsheet design intact, which is probably the right choice right now.
For the long term, however, I agree that a normalized log structure would be easier to maintain. Storing records as VolunteerID + WeekDate + Status would eliminate most row/column synchronization logic and allow lookups and pivot tables to generate the current view automatically.
So I see these as complementary approaches rather than competing ones:
Keith's solution = best short-term fix with minimal disruption.
Peter's design = stronger long-term architecture if the system continues to grow.
If the current setup is working well after Keith's changes, I would keep it as-is for now and consider Peter's redesign only if maintenance becomes a recurring issue.
- Automated Row Addition/Deletion - 3 Updates
Frayda Zirkind <fzirk...@gmail.com>: Jun 12 09:52AM -0700
Thank you Keith!
This is exactly what I need!
Have a great weekend!
On Thursday, June 11, 2026 at 6:12:31 PM UTC-4 Keith Andersen wrote:
Keith Andersen <contact...@gmail.com>: Jun 12 12:07PM -0500
You're quite welcome.
Have a great weekend yourself.
Feel free to reach out should you need adjustments.
God bless,
Keith
My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets
+ App Script and much more!
Peter Isakson <isak...@gmail.com>: Jun 12 10:55AM -0700
I would consider redesigning the structure of the Logs sheet to be more
"relational friendly" (normalized, if you are a geek). Instead of the weeks
going across the page in columns, add a new field for "date" (or week
number, whatever you prefer) and one for the checkbox. The structure would
look something like:
*Rec#* *Week Date* *Checked*
4902 4/12/2026 False
You simply add a new row for the person using their ID (rec #) when the
"log" action occurs (whatever in real life triggers that). You can easily
create a new tab that will look like Logs. Use a lookup function (like
XLOOKUP) to fetch the Rec # and look up the name of that person from the
Volunteers "master list" (or any other attributes you want); this means you
don't have to manually or in code copy over these attributes and they will
always be correct (say, you correct a misspelling in a Volunteers name; by
using lookups it will show in the logs crosstab automatically). Then use a
Pivot table to create the crosstab view like it appears in Logs by
selecting the rec # in the pivot tables' rows area (as well as adding the
names), and the Week Date in the columns section. The Value section will be
the Checked column, converted to whatever visual char you want (such as a
check). Because the value requires an aggregate function like SUM or MIN,
and the Checked column in Logs isn't a number, you would need to use a
function like MIN - since there is only one cell per rec# per week, this
will work to fetch that one value. The Last Logged value is simply the max
per row (per pwerson) of the Week Date.
I have not looked at the apps script, but it would obviously have to
change, and I suspect would be simpler.
On Friday, June 12, 2026 at 1:07:49 PM UTC-4 Keith Andersen wrote:
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to google-apps-script-c...@googlegroups.com.
