Skip to content
Serplyze
Start free
All docs

Google Sheets

Pull keywords, groups, site totals, pages or queries of a project into Google Sheets with a live CSV link and IMPORTDATA.

A live CSV link is a URL that returns one project's data as a CSV file. Paste it into =IMPORTDATA() in Google Sheets and the sheet keeps itself up to date, with no script and no API key.

Use it for client dashboards and reporting sheets that should always show a rolling period such as the last 28 days. Any tool that imports a CSV from a URL works the same way.

Plan

Live CSV links need a paid plan. While a workspace is on Free, its existing links answer with an error instead of data.

Good to know

Anyone who has the link can read that project's data without signing in. Share the sheet, not the link, prefer an expiry, and revoke a link as soon as it may have leaked.

Create a link

  1. Open Settings → API and Sheets and select Create CSV link. Only workspace owners and admins see the button.
  2. Enter a Name that says what the link is for, pick the Project, and choose when it Expires: In 90 days (the default), In 30 days, In 1 year or Never.
  3. Select Create link. A second dialog opens with a builder.
  4. In the builder, pick a Dataset and a Period. Depending on the dataset you can also set Device, Country, Group id and Max rows.
  5. Copy the Google Sheets formula or the CSV URL. Change the options and copy again for every dataset you need: one link serves all five datasets.
Limit

The link is shown only once, in this dialog. Serplyze stores a fingerprint of it, not the link itself, so build and copy every URL you need before you select Done. If you lose it, create a new link.

Use it in Google Sheets

Paste the formula into the top-left cell of an empty area. The first row is the header.

=IMPORTDATA("https://serplyze.com/api/sheets/ord_sheet_your_link_token/keywords.csv?period=3m")

The URL is made of the link token, the dataset name with .csv, and optional parameters. You can edit the parameters by hand in the sheet later; you do not need the builder again.

Datasets and columns

Dataset (file)One row perColumns
Keywords with metrics (keywords.csv)Tracked keywordkeyword, keyword_id, intent, groups, clicks, impressions, ctr, position
Keyword groups (groups.csv)Group or foldergroup, group_id, kind, parent_id, keywords, clicks, impressions, ctr, position
Site totals by day (site.csv)Daydate, clicks, impressions, ctr, position
Pages (pages.csv)Pagepage, clicks, impressions, ctr, position
Queries (queries.csv)Queryquery, clicks, impressions, ctr, position, branded, tracked_keyword_id
  • ctr is a ratio from 0 to 1 (format the column as a percentage in Sheets). position is the average position with one decimal. Both are empty when there are no impressions.
  • groups in the keywords file lists the names of the manual groups a keyword is in, separated by a semicolon.
  • In the groups file, the metrics are summed over each group's tracked keywords. A folder includes everything below it, a rule group is evaluated on the requested range, and a keyword that is in several groups counts in each.
  • branded is true or false. tracked_keyword_id is filled when you track the query.
  • Pages and queries are sorted by clicks, highest first.

Parameters

ParameterDatasetsDefaultValues
periodall28dDays (1d to 486d) or calendar months (1m to 16m), ending on the last synced day. The builder offers 7 and 28 days and 3, 6, 12 and 16 months.
from, toallnoneFixed dates as YYYY-MM-DD instead of a rolling period. from and period cannot be combined.
devicekeywords, groups, siteallall, desktop, mobile, tablet
countrysite, pages, queriesnoneThree-letter country code such as deu. For site totals it cannot be combined with a device.
groupkeywordsnoneA group id (the five characters in the group's URL in the app).
limitkeywords, pages, queries1000Up to 5000 for keywords, up to 25000 for pages and queries.

A parameter that a dataset does not use is rejected with an error, so a typo does not pass silently.

Limit

Keywords and groups are limited to the keyword history of your plan. Site totals, pages and queries are not. For pages and queries the range must end before today.

Refresh behaviour

  • Google Sheets decides when to fetch an IMPORTDATA URL again, roughly once an hour. Serplyze does not push data into your sheet.
  • Serplyze caches a finished CSV for 15 minutes per link and set of parameters. Recalculating a sheet inside that window returns the same file.
  • The data itself changes when the project syncs with Search Console. See How the data works.
  • The first request for a pages or queries range that is not cached yet can take up to a minute on a large site.

Limits and errors

  • One link covers one project. Create a link per project.
  • A workspace can have up to 50 active links.
  • A link may be requested 120 times per 10 minutes, cached answers included. Over that, it answers 429 until the window ends.
  • The file is UTF-8 CSV. Text cells that start with =, +, - or @ get a leading apostrophe so that a spreadsheet does not run query text as a formula.
  • Errors are one line of plain text, which Sheets shows as the import error: an unknown, revoked or expired link, an unknown dataset, a bad parameter, a workspace on Free, or revoked Google access.

Expiry and revoking

  • The Live CSV links list in Settings → API and Sheets shows each link's name, project, first characters, expiry, when it was created and when it was last used.
  • To revoke a link, select Revoke next to it and confirm with Revoke link. It stops working right away. Sheets that import it stop updating and keep their last data.
  • An expired link behaves like a revoked one. Expiry cannot be extended; create a new link and replace the URL in the sheet.