AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Ads
4.4K members online now
4.4K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

Create Adwords ads from a spreadsheet

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I have a spreadhsheet with 20 rows and I want to iterate through the spreadhseet to create ads in Adwords.

 

With this code I create 1 ad without any probem:

 

function main() {
  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx/edit#gid=0";
  var accountName = AdWordsApp.currentAccount().getName();
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheets = spreadsheet.getSheets();

  var sheet = sheets[0];
  var url = sheet.getRange('A2').getValue();
  var headlineOne = sheet.getRange('B2').getValue();
  var headlineTwo = sheet.getRange('D2').getValue();
  var description = sheet.getRange('E2').getValue();

  var adGroupIterator = AdWordsApp.adGroups()
    .withCondition('Name = "xxxxxxx"')
    .get();
  if (adGroupIterator.hasNext()) {
    adGroup = adGroupIterator.next();

    adGroup.newAd().expandedTextAdBuilder()
      .withHeadlinePart1(headlineOne)
      .withHeadlinePart2(headlineTwo)
      .withDescription(description)
      .withFinalUrl(url)
      .build();
  }
}

 

But when I try to iterate through my spreadsheet, it doesn't work:

 

function main() {
  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=0";
  var accountName = AdWordsApp.currentAccount().getName();
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheets = spreadsheet.getSheets();

  var sheet = sheets[0];
  var url = sheet.getRange('A1:A20').getValues();
  var headlineOne = sheet.getRange('B1:B20').getValues();
  var headlineTwo = sheet.getRange('D1:D20').getValues();
  var description = sheet.getRange('E1:E20').getValues();

  var adGroupIterator = AdWordsApp.adGroups()
    .withCondition('Name = "xxxxxx"')
    .get();
  if (adGroupIterator.hasNext()) {
    adGroup = adGroupIterator.next();

    for (var row = 0; row < 20; row++) {

      adGroup.newAd().expandedTextAdBuilder()
        .withHeadlinePart1(headlineOne[row])
        .withHeadlinePart2(headlineTwo[row])
        .withDescription(description[row])
        .withFinalUrl(url[row])
        .build();
  }
}

In the logs I have "Invalid argument: finalUrl. Should be of type: string". Any idea?

1 Expert replyverified_user

Re: Create Adwords ads from a spreadsheet

Top Contributor
# 2
Top Contributor

Hey,

 

I have a couple ideas, maybe you have tried them already:

 

- add a line of code formatting the spreadsheet URL data  to plain text underneath this line:

var description = sheet.getRange('E1:E20').getValues();"

Code to Add:

url.setNumberFormat('@');

 

-Add quotation marks around the final URL in the spreadsheet

 

Hope that helps. You may possibly need to try using "setNumberFormats" instead

Jim Vaillancourt, AdWords Top Contributor, LinkedIn
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Create Adwords ads from a spreadsheet

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Jim,

 

Thanks for the input but adding quotation marks doesn't help and when using your suggestion I get "TypeError: Cannot find function setNumberFormat in object", same with setNumberFormats