1. Create a new spreadsheet in which you can copy, paste and manipulate the data. Name it the “Working Tracker spreadsheet.”
2. In the first worksheet, which should be named “Where stories ran,” copy and paste the slug, news outlet’s name, date, link and headline from the “Our stories” spreadsheet. In a new column, combine the link and headline into a hyperlink (=hyperlink(linkcolumn,headlinecolumn)). Copy and paste values into the same new hyperlink column, then delete the separate headline and link columns.
3. Using Vlookup with the slug, pull in the date and hyperlink of your news organization’s original story from the “Slug info” worksheet. Copy and paste values into the same columns, then delete the slug column.
4. Create a pivot table with the data from the “Where stories ran” worksheet. Under rows, choose group by news outlet. Under values, chose display headline and summarize by COUNTA.
5. Copy and paste values from the pivot table into a new worksheet. Sort the news organizations by the number of stories picked up, in descending order. Name this worksheet “List of news outlets.”
6. Create a third spreadsheet to be publicly shared. Give it a name that can be displayed on your website. Copy the “Where stories ran” worksheet and “List of news outlets” worksheets from “Working Tracker spreadsheet” into the publishable spreadsheet using the small arrow on the worksheet tab in Google spreadsheets.
7. Select File > Publish to the web. Select “All sheets.” Choose “HTML to embed in a page” under “Get a link to the published data.”