I did find few scripts about how to make Google Forms result sheets to auto-create new sheets by a specific data in a cell. I did not manage to get the scripts to work - and I was wondering if conditional formatting could be added to the script too.

So, lets say my sheet document is named 'campaign'. The sheet with the used data is named 'data'. That sheet has header row which does not need to be copied to the new sheets. Column E on that sheet is named 'workplace'. Whenever there is a new workplace added to the list I would want the script to auto-create a new sheet and name it with the workplace value/data/info. At the same time I would like the script to copy the specific row into the new sheet.

Could someone tell me how to write a working script for this?

As a bonus question: In the 'data' sheet is also a column named 'score'. When the script copies the rows into the auto-created sheets, can the script also apply conditional formatting based on the value in the column 'score' (we can say the column is G)? For example if the value in the copied row is 1, make the background of the cell green, 2 = yellow and 3 = red.

I would also like the script to run only with the specific document 'campaign' not with any other sheets. Is that doable too?

  • What are your coding skills like? Your script has several discrete elements for which there are many excellent precedents. 1) onEdit trigger to detect a cell edit; 2) Event objects to identify the edited sheet and column; 3) check for existing value; 4) create a new sheet and copy the row into the new sheet. – Tedinoz Nov 28 '19 at 10:45
  • @Tedinoz thank you for your comment! I would rank my coding skills at the beginner level. I will see the posts you linked and see how I can manage with those guides. – Antti M Nov 28 '19 at 13:29
  • Google Script onedit(e) event restricted to a specific sheet and specific range. This has 1) onEdit(e) and 2) using Event Objects to test for sheet name and column number. "Take small steps" - try and build this script as it applies to your spreadsheet (sheet=="campaign" and column == 5). Insert some logger statements for true and false so you can observe whether the combination is met or not. FWIW, the answer covers just with the "IF" statement, so later on you'll probably need some of the OP's early code such as var ss, etc. Report back. – Tedinoz Nov 29 '19 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.