Automated Row Addition/Deletion

[Frayda Zirkind]

Hello again, 

I've had a Sheet with its accompanying Script and Triggers for a while that does a lot of work for me. Since there are so many details, though, it easily gets funky and needs edits because it suddenly doesn't work. 

I have a feeling that the Scripts could be condensed and made a lot simpler, so if anyone can help with that, it would be greatly appreciated. 

Sample spreadsheet with my data: https://docs.google.com/spreadsheets/d/1yHpNuOu5AOwEgyRJBg2Q9JedYta4RxUem7I2mXgX2TE/edit

I have a list of teenagers who volunteer for my organization on the first Sheet. They are supposed to inform me every time they come to our office with their volunteer buddy and I notate every visit in the second Sheet, which pulls the kids' names from the first Sheet. 

Every time I add a row in the first Sheet I want a row to be added in the second sheet so I can have a record of all their visits, and every time I remove a row in the first Sheet (because they've stopped volunteering), I want that kid's row to be completely deleted (including all the static data from their row) in the second Sheet. 

As I said, I have Scripts that do this, but they are clunky and I'm wondering if there's any way to simplify it. 

Thank you in advance!

[Keith Andersen]

What and where is the FCConnect tab?

What does it contain/ do?

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/1c100ddd-9822-4992-bb14-9896ac00181dn%40googlegroups.com.

[Kildere S Irineu]

Hi Frayda,

Keith’s question is important: before changing the script, it would help to know what the FCConnect tab does.

That said, I think this can probably be simplified. Instead of trying to track row insertions and deletions one by one, I would recommend using a unique ID for each volunteer on the first sheet, then running one sync function that compares the current volunteer list with the visit log sheet.

Basic idea:
1. Keep a unique Volunteer ID in the first sheet.
2. In the visit sheet, also store that same ID, preferably in a hidden column.
3. When the sync runs:
   - if an ID exists in sheet 1 but not in sheet 2, add a new row;
   - if an ID exists in sheet 2 but no longer exists in sheet 1, delete that row;
   - do not rely only on row numbers, because row numbers change when users sort, insert, or delete rows.

Apps Script has native methods like insertRowsAfter() and deleteRows() for this kind of operation, but I would avoid having many small trigger functions. A single sync function, run from an installable onEdit trigger or a custom menu, will likely be easier to maintain.

One important caution: if the second sheet contains historical visit records, deleting the whole row may also delete history. In that case, I would mark the volunteer as inactive instead of deleting their row.

If you can clarify what FCConnect contains, it will be easier to suggest the safest simplified structure.

[Frayda Zirkind]

I'm sorry, folks. "FCConnect" was the name of the "Logs" Sheet in my original Spreadsheet. I changed it in the Script.

[Frayda Zirkind]

Hi Kildere,

Thanks for your response. 

I have unique IDs already established, and that's how the rows delete, but I don't think I can use that for adding the rows since the row needs to be added in the same position as in the first Sheet. And if I just have the second Sheet mirroring the columns in the first Sheet, the static data in the second Sheet won't be aligned with the correct kid's name. 

The second Sheet does contain historical visit records, but I don't need the visits' information after the volunteer becomes inactive, so I have no problem just deleting the row. 

On Thursday, June 11, 2026 at 7:44:41 AM UTC-4 Kildere S Irineu wrote:

[Keith Andersen]

See if this meets your needs.
https://docs.google.com/spreadsheets/d/15UExGu_LujkozJmrFx0Mqq5F7P2QYoYKFM9Q87fHCUU/edit?usp=sharing

Make a copy to gain full editor access.

Read Info page

Cheers

Keith

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/8c79d163-619b-4468-8a6b-ebfcef72698bn%40googlegroups.com.

--

My Site: https://www.sites.google.com/view/klaweb/

My Google Stuff: https://sites.google.com/view/klaweb/google-sheets-stuff

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[Frayda Zirkind]

Thank you Keith!

This is exactly what I need!

Have a great weekend!

[Keith Andersen]

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!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0f889fe5-901d-4daa-a24b-bb6be489ac1an%40googlegroups.com.

[Peter Isakson]

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.

[Kildere S Irineu]

Glad to see Keith’s sample solved the immediate issue.

I also think Peter’s suggestion is worth considering as a longer-term improvement. Keeping Logs in a normalized format — one row per volunteer/week or volunteer/visit — would probably make the Apps Script much simpler and reduce the need to insert/delete columns or rows to preserve layout.

Keith’s solution seems ideal for the current structure, while Peter’s approach would be a good future redesign if the sheet keeps growing.

[G. G. R.]

Nu México no tiene un respaldo no tiene señal o soy yo pero mis cuentas. Están en ceros

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/f87cd658-995a-432e-8f9c-7015983234f5n%40googlegroups.com.

[Keith Andersen]

Frayda,

I agree with Peter and Kildere that a proper normalized database type solution is best. To that end I created that system. Use it if you like or use your system - totally up to you.

Here it is: https://docs.google.com/spreadsheets/d/1UVy_9OkfsnRtS8saX0gEblufQ_16MdPDBxIHgvQm8mM/edit?usp=sharing

It is view only, so make a copy as before.

It should be pretty intuitive, feel free to reach out and ask questions or if you need tweaks to suit your needs. 

You can still delete Volunteers and their logs. 

There's an email feature in the Special Menu. You can send individual or bulk emails to your volunteers if needed. Just add their emails in the settings menu.

There are other bells and whistles as I used my sheet template for this project. Just incase you plan on learning more about app script.

Cheers

Keith

PS- Peter and Kildere....let me know if this is what you envisioned.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAF22h1GaqUTip8TYY90Ts%2BPjnD_aFr_gDS-tiwXNMPn_61xVLA%40mail.gmail.com.

[Frayda Zirkind]

Thanks Peter and Kildere for your input and Keith for creating the system!

For now, Keith's original Sheet works perfect for me, but I will keep the new Sheet in my Drive in case I want to use it in the future. 

Thank you again!