I Made an Automated Pacing Doc Script for Google Sheets

Automate the creation of Google Sheets Pacing Docs with this script.

Table of Contents

What is a pacing doc?

A pacing doc is an essential tool for digital marketers and advertisers, designed to effectively manage and monitor client budgets across various advertising platforms, such as Google and Facebook. By tracking ad spend and performance, a pacing doc helps ensure that campaigns are on track to meet their goals while maximizing return on investment.

This powerful, data-driven solution streamlines the management of marketing campaigns, provides real-time insights, and enables advertisers to make informed decisions to optimize results. In this blog post, we’ll dive into the process of creating a pacing doc in Google Sheets, taking you step by step through the intricacies of this indispensable tool, and demonstrating how it can revolutionize the way you manage your clients’ digital ad budgets.

The Fundementals of a Pacing Doc

Here’s a quick overview of the key components of a pacing doc:

• Pacing tab: The pacing tab is the main tab of the pacing doc. It displays a summary of your clients’ ad spend and performance across Google and Facebook, along with key metrics such as cost per click (CPC), cost per thousand impressions (CPM), and cost per acquisition (CPA).

• Looker Studio tabs: The Looker Studio tabs display key insights about your clients’ campaigns, including the number of impressions, clicks, and conversions, as well as the cost per click (CPC), cost per thousand impressions (CPM), and cost per acquisition (CPA). This exists so you can display pacing data in your Looker Studio dashboards.

• Google Ads data: The Google Ads data sheet contains the data for your clients’ Google Ads campaigns. This data is imported from Google Ads using the “Power My Analytics” extension.

• Facebook Ads data: The Facebook Ads data sheet contains the data for your clients’ Facebook Ads campaigns. This data is imported from Facebook Ads using the “Power My Analytics” extension.

• Named Ranges: Named ranges are used to reference specific cells in a Google Sheet. They are used to reference the data in the Google Ads and Facebook Ads sheets.

• Labels: Labels are used to identify the data in the Google Ads and Facebook Ads sheets. They are used to reference the data in the Google Ads and Facebook Ads sheets.

• Google App Script: Google App Script is a scripting language that allows you to automate tasks in Google Sheets. It is used to automate the creation of the pacing doc.

• Power My Analytics: Power My Analytics is a sheet extension that allows you to import data from Google Ads and Facebook Ads into Google Sheets. It is used to import the data for your clients’ Google Ads and Facebook Ads campaigns.

• Instructions: The instructions sheet contains instructions for using the pacing doc.

Making the Pacing Doc

This tool is close to my heart because of how well it integrates into my daily life and workflows. Check back here for updated tutorials for each of these use cases.

Create a new Google Sheet & Authorize the script

1. Create a new, blank Google Sheet to serve as your pacing doc.
2. Rename the Google Sheet to “Pacing” or whatever you want to name it.
3. In the new Google Sheet, navigate to Extensions > Apps Script to access the scripting environment.

4. Copy and paste the pacing doc script into the script editor, then save your changes.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Pacing Sheet Setup")
    .addItem("Step 1 - Create Sheets", "createNewSheets")
    .addItem("Step 2 - Create the Instructions", "readMeInstructions")
    .addItem("Step 3 - Label Google Ads Data", "labelGadsData")
    .addItem("Step 4 - Label Facebook Ads Data", "labelFbadsData")
    .addItem("Step 5 - Create Pacing & Looker Studio Tables", "stepFive")
    .addToUi();
}
function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.insertSheet("Pacing");
  spreadsheet.insertSheet("Looker Budgets");
  spreadsheet.insertSheet("gads");
  spreadsheet.insertSheet("fbads");
  makeInstructions();
}

5. You’ll need to authorize the script to run. Follow the prompts to grant the necessary permissions to your Google account. (click save and try to run the onOpen function)

6. After authorizing the script run the onOpen function and head back to your Google Sheet. You should see a new menu item called “Pacing Sheet Setup” in the toolbar.

Getting Started

1. Add the next part of the script to the script editor, then save your changes. (alternatively copy the whole script at the bottom)

