How to use Google Sheets from a Google Ads script

Reading settings from and writing reports to a Google Sheet, the permissions involved, and the usual errors.

Spreadsheets are the most common companion to a script: as an output for reports and as an input for lists of keywords, budgets or URLs.

Opening a sheet

var SHEET_URL = "https://docs.google.com/spreadsheets/d/…";

function main() {
  var sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName("Report");
  sheet.clearContents();
  sheet.appendRow(["Campaign", "Clicks", "Cost"]);
}

For anything more than a few rows, build an array and write it in one call with getRange(...).setValues(rows). Writing row by row is slow and can run a script into its time limit.

Permissions

The script acts as the user who authorized it. That user needs edit access to the sheet.
The first time a script uses SpreadsheetApp, Google Ads asks for spreadsheet permission during authorization.

One sheet per client

Make the sheet URL a variable. In chiliad, declare it with the URL type and set a different sheet for each account in the Configure table; cells of that type get a link to open the sheet directly.

Common errors

"You do not have permission to access the requested document" — the authorizing user cannot edit the sheet. Share it with that login.
"Authorization is required" — the script gained spreadsheet code after it was authorized. With a chiliad loader, copy the loader again so it requests the new permission, then authorize.
Sheet not found — getSheetByName returned nothing because the tab was renamed. Check the tab name.
Writes in preview — spreadsheet changes are not held back by preview. Use a test sheet while trying a script out.