I'm a teacher and I'm trying to set up a spreadsheet with students' grades that will link to an individual spreadsheet with the header from spreadsheet#1 plus the row that has an individual's grades. I want the formatting to copy over as well. I see how I can do that with the importrange() function and copy the formatting with a script, but I want the "comments" to transfer over as well. Is this possible? Is it possible to transfer merged cells? Does anyone have a more simple idea for copying formatting than what was discussed here: Cell reference with colour formatting ?
Comments cannot be copied. They are not considered part of the contents of a spreadsheet, they are more like chat messages, the record of a conversation that took place about that spreadsheet. If there was no such conversation about a copy of that spreadsheet, comments won't be there.
Alternative: use notes instead of comments. They get copied same way as cell contents.
In the situation you have, it'd be better to create a blank template of grade report, with all the formatting, and then copy that to individual students.
importrange is meant for importing data, not formatting. (Also, it does not import notes or comments.)
I have a come up with a way in Google sheets to make a sheet copy the text and formatting from a master sheet.
I will explain the scenarios to see if it matches your needs.
I have a created a master competency model for all a skills required within our business per grade for all roles.
I then need to create separate sheets that only show the competencies required for specific roles.
Since we have hundreds of competencies and I expect them to change over time I don't want to have to keep copying and pasting cells changes across multiple sheets.
What I have done is create replicas of the master sheet by having each cell in the copies reference the equivalent cell in the master. I have then made the formatting match. The child sheets will then have all irrelevant content removed but if anything changes on the master the equivalent cells on the child sheets will automatically update.
Here are the steps:
- Have a 'master' tab/sheet with all content and formatting you want to replicate
- Create a new tab/sheet 'child'
- In the top left cell of the 'child' tab/sheet (A1) type =
- Navigate to 'master' tab click on top left cell (A1) in master tab and then hit return.
- The 'child' tab now has the formula to grab all the data you need from the master. Now drag the A1 cell across and down the sheet and all the data will form the master will appear - with no formatting.
- To copy over the formatting go back to the 'Master' click Select all (Ctrl+A) Copy (Ctrl+C)
- Return to the 'Child' Select all (Ctrl+A) or click on top left of the sheet.
- Right Click > Paste special > Paste format only
You should now have an exact replica of the master sheet. And any changes to master will be reflected on the 'child'
NOTES: - comments are not copied over, nor are formulas or conditional formatting r data validation rules - any changes made to the data in a cell on the child sheet will overwrite the cell reference to the master