function readMeInstructions() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("[README]");
  var stepsAndInstructions = [
    {
      step: "Prereq 1",
      instruction:
        "Add the Power My Analytics Extension to your Google Sheets Account",
    },
    {
      step: "Prereq 2",
      instruction: "Login to your PMA Account through the extension",
    },
    {
      step: "Step 3",
      instruction:
        "Add Google Ads data to your gads tab. You can add Account ID, Campaign Name, Date, Cost, Impressions, Clicks, Conversions, & Conversions Value. Use the Pacing Sheet Setup Menu to Label the Google Ads data.",
    },
    {
      step: "Step 4",
      instruction:
        "Add Facebook Ads data to your fbads tab. You can add Account ID, Campaign Name, Date, Ad set name, Amount Spent, Impressions, Clicks (All), Website Purchases, & Website Purchase Value. Use the Pacing Sheet Setup Menu to Label the Facebook Ads data.",
    },
    {
      step: "Step 5",
      instruction:
        "Run step 5 in the menu to create pacing tab and looker studio tabs. Now rejoice",
    },
  ];
  var currentRow = 2;
  for (var i = 0; i < stepsAndInstructions.length; i++) {
    var step = stepsAndInstructions[i].step;
    var instruction = stepsAndInstructions[i].instruction;
    sheet.getRange("B" + currentRow).setValue(step);
    sheet.getRange("B" + (currentRow + 1)).setValue(instruction);
    currentRow += 3;
  }
}
function makeInstructions() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("Sheet1");
  if (sheet) {
    sheet.setName("[README]");
  } else {
    Logger.log("Sheet1 not found.");
  }
}

1. Click on the “Pacing Sheet Setup” menu item and select “Step 1 - Create Sheets” to create the necessary sheets for the pacing doc.

2. Click on the “Pacing Sheet Setup” menu item and select “Step 2 - Create the Instructions” to create the instructions sheet.

Install & Login to Power My Analytics

1. Click on the extensions menu and select “Get Add-ons” to open the add-ons store.

2. Search for “Power My Analytics” and click on the “Add” button to install the add-on.

3. Click on the extensions menu and select “Power My Analytics” to open the add-on.

4. Click on the “Login” button to login to your Power My Analytics account.

5. Click on the “Authorize” button to authorize the add-on to access your Google Sheets account.

6. Add your Google Ads and Facebook Ads data to the “gads” and “fbads” tabs.

Label Your Data Using Named Ranges

1. Add the next part of the script to the script editor, then save your changes. (alternatively copy the whole script at the bottom)

function labelGadsData() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("gads");
  var headersRange = sheet.getRange("2:2");
  var headers = headersRange.getValues()[0];
  var namedRanges = [
    { name: "gads_accid", colName: "Account ID" },
    { name: "gads_camp", colName: "Campaign Name" },
    { name: "gads_date", colName: "Date" },
    { name: "gads_cost", colName: "Cost" },
    { name: "gads_impr", colName: "Impressions" },
    { name: "gads_clicks", colName: "Clicks" },
    { name: "gads_conv", colName: "Conversions" },
    { name: "gads_rev", colName: "Conversions Value" },
  ];
  namedRanges.forEach(function (nr) {
    var colIndex = headers.indexOf(nr.colName);
    var range = sheet.getRange(3, colIndex + 1, 998, 1);
    spreadsheet.setNamedRange(nr.name, range);
  });
}
function labelFbadsData() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("fbads");
  var headersRange = sheet.getRange("2:2");
  var headers = headersRange.getValues()[0];
  var namedRanges = [
    { name: "fb_accid", colName: "Account ID" },
    { name: "fb_camp", colName: "Campaign Name" },
    { name: "fb_date", colName: "Date" },
    { name: "fb_adset", colName: "Ad Set name" },
    { name: "fb_cost", colName: "Amount Spent" },
    { name: "fb_impr", colName: "Impressions" },
    { name: "fb_clicks", colName: "Clicks (All)" },
    { name: "fb_conv", colName: "Website Purchases" },
    { name: "fb_rev", colName: "Website Purchases Conversion Value" },
  ];
  namedRanges.forEach(function (nr) {
    var colIndex = headers.indexOf(nr.colName);
    var range = sheet.getRange(3, colIndex + 1, 998, 1);
    spreadsheet.setNamedRange(nr.name, range);
  });
}

2. Click on the “Pacing Sheet Setup” menu item and select “Step 3 - Label Google Ads Data” to label the Google Ads data.

3. Click on the “Pacing Sheet Setup” menu item and select “Step 4 - Label Facebook Ads Data” to label the Facebook Ads data.

