Find a given word in a given colum and insert a row above if found

Skip to first unread message

Joseph Strunk

Jun 25, 2022, 12:17:08 PMJun 25
to Google Apps Script Community
Hey all,

I am brand new to attempting to write scripts for google sheets.

I am managing a list of camp counselors, their schedule, attendance, pay, and the overall budget of the camp.

I have 5 separate sheets, one for the counselor list, one for the schedule, one for the attendance, one for the pay, and one for the overall budget.

in each sheet, there is a list of the current counselors that references back to the master counselor list sheet.

I want to be able to easily add new counselors to the master list and have it update across the document.

at the bottom of the counselor list is a counselor named "anchor".

I want to know if its possible to add button to the menu toolbar that, when clicked, will search for "anchor" in the appropriate column, then insert a new row above the cell that has "anchor", and then type "New Counselor" in the cell above "anchor".

After it updates the master counselor list, I would like it to search the other 4 sheets for "anchor" in the appropriate columns, insert a row above "anchor" and then autofill the cell above "anchor".

Like I said I am very new to this, If anyone could point me in the right direction I would be very thankful.

Clark Lind

Jun 26, 2022, 8:34:04 AMJun 26
to Google Apps Script Community
Something like this is exactly what Apps script is designed for. If you want to get started writing your own scripts and solutions, then I recommend creating a copy of your Spreadsheet and having some fun. 
Start by using the Macro recorder. Be very explicit in the steps you take. For instance, when you start recording, don't start on the tab where you want to start, start on the second tab, that way, when you record your first steps, you will be clicking on the real start tab, and it will get recorded. Does that make sense? That way, when you run the macro in the future, you could be on Tab 5, and it will know to go to tab 1. 
Then click on the column you want to search, and do a search for 'anchor'. When it finds it, right click and insert a row above and type in what you want. 
etc etc. it will record all your actions into a script. When done, save it, and try running it and go from there. 

For someone to create the script for you, it would be <60 minutes.. maybe <20-30 minutes for an experienced script writer/developer. Whenever I want to figure out how Sheets would do an action, I simply record a macro and do the action lol

Good luck!
Reply all
Reply to author
0 new messages