Formatting Sheets and Cells, also with Script


Clean and Clear Layout

Sometimes, we like our spreadsheet to give focus to what is on it. General formatting: colours, borders etc. do help, but even better is to remove all unneeded columns and rows, along with other distractions. For this to work, the top of the last row needs to be visible. Here is a finished example:

So how do we achieve this:

  1. Decide on the cells you want to keep. In this case it was A1:G13

  2. Select Cell A:13 then freeze Rows at that cell from File Menu

  3. Next drag and select all columns to the right of G and delete

  4. Now drag and select rows from A15 to A999 and delete

  5. Set row size of A14 to some outrageous like 1500

  6. Select A14:G14 and set to the lightest gray (next to white) colour to match

  7. While A14:G14 is selected protect the range to “Only You”

That should be that, clean and clear….: the view should be fixed, the “Adds 100 rows” should be nowhere to be seen, and we can focus on the job in hand.

Note: screenshot was taken by going fullscreen in browser (F11) then by selecting Fullscreen from the View menu.

This could all be done with google apps scripting, watch this space!

Format Cell Background

Format Cell Number Format

in this case, a date

Format Cell Wrapping

This script sets the cell wrapping to on for the whole column. Useful for Google Form responses which over write existing cell formats

Format Cell and Row Alignment Vertically

Another simple but useful one, particularly for better presentation of Form Responses, so pop this into your onFormSubmit script:

Conditional Formatting

No point re-inventing the wheel, go to THIS SPREADSHEET compiled by James/MrEighties for a world of conditional formatting possibilities