Weekly search performance export to Sheets

Every Monday at 08:00, last week's search performance for your site is pulled from your search analytics endpoint and appended to a Google Sheet, one row per query, so the trend builds up week after week without anyone opening a console.

Each row records the week it covers, the query, clicks, impressions, CTR and average position, written as real numbers and dates so you can chart any column straight from the sheet. It reads the seven full days ending three days before the run, because search consoles finalise a day's figures on a lag. The first run creates the tab and writes the header; every run after that only appends, so history is never overwritten. A week with no data stops cleanly and leaves the sheet alone, and a result with no query text is logged and skipped rather than ending the run.

You need an access token for your search analytics endpoint and a Google Sheet to hold the history.

Run every Monday at 08:00.Run every Monday at 08:00.

Set searchDataUrl to blank.Set searchDataUrl to blank.

Set accessToken to blank.Set accessToken to blank.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set sheetTab to Search performance.Set sheetTab to Search performance.

Set maxQueries to 50.Set maxQueries to 50.

Get the first item of today's date shifted by -3 days split by T and save it as weekEnd.Get the first item of today's date shifted by -3 days split by T and save it as weekEnd.

Get the first item of today's date shifted by -9 days split by T and save it as weekStart.Get the first item of today's date shifted by -9 days split by T and save it as weekStart.

Send data to searchDataUrl and save it as report.Send data to searchDataUrl and save it as report.

Set queryRows to report.body.rows.Set queryRows to report.body.rows.

Set rowsFound to 0.Set rowsFound to 0.

If a step below fails, save the error as noRowsErrorIf a step below fails, save the error as noRowsError

Try

Count the items in queryRows and save it as rowsFound.Count the items in queryRows and save it as rowsFound.

On error

Show in the output: The search analytics endpoint returned no rows for this week..Show in the output: The search analytics endpoint returned no rows for this week..

If rowsFound is 0If rowsFound is 0

Yes

Finish the run: No search data between weekStart and weekEnd. Nothing was added to the sheet.Finish the run: No search data between weekStart and weekEnd. Nothing was added to the sheet.

No

No steps

Set tabIsNew to false.Set tabIsNew to false.

If a step below fails, save the error as tabErrorIf a step below fails, save the error as tabError

Try

Google SheetsCreate the tab sheetTab in spreadsheetUrl.Create the tab sheetTab in spreadsheetUrl.

Change tabIsNew to true.Change tabIsNew to true.

On error

Show in the output: Adding this week's rows to the existing '@sheetTab' tab.Show in the output: Adding this week's rows to the existing '@sheetTab' tab.

If tabIsNew is yesIf tabIsNew is yes

Yes

Google SheetsAdd a row to sheetTab in spreadsheetUrl.Add a row to sheetTab in spreadsheetUrl.

No

No steps

Set rowsAdded to 0.Set rowsAdded to 0.

For each searchRow in queryRows:For each searchRow in queryRows:

Set queryText to blank.Set queryText to blank.

If a step below fails, save the error as queryErrorIf a step below fails, save the error as queryError

Try

Trim the first item of searchRow.keys and save it as queryText.Trim the first item of searchRow.keys and save it as queryText.

On error

Show in the output: Skipped a search result that carried no query text..Show in the output: Skipped a search result that carried no query text..

If queryText isn't emptyIf queryText isn't empty

Yes

Google SheetsAdd a row to sheetTab in spreadsheetUrl.Add a row to sheetTab in spreadsheetUrl.

Add 1 to rowsAdded.Add 1 to rowsAdded.

No

No steps

Show in the output: Added rowsAdded query rows for weekStart to weekEnd to the '@sheetTab' tab.Show in the output: Added rowsAdded query rows for weekStart to weekEnd to the '@sheetTab' tab.