Create the Pacing & Looker Studio Tables

1. Add the next part of the script to the script editor, then save your changes. (alternatively copy the whole script at the bottom)

function stepFive() {
  createLookerData();
  createPacingTable();
}

function createPacingTable() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Pacing");
  var PacingHeaders = [
    "Channel",
    "Tactics",
    "Budget",
    "Spend",
    "Ideal Daily Budget",
    "Expected Spend",
    "% +/-",
  ];
  var range = sheet.getRange("B2:H2");
  range.setValues([PacingHeaders]);
  var channels = ["Paid Search", "Paid Social"];
  var numRows = channels.length;
  var startRow = 3;
  var startCol = 2;
  var channelRange = sheet.getRange(startRow, startCol, numRows, 1);
  channelRange.setValues(
    channels.map(function (channel) {
      return [channel];
    })
  );
  var totalsRow = ["Totals", "", "=SUM(D3:D4)", "=SUM(E3:E4)", "", "", ""];
  var totalRowStart = startRow + numRows;
  var totalRowRange = sheet.getRange(
    totalRowStart,
    startCol,
    1,
    PacingHeaders.length
  );
  totalRowRange.setValues([totalsRow]);
  var headerRow = sheet.getRange("B2:H2");
  headerRow.setBackground("#0b5394");
  headerRow.setFontColor("#FFFFFF");
  headerRow.setFontWeight("bold");
  var tableRange = sheet.getRange(
    startRow,
    startCol,
    numRows + 1,
    PacingHeaders.length
  );
  tableRange.setBorder(null, true, true, true, null, null);
  headerRow.setBorder(true, null, true, null, null, null);
  totalRowRange.setBorder(true, null, true, null, null, null);
  var totalsRange = sheet.getRange(
    totalRowStart,
    startCol,
    1,
    PacingHeaders.length
  );
  totalsRange.setBackground("#c9daf8");
  var dataRange = sheet.getRange(
    startRow - 1,
    startCol + 2,
    numRows + 2,
    PacingHeaders.length - 2
  );
  dataRange.setHorizontalAlignment("center");
  var spendCol = PacingHeaders.indexOf("Spend");
  var gadsSpendStartRow = startRow + channels.indexOf("Paid Search");
  var fbadsSpendStartRow = startRow + channels.indexOf("Paid Social");
  var gadsSpendFormula =
    '=SUMIFS(gads_cost,gads_date,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))';
  var fbadsSpendFormula =
    '=SUMIFS(fb_cost,fb_date,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))';
  var gadsSpendRange = sheet.getRange(gadsSpendStartRow, spendCol + 2);
  var fbadsSpendRange = sheet.getRange(fbadsSpendStartRow, spendCol + 2);
  gadsSpendRange.setFormula(gadsSpendFormula);
  fbadsSpendRange.setFormula(fbadsSpendFormula);

  var idealDailyBudgetCol = PacingHeaders.indexOf("Ideal Daily Budget");
  for (var i = startRow; i <= totalRowStart; i++) {
    var budgetRange = sheet.getRange(i, idealDailyBudgetCol + 2);
    var budgetFormula =
      "=iferror((D" +
      i +
      "-E" +
      i +
      ')/((DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,)))-(DAY(TODAY()-1))),"")';
    budgetRange.setFormula(budgetFormula);
    var expectedSpendRange = sheet.getRange(i, idealDailyBudgetCol + 3);
    var expectedSpendFormula =
      "=iferror(E" +
      i +
      '/(day(today()-1))*(DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))),"")';
    expectedSpendRange.setFormula(expectedSpendFormula);
    var percentChangeRange = sheet.getRange(i, idealDailyBudgetCol + 4);
    var percentChangeFormula =
      "=iferror((G" + i + "-D" + i + ")/D" + i + ',"")';
    percentChangeRange.setFormula(percentChangeFormula);
  }
  var currencyRange = sheet.getRange(
    startRow,
    startCol + 2,
    numRows + 2,
    PacingHeaders.length - 3
  );
  currencyRange.setNumberFormat("$#,##0;($#,##0)");
  var percentChangeCol = PacingHeaders.indexOf("% +/-");
  var percentChangeRange = sheet.getRange(
    startRow,
    percentChangeCol + 2,
    numRows + 1,
    1
  );
  percentChangeRange.setNumberFormat("0.0%");
}

