CommentsToSheets

Guides

Build a comment dashboard in Looker Studio from a Google Sheet

A spreadsheet answers questions for the person who built it. A dashboard answers them for everyone else: a client, a manager, a community team. Looker Studio is free, reads Google Sheets directly and takes about half an hour to set up for comment data.

Updated Nov 19, 2026 · 7 min read

Prepare the sheet

Looker Studio reads one tab as one table. It needs a header in row 1, one row per comment and nothing else on the tab: no totals row, no notes beside the data.

  1. Export the post or posts and get the rows into a Google Sheet. For one post, Copy for Sheets or a live =IMPORTDATA link is enough. For several, stack them in one tab as described in compare comments across networks in one sheet.
  2. Name the tab Data.
  3. Rename the first header from # to row. Field names made only of a symbol are awkward to use in Looker Studio formulas.
  4. If you stacked several posts, add a post column with a short label for each.

Copy for Sheets carries up to 20,000 rows per paste. For bigger posts, upload the workbook to Google Sheets with File, Import and use its Comments sheet.

If the tab is filled by IMPORTDATA, the rows disappear when the link ends, and the dashboard empties with them. For a dashboard meant to last, copy the imported range and Paste special, Values only into the Data tab. The IMPORTDATA guide explains when a link ends.

Connect the sheet

  1. Open Looker Studio and choose Create, Data source.
  2. Pick the Google Sheets connector, then your spreadsheet and the Data tab.
  3. Leave Use first row as headers ticked and press Connect.

You now see the field list. Check the types before building anything; a wrong type here is the cause of most broken charts later.

FieldType to setWhy
comment_id, user_id, reply_to_comment_idTextThey are identifiers. As numbers they would be summed and rounded.
posted_at_utcDate & TimeNeeded for the time series and the date range control.
likesNumber, default aggregation SumSo scorecards can total it.
username, comment, is_reply, platform, mentions, hashtagsTextDimensions to group and filter by.

If posted_at_utc will not switch to Date & Time, the sheet holds it as text. Add a calculated field instead (Add a field):

PARSE_DATETIME("%Y-%m-%d %H:%M:%S", posted_at_utc)

Name it posted_at and use it wherever a date is needed.

Calculated fields worth adding

In the data source, Add a field for each of these. Looker Studio formulas are not spreadsheet formulas: no leading equals sign, and field names instead of cell references.

NameFormulaUse
TypeCASE WHEN is_reply = "yes" THEN "Reply" ELSE "Top-level" ENDReadable labels for charts.
PeopleCOUNT_DISTINCT(user_id)A metric: how many different commenters.
Is questionCONTAINS_TEXT(comment, "?")A true or false dimension to filter on.
Comment lengthLENGTH(comment)To filter out one-word comments.
Local timeDATETIME_ADD(posted_at_utc, INTERVAL -5 HOUR)The timestamp moved to your audience's zone. Change -5 to your offset from UTC.
HourHOUR(Local time)For a comments-by-hour chart.

The offset in Local time is fixed, so it is an hour out for half the year in places with daylight saving. For a dashboard covering one campaign that is usually acceptable. When it is not, do the conversion in the sheet, where a formula can switch offsets by date: convert UTC timestamps to your time zone.

Lay out the page

Press Create report from the data source. A layout that works for comment data, top to bottom:

  1. Controls row. A date range control, a drop-down list on platform, a drop-down on post if you have it, and a drop-down on Type.
  2. Four scorecards. Record Count renamed "Comments and replies"; People; likes (Sum) renamed "Likes on comments"; and Record Count again with a filter Is question = true, renamed "Questions".
  3. Time series. Dimension posted_at_utc (or Local time), metric Record Count, breakdown dimension Type. This shows the first wave of comments and the later wave of replies.
  4. Bar chart by network. Dimension platform, metrics Record Count and People.
  5. Top commenters table. Dimension username, metrics Record Count and likes, sorted by Record Count, 10 rows per page.
  6. Comment table. Dimensions username, comment, platform; metric likes; sorted by likes, descending. Turn on text wrapping for the comment column.

Add an Input box control (under Add a control) on the comment field with the Contains search type. Viewers can then type a word such as "delivery" and every chart and table narrows to comments containing it. Of everything on the page, this is the control people use most.

A worked example

Fernhill Market posts the same autumn menu video on Instagram, TikTok and Facebook. The three exports are stacked in the Data tab, 1,840 rows in all. On the finished dashboard:

  • The scorecards read 1,840 comments and replies from 1,212 people, with 96 questions.
  • The bar chart shows TikTok with the most comments and Facebook with the most questions per comment.
  • Typing "gluten" in the search box leaves 31 comments, 27 of them questions, almost all on Facebook.
  • Setting Type to Reply and looking at the top commenters table shows fernhill.market wrote 40 replies on Instagram and 3 on Facebook.

The finding writes itself: the Facebook audience is asking about allergens and is not getting answers. No single number in a spreadsheet would have shown that as quickly as three clicks did.

Keeping it fresh

Looker Studio does not read the sheet on every view. It keeps a copy and refreshes it on a schedule set in the data source under Data freshness, 15 minutes by default for Google Sheets. After pasting new rows, open the report and choose Refresh data from the three-dot menu to see them at once.

Three things to know when the data changes:

  • New rows appear on the next refresh. Nothing to do.
  • New columns do not appear until you edit the data source and press Refresh fields.
  • Renamed headers break every chart that used the old name. Decide the headers once and leave them.

The comments themselves only change when you export again. A dashboard for an ongoing account is fed by a weekly routine of exporting and pasting: a weekly comment report for agency clients describes one, and its Data tab is already in the right shape for this dashboard.

Sharing, and what not to share

Share the report from the Share button like any Google file. With the data source set to Owner's credentials (the default), viewers see the charts without needing access to the sheet behind them.

The comment table shows usernames and what people wrote. They wrote it in public, but a searchable list of named people is still personal data. Share with named people, not with "anyone with the link", and consider leaving username out of the comment table on reports that travel outside the team. For research use, the page on exporting comments for research covers what to state about the data.

Also state the coverage somewhere on the page, in a text box: comments read out of the count each post shows, and the export date. A dashboard looks authoritative; it should say what it is built on. If a count looks low, check the export is complete before the dashboard goes out.

FAQ

Is Looker Studio free?

The standard version is free with a Google account, and the Google Sheets connector is included.

Can Looker Studio read the IMPORTDATA link directly?

Not as a connector. Load the link into a Google Sheet with =IMPORTDATA and connect Looker Studio to that sheet. For a lasting dashboard, paste the rows as values so they remain after the link ends.

Why is my scorecard for comment_id a huge number?

The field is typed as Number and is being summed. Edit the data source and set comment_id, user_id and reply_to_comment_id to Text. Count comments with Record Count.

How many rows can it handle?

A Google Sheet of tens of thousands of comment rows works. Charts slow down as the sheet grows; keep one data tab per client or campaign instead of one for everything.

Can I chart sentiment?

Yes, once the sheet has a label column. Add the labels in Google Sheets first, then use the label as a breakdown dimension.