How to use Google AppsScript in Google Sheets to clean text data

Jessica YungData Science, ProgrammingLeave a Comment

Two weeks ago I finally finished cleaning a pile of debating topics data. And now I’ve imported it all into HelloMotions.com ‘s database so it now has over 3500 debating topics!

When I first tried to use Google AppsScript a few months ago, I got a lot more confused than I should have been. The aim of this post is to make using AppsScript easier for first-time users. The format of this post is a list of questions indicating functions you might want to write and then answers in the form of real AppsScript functions.

Functions covered in this post:

  • How do I append a value to each bolded cell?
  • How do I replace the last few characters in each bolded cell?
  • How do I remove the end date for each bolded cell?
  • How do I copy the content of the row above if the current row is blank?
  • How do I fill all cells in a range (e.g. a column) with a certain value?
    • How do I fill all cells in a range (e.g. a column) with a certain value ONLY IF the cell is blank?
  • How do I fill cells in Column A with values of keys corresponding to what was in Column B? (dictionaries, look-up tables)

How do I append a value to each bolded cell?

If you want to do this for all cells in a range (and not only all bolded cells in the range), simply removed the if clause wrapper:

How do I replace the last few characters in each bolded cell?

This is similar to the previous function. The only difference is in the manipulation in the final ‘if’ clause.

The existing year is in the last four characters of each cell. ( current_value.slice(0,-4) takes all characters in current_value except the last four.) There is a year in every cell we’re manipulating.

How do I remove the end date for each bolded cell?

E.g. For cells that end with e.g. April 22-23, this takes out  -23.

This function includes an ‘if’ clause that removes characters at or after the symbol  - only if they appear after the 15th character in the cell. This is to avoid deleting all the information in a cell that follows early hyphens in words. It’s not foolproof, but it worked well enough for my purposes.

How do I copy the content of the row above if the current row is blank?

I used this to fill in tournament information. So applied to the first column on the left, this would copy ‘Split Union Open’ to the first-column cells of the blank rows underneath.

The function would stop filling blank cells with ‘Split Union Open’ when it reached another non-blank cell with another tournament name, e.g. ‘Oxford IV’, and then fill the blank cells (of that column) in the following rows with the new tournament name ‘Oxford IV’.

screenshot.png

How do I fill all cells in a range (e.g. a column) with a certain value?

How do I fill all cells in a range (e.g. a column) with a certain value ONLY IF the cell is blank?

How do I fill cells in Column A with values of keys corresponding to what was in Column B? (dictionaries, look-up tables)

(Nothing happens if the value of Column B cell does not correspond to a key in our dictionary.)

What does this mean? Suppose I have two columns Colour and Colour HexCode and have a dictionary {‘Red’: ‘FF0000’, ‘Green’: ’00FF00′}.

I want to fill the column Colour HexCode with the corresponding hexcode of the colour in the column Colour. So if   Colour is ‘Red’ in my row, I want to fill Colour HexCode with ‘FF0000’.

Leave a Reply