Appends last week's top search queries, clicks, impressions and average position to a Google Sheet every Monday.
Uses
Google Sheets
Weekly search performance export to Sheets
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 noRowsErrorTry
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 0Yes
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 tabErrorTry
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 yesYes
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 queryErrorTry
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 emptyYes
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.