function createLookerData() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Looker Budgets");
  var colHeaders = [
    "Channel",
    "Days Left in Month",
    "Budget",
    "Spend",
    "Target DRR",
    "Expected Spend",
    "% +/-",
    "3d Spend",
    "Yesterday Spend",
  ];
  var range = sheet.getRange("A1:I1");
  range.setValues([colHeaders]);
  var gadsRow = [
    "Google",
    "=eomonth(TODAY(),0)-TODAY()",
    "=Pacing!D3",
    "=Pacing!E3",
    "=Pacing!F3",
    "=Pacing!G3",
    "=Pacing!H3",
    '=IFERROR(SUMIFS(gads_cost,gads_date,">="&TODAY()-3),0)',
    '=IFERROR(SUMIFS(gads_cost,gads_date,">="&TODAY()-1),0)',
  ];
  var fbadsRow = [
    "Facebook",
    "=eomonth(TODAY(),0)-TODAY()",
    "=Pacing!D4",
    "=Pacing!E4",
    "=Pacing!F4",
    "=Pacing!G4",
    "=Pacing!H4",
    '=IFERROR(SUMIFS(fb_cost,fb_date,">="&TODAY()-3),0)',
    '=IFERROR(SUMIFS(fb_cost,fb_date,">="&TODAY()-1),0)',
  ];
  var gRange = sheet.getRange("A2:I2");
  var fbRange = sheet.getRange("A3:I3");
  gRange.setValues([gadsRow]);
  fbRange.setValues([fbadsRow]);
  range.setBackground("#2D4175");
  range.setFontColor("#FFFFFF");
  range.setFontWeight("bold");
  for (var i = 1; i <= colHeaders.length; i++) {
    sheet.autoResizeColumn(i);
  }
  for (var i = 1; i <= colHeaders.length; i++) {
    var columnWidth = sheet.getColumnWidth(i);
    if (columnWidth < 100) {
      sheet.setColumnWidth(i, 75);
    }
  }
}

2. Add your budgets to the Pacing sheet and enjoy!

Conclusion

In conclusion, the pacing doc and its accompanying automation script serve as game-changers in the world of digital marketing, allowing advertisers to effectively manage and monitor their clients’ ad budgets across Google and Facebook. By leveraging this powerful tool, you can optimize campaigns to maximize return on investment and stay on track to meet your clients’ goals.

The step-by-step guide provided in this blog post simplifies the process of creating a pacing doc in Google Sheets, enabling you to harness its full potential with ease. As you begin to utilize the pacing doc in your daily advertising efforts, you’ll soon realize its invaluable contribution to the success of your clients’ campaigns, and ultimately, to the growth of your business.

Copy The Entire Script

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Pacing Sheet Setup")
    .addItem("Step 1 - Create Sheets", "createNewSheets")
    .addItem("Step 2 - Create the Instructions", "readMeInstructions")
    .addItem("Step 3 - Label Google Ads Data", "labelGadsData")
    .addItem("Step 4 - Label Facebook Ads Data", "labelFbadsData")
    .addItem("Step 5 - Create Pacing & Looker Studio Tables", "stepFive")
    .addToUi();
}

function readMeInstructions() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("[README]");
  var stepsAndInstructions = [
    {
      step: "Prereq 1",
      instruction:
        "Add the Power My Analytics Extension to your Google Sheets Account",
    },
    {
      step: "Prereq 2",
      instruction: "Login to your PMA Account through the extension",
    },
    {
      step: "Step 3",
      instruction:
        "Add Google Ads data to your gads tab. You can add Account ID, Campaign Name, Date, Cost, Impressions, Clicks, Conversions, & Conversions Value. Use the Pacing Sheet Setup Menu to Label the Google Ads data.",
    },
    {
      step: "Step 4",
      instruction:
        "Add Facebook Ads data to your fbads tab. You can add Account ID, Campaign Name, Date, Ad set name, Amount Spent, Impressions, Clicks (All), Website Purchases, & Website Purchase Value. Use the Pacing Sheet Setup Menu to Label the Facebook Ads data.",
    },
    {
      step: "Step 5",
      instruction:
        "Run step 5 in the menu to create pacing tab and looker studio tabs. Now rejoice",
    },
  ];
  var currentRow = 2;
  for (var i = 0; i < stepsAndInstructions.length; i++) {
    var step = stepsAndInstructions[i].step;
    var instruction = stepsAndInstructions[i].instruction;
    sheet.getRange("B" + currentRow).setValue(step);
    sheet.getRange("B" + (currentRow + 1)).setValue(instruction);
    currentRow += 3;
  }
}

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.insertSheet("Pacing");
  spreadsheet.insertSheet("Looker Budgets");
  spreadsheet.insertSheet("gads");
  spreadsheet.insertSheet("fbads");
  makeInstructions();
}

function stepFive() {
  createLookerData();
  createPacingTable();
}

function createPacingTable() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Pacing");
  var PacingHeaders = [
    "Channel",
    "Tactics",
    "Budget",
    "Spend",
    "Ideal Daily Budget",
    "Expected Spend",
    "% +/-",
  ];
  var range = sheet.getRange("B2:H2");
  range.setValues([PacingHeaders]);
  var channels = ["Paid Search", "Paid Social"];
  var numRows = channels.length;
  var startRow = 3;
  var startCol = 2;
  var channelRange = sheet.getRange(startRow, startCol, numRows, 1);
  channelRange.setValues(
    channels.map(function (channel) {
      return [channel];
    })
  );
  var totalsRow = ["Totals", "", "=SUM(D3:D4)", "=SUM(E3:E4)", "", "", ""];
  var totalRowStart = startRow + numRows;
  var totalRowRange = sheet.getRange(
    totalRowStart,
    startCol,
    1,
    PacingHeaders.length
  );
  totalRowRange.setValues([totalsRow]);
  var headerRow = sheet.getRange("B2:H2");
  headerRow.setBackground("#0b5394");
  headerRow.setFontColor("#FFFFFF");
  headerRow.setFontWeight("bold");
  var tableRange = sheet.getRange(
    startRow,
    startCol,
    numRows + 1,
    PacingHeaders.length
  );
  tableRange.setBorder(null, true, true, true, null, null);
  headerRow.setBorder(true, null, true, null, null, null);
  totalRowRange.setBorder(true, null, true, null, null, null);
  var totalsRange = sheet.getRange(
    totalRowStart,
    startCol,
    1,
    PacingHeaders.length
  );
  totalsRange.setBackground("#c9daf8");
  var dataRange = sheet.getRange(
    startRow - 1,
    startCol + 2,
    numRows + 2,
    PacingHeaders.length - 2
  );
  dataRange.setHorizontalAlignment("center");
  var spendCol = PacingHeaders.indexOf("Spend");
  var gadsSpendStartRow = startRow + channels.indexOf("Paid Search");
  var fbadsSpendStartRow = startRow + channels.indexOf("Paid Social");
  var gadsSpendFormula =
    '=SUMIFS(gads_cost,gads_date,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))';
  var fbadsSpendFormula =
    '=SUMIFS(fb_cost,fb_date,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))';
  var gadsSpendRange = sheet.getRange(gadsSpendStartRow, spendCol + 2);
  var fbadsSpendRange = sheet.getRange(fbadsSpendStartRow, spendCol + 2);
  gadsSpendRange.setFormula(gadsSpendFormula);
  fbadsSpendRange.setFormula(fbadsSpendFormula);

  var idealDailyBudgetCol = PacingHeaders.indexOf("Ideal Daily Budget");
  for (var i = startRow; i <= totalRowStart; i++) {
    var budgetRange = sheet.getRange(i, idealDailyBudgetCol + 2);
    var budgetFormula =
      "=iferror((D" +
      i +
      "-E" +
      i +
      ')/((DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,)))-(DAY(TODAY()-1))),"")';
    budgetRange.setFormula(budgetFormula);
    var expectedSpendRange = sheet.getRange(i, idealDailyBudgetCol + 3);
    var expectedSpendFormula =
      "=iferror(E" +
      i +
      '/(day(today()-1))*(DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))),"")';
    expectedSpendRange.setFormula(expectedSpendFormula);
    var percentChangeRange = sheet.getRange(i, idealDailyBudgetCol + 4);
    var percentChangeFormula =
      "=iferror((G" + i + "-D" + i + ")/D" + i + ',"")';
    percentChangeRange.setFormula(percentChangeFormula);
  }
  var currencyRange = sheet.getRange(
    startRow,
    startCol + 2,
    numRows + 2,
    PacingHeaders.length - 3
  );
  currencyRange.setNumberFormat("$#,##0;($#,##0)");
  var percentChangeCol = PacingHeaders.indexOf("% +/-");
  var percentChangeRange = sheet.getRange(
    startRow,
    percentChangeCol + 2,
    numRows + 1,
    1
  );
  percentChangeRange.setNumberFormat("0.0%");
}

function createLookerData() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Looker Budgets");
  var colHeaders = [
    "Channel",
    "Days Left in Month",
    "Budget",
    "Spend",
    "Target DRR",
    "Expected Spend",
    "% +/-",
    "3d Spend",
    "Yesterday Spend",
  ];
  var range = sheet.getRange("A1:I1");
  range.setValues([colHeaders]);
  var gadsRow = [
    "Google",
    "=eomonth(TODAY(),0)-TODAY()",
    "=Pacing!D3",
    "=Pacing!E3",
    "=Pacing!F3",
    "=Pacing!G3",
    "=Pacing!H3",
    '=IFERROR(SUMIFS(gads_cost,gads_date,">="&TODAY()-3),0)',
    '=IFERROR(SUMIFS(gads_cost,gads_date,">="&TODAY()-1),0)',
  ];
  var fbadsRow = [
    "Facebook",
    "=eomonth(TODAY(),0)-TODAY()",
    "=Pacing!D4",
    "=Pacing!E4",
    "=Pacing!F4",
    "=Pacing!G4",
    "=Pacing!H4",
    '=IFERROR(SUMIFS(fb_cost,fb_date,">="&TODAY()-3),0)',
    '=IFERROR(SUMIFS(fb_cost,fb_date,">="&TODAY()-1),0)',
  ];
  var gRange = sheet.getRange("A2:I2");
  var fbRange = sheet.getRange("A3:I3");
  gRange.setValues([gadsRow]);
  fbRange.setValues([fbadsRow]);
  range.setBackground("#2D4175");
  range.setFontColor("#FFFFFF");
  range.setFontWeight("bold");
  for (var i = 1; i <= colHeaders.length; i++) {
    sheet.autoResizeColumn(i);
  }
  for (var i = 1; i <= colHeaders.length; i++) {
    var columnWidth = sheet.getColumnWidth(i);
    if (columnWidth < 100) {
      sheet.setColumnWidth(i, 75);
    }
  }
}

function labelGadsData() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("gads");
  var headersRange = sheet.getRange("2:2");
  var headers = headersRange.getValues()[0];
  var namedRanges = [
    { name: "gads_accid", colName: "Account ID" },
    { name: "gads_camp", colName: "Campaign Name" },
    { name: "gads_date", colName: "Date" },
    { name: "gads_cost", colName: "Cost" },
    { name: "gads_impr", colName: "Impressions" },
    { name: "gads_clicks", colName: "Clicks" },
    { name: "gads_conv", colName: "Conversions" },
    { name: "gads_rev", colName: "Conversions Value" },
  ];
  namedRanges.forEach(function (nr) {
    var colIndex = headers.indexOf(nr.colName);
    var range = sheet.getRange(3, colIndex + 1, 998, 1);
    spreadsheet.setNamedRange(nr.name, range);
  });
}

function labelFbadsData() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("fbads");
  var headersRange = sheet.getRange("2:2");
  var headers = headersRange.getValues()[0];
  var namedRanges = [
    { name: "fb_accid", colName: "Account ID" },
    { name: "fb_camp", colName: "Campaign Name" },
    { name: "fb_date", colName: "Date" },
    { name: "fb_adset", colName: "Ad Set name" },
    { name: "fb_cost", colName: "Amount Spent" },
    { name: "fb_impr", colName: "Impressions" },
    { name: "fb_clicks", colName: "Clicks (All)" },
    { name: "fb_conv", colName: "Website Purchases" },
    { name: "fb_rev", colName: "Website Purchases Conversion Value" },
  ];
  namedRanges.forEach(function (nr) {
    var colIndex = headers.indexOf(nr.colName);
    var range = sheet.getRange(3, colIndex + 1, 998, 1);
    spreadsheet.setNamedRange(nr.name, range);
  });
}

function makeInstructions() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName("Sheet1");
  if (sheet) {
    sheet.setName("[README]");
  } else {
    Logger.log("Sheet1 not found.");
  }
}