Determine if text in google spreadsheet cell exists in text in another column of cells

I had a email newsletter list with name and email addresses listed. I also had a different list of emails that needed to be removed from that list. I used this google spreadsheet cell formula to check to see if the current row’s email was in the list of emails in the next column. Once I had them marked I used Delete a row in google spreadsheets based on value of cell to remove the row.

=IF( COUNTA( IFERROR( FILTER($E$1:$E$97 , $E$1:$E$97=C2 ) ) ) , "Yes" , "No" )

Delete a row in google spreadsheets based on value of cell

I used the following google spreadsheet script to remove a bunch of rows that had a set value:

/**
 * Deletes rows in the active spreadsheet that contain 'Yes' in column A
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function readRows() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();

 var rowsDeleted = 0;
 for (var i = 0; i <= numRows - 1; i++) {
 var row = values[i];
 if (row[0] == 'Yes') {
 sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
 rowsDeleted++;
 }
 }
};

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var entries = [{
 name : "Remove rows where column A is 'True'",
 functionName : "readRows"
 }];
 sheet.addMenu("Script Center Menu", entries);
};

Publishing and Sharing Google Analytics Certification Certificate

Passed the Google Analytics and want to share with the world your results? Here is how you can find and publish your certificate for anyone to view.

How to:

1. Log into your account at https://google.starttest.com/

2. Navigate to “Manage Test Records”

3. Add a new test record

4. Add details to new test record

5. Confirm test details in preview

6.  Save test record

7. View record details

8. Copy test record link and share with the world

Final results and what the world sees when you share the link

Original instructions from Google back in October 2009

Personalizing Google Analytics

Since I passed the Google Analytics individual qualification test I have been working to make the data captured in GA more actionable. I have found custom dashboards to be a great way to pull out the information that is useful to me at a glance. Note that this doesn’t replace digging through the data and asking new questions about your customers.

Not sure what you could put in a custom Google Analytics dashboard? Check out this blog post by econsultancy.com on 10 useful Google Analytics custom dashboards.