I’m working with a data entry spreadsheet that contains dropdown menus and various formulas to help users input information more efficiently. My issue is that whenever I add a new row right below the header row (position 2), the dropdown validation transfers correctly but all the formulas disappear. I’ve attempted using different automation scripts to solve this but haven’t had success yet. There are approximately 10 columns that contain formulas that need to be preserved when new rows are inserted. Does anyone know a reliable script or method to ensure formulas automatically copy to newly inserted rows?
Another solution that worked for me is using the onEdit trigger combined with a simple Apps Script function. Create a script that detects when a new row is inserted and automatically copies the formulas from the row above or a template row. The key is to set up a reference row (maybe row 3) as your template with all formulas intact, then have the script copy those formulas to any newly inserted rows. This approach is more flexible than array formulas since you can customize which columns get copied and handle complex formula dependencies. Just make sure to test the script thoroughly with your specific dropdown validations first, as some validation rules can interfere with formula copying.
I had a similar problem and found that creating a protected range for your formula rows works well. Set up your first data row with all the formulas, then protect that range but allow editing of cells. When you insert new rows, copy the protected row and paste it where needed. The formulas stay intact this way. Another approach is using table formatting - select your data range including headers and convert it to a table format. Google Sheets will automatically extend formulas when you add data to the bottom of the table. This method is more reliable than scripts and doesn’t break when the sheet structure changes.
try using array formulas instead of regular ones - they automatically extend when you add rows. just wrap your formula with ARRAYFORMULA() and it should copy down automaticaly. works way better than scripts imo