Running AdWords Scripts From gdrive

[Dean Yates]

Hi all, 

I would like to run a number of AdWords scripts externally in Google Drive. I have attempted the 'http://www.freeadwordsscripts.com/2013/10/use-gdrive-to-load-single-adwords.html' method with no luck. Does anybody have a quick/easy method for this? 

Thank you,

Dean

[Tyler Sidell (AdWords Scripts Team)]

Hi Dean, 

You should be able to accomplish this through App Scripts.  I'd suggest reaching out to their product support team.

What issues are you running into with the FreeAdWordsScripts solution?  You may want to reach out to their team as well.

Thanks,
Tyler Sidell

AdWords Scripts Team

[Argyris Kaintaris Ravanis]

Hello Dean,

I have used gdrive to load scripts in the past extensively.

Where did you encountered an error? It might be a drive error, rather than a coding one.

Best Regards,

Argi

[Dean Yates]

Morning Argyris, 

The error has probably occurred due to my lack of technical knowledge in this area! Please see below for error message I receive when I attempt to surround the entire script with the function call. Updating main method to be a public method appears to be fine.

[Tyler Sidell (AdWords Scripts Team)]

Hi Dean,

This is an error originating from the Drive side.  However, you are getting this error due to the hyphen in your function name.  If you remove the hyphen the error should disappear.

Thanks,

Tyler Sidell
AdWords Scripts Team

[Dean Yates]

Hi Tyler, 

Thanks, the error did disappear and unearthed various other errors. In AdWords I receive this;

This is the script as it stands: 

function URLCheck() {

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1GdRbauPVutKNXy5N0UJxRNkoCBZ8pu5xj771CH4niKA/edit#gid=0';

var LABEL_NAME = 'URL Checked MM';

var shelper = new SHelper();

var badUrls = 0;

}

this.main = function() {

  dealWithFirstRunOfTheDay();

  if (shelper.config.email.length == 0 &&

      shelper.config.emailPreference != 'Never') {

    Logger.log('WARNING: no email specified, proceeding...');

  }

  if (!shelper.config.checkAds && !shelper.config.checkKeywords) {

    Logger.log('WARNING: requested no keywords and no ads checking. Exiting.');

    return;

  }

  createLinkCheckerLabel();

  var anythingChanged = shelper.config.checkKeywords &&

      checkKeywordUrls(AdWordsApp.keywords());

  anythingChanged = (shelper.config.checkAds &&

      checkAdUrls(AdWordsApp.ads())) || anythingChanged;

  if (anythingChanged) {

    shelper.flush();

    if (badUrls > 0 && shelper.config.email.length > 0 &&

        shelper.config.emailPreference == 'As soon as an error is discovered') {

      var bad = shelper.spreadsheet.getRangeByName('bad').getValue();

      var good = shelper.spreadsheet.getRangeByName('good').getValue();

      sendReportWithErrors(good, bad);

    }

  } else {

    shelper.spreadsheet.getRangeByName('finished').setValue(

        'All done for the day!');

  }

}

function dealWithFirstRunOfTheDay() {

  var date = new Date();

  var lastCheckDate = shelper.dataSheet.getRange(1, 3).getValue();

  if (lastCheckDate.length == 0 || date.getYear() != lastCheckDate.getYear() ||

      date.getMonth() != lastCheckDate.getMonth() ||

      date.getDay() != lastCheckDate.getDay()) {

    // kill the label.

    var labels = AdWordsApp.labels().withCondition(

        "Name='" + LABEL_NAME + "'").get();

    if (labels.hasNext()) {

      labels.next().remove();

    }

    // send out yesterday's report

    if (shelper.config.email.length > 0 &&

        (shelper.config.emailPreference == 'Once a day' ||

        shelper.config.emailPreference == 'Once a day if there are errors')) {

      var bad = shelper.spreadsheet.getRangeByName('bad').getValue();

      var good = shelper.spreadsheet.getRangeByName('good').getValue();

      if (shelper.config.emailPreference == 'Once a day') {

        if (bad == 0) {

          MailApp.sendEmail(shelper.config.email,

              'AdWords Link Checker verified ' + good +

              ' URLs on account ' +

              AdWordsApp.currentAccount().getCustomerId() +

              ', all looking good!', '');

        } else {

          sendReportWithErrors(good, bad);

        }

      } else if (shelper.config.emailPreference ==

          'Once a day if there are errors' && bad > 0) {

        sendReportWithErrors(good, bad);

      }

    }

    // reset the spreadsheet

    shelper.spreadsheet.getRangeByName('account_id_dashboard').setValue(

        AdWordsApp.currentAccount().getCustomerId());

    shelper.spreadsheet.getRangeByName('account_id_report').setValue(

        AdWordsApp.currentAccount().getCustomerId());

    shelper.spreadsheet.getRangeByName('date').setValue(date);

    shelper.spreadsheet.getRangeByName('finished').setValue(

        'Checking links...');

    shelper.dataSheet.getRange(

        4, 1, shelper.dataSheet.getMaxRows() - 3, 6).clear();

  }

}

function sendReportWithErrors(good, bad) {

  var emailBody = [];

  emailBody.push('Summary for account ' +

      AdWordsApp.currentAccount().getCustomerId() +

      ': ' + good + ' good URLs, ' + bad + ' bad ones\n');

  emailBody.push('Full report available at ' + shelper.spreadsheet.getUrl() +

      '\n');

  shelper.reset();

  var row = shelper.readRow();

  while (row != null && emailBody.length < 200) {

    if (row[1] >= 300) {

      var entityType = row[4].length > 0 ? 'Keyword: ' : 'Ad: ';

      var entityText = row[4].length > 0 ? row[4] : row[5];

      emailBody.push('Campaign: ' + row[2] + ', Ad Group: ' + row[3] + ', ' +

          entityType + entityText);

      emailBody.push(row[0] + ' - ' + row[1] + ' response code.\n');

    }

    row = shelper.readRow();

  }

  if (emailBody.length >= 200) {

    emailBody.push('Further URLs omitted. Check the report at ' +

        shelper.spreadsheet.getUrl());

  }

  shelper.reset();

  MailApp.sendEmail(shelper.config.email,

    'AdWords Link Checker verified found ' + bad +

    ' bad URLs on account ' + AdWordsApp.currentAccount().getCustomerId() + '',

    emailBody.join('\n'));

}

function checkAdUrls(selector) {

  var iterator = selector

      .withCondition('CreativeFinalUrls STARTS_WITH_IGNORE_CASE "h"')

      .withCondition('Status IN [ENABLED]')

      .withCondition('CampaignStatus IN [ENABLED]')

      .withCondition('AdGroupStatus IN [ENABLED]')

      .withCondition('LabelNames CONTAINS_NONE ["' + LABEL_NAME + '"]')

      .withLimit(800)

      .get();

  return checkUrls(iterator);

}

function checkKeywordUrls(selector) {

  var iterator = selector

      .withCondition('FinalUrls STARTS_WITH_IGNORE_CASE "h"')

      .withCondition('Status IN [ENABLED]')

      .withCondition('CampaignStatus IN [ENABLED]')

      .withCondition('AdGroupStatus IN [ENABLED]')

      .withCondition('LabelNames CONTAINS_NONE ["' + LABEL_NAME + '"]')

      .withLimit(800)

      .get();

  return checkUrls(iterator);

}

function checkUrls(iterator) {

  if (!iterator.hasNext()) {

    return false;

  }

  var urlMap = {};

  while (iterator.hasNext()) {

    var entity = iterator.next();

    var urls = [entity.urls().getFinalUrl(), entity.urls().getMobileFinalUrl()];

    for (var i = 0; i < urls.length; i++) {

      if (urls[i] == null) {

        continue;

      }

      var lastUrl = encodeURI(urls[i]);

      if (lastUrl in urlMap) {

        continue;

      }

      urlMap[lastUrl] = true;

      var now = new Date().getTime();

      var responseCode = 0;

      try {

        var response = UrlFetchApp.fetch(lastUrl, {muteHttpExceptions: true});

        responseCode = response.getResponseCode();

      } catch (e) {

        // Something went wrong. Since this a script error, let's mark it as

        // 500.

        Logger.log('Could not fetch %s due to an internal error : "%s". ' +

            'Marking this URL as failed, with an error code 500.', lastUrl, e);

        responseCode = 500;

      }

      var then = new Date().getTime();

      Utilities.sleep(then - now);

      if (responseCode < 300) {

        shelper.writeRow(lastUrl, responseCode);

      } else {

        badUrls++;

        if (typeof(entity['getHeadline']) != 'undefined') {

          var adText = entity.getType() == 'TEXT_AD' ?

            entity.getHeadline() + '\n' + entity.getDescription1() + '\n' +

            entity.getDescription2() : entity.getType();

          shelper.writeRow(lastUrl, responseCode,

                           entity.getCampaign().getName(),

                           entity.getAdGroup().getName(),

                           null, adText);

        } else {

          shelper.writeRow(lastUrl, responseCode,

                           entity.getCampaign().getName(),

                           entity.getAdGroup().getName(),

                           entity.getText());

        }

      }

    }

    entity.applyLabel(LABEL_NAME);

  }

  return true;

}

function createLinkCheckerLabel() {

  var labels = AdWordsApp.labels().withCondition(

      "Name='" + LABEL_NAME + "'").get();

  if (!labels.hasNext()) {

    AdWordsApp.createLabel(LABEL_NAME,

        "URL Health Check Carried Out & Report Compiled", '#60e020');

  }

}

// Spreadsheet helper

function SHelper() {

  this.MAX_ROWS = 20000;

  this.BATCH_SIZE = 50;

  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);

  this.spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  this.dataSheet = this.spreadsheet.getSheets()[1];

  this.config = {

    checkAds: this.spreadsheet.getRangeByName('check_ads').getValue() == 'Yes',

    checkKeywords: this.spreadsheet.getRangeByName('check_keywords').

        getValue() == 'Yes',

    email: this.spreadsheet.getRangeByName('email_address').getValue(),

    emailPreference: this.spreadsheet.getRangeByName('email_preference').

        getValue()

  };

  this.globalRow = 4;

  this.cells = null;

  this.localRow = 0;

  this.reset = function() {

    this.globalRow = 4;

    this.cells = null;

    this.localRow = 0;

  };

  this.readRow = function() {

    initCells(this);

    if (this.localRow == this.cells.length) {

      this.globalRow += this.cells.length;

      if (this.globalRow >= this.dataSheet.getMaxRows()) {

        return null;

      }

      this.cells = this.dataSheet.getRange(

          this.globalRow, 2, this.BATCH_SIZE, 6).getValues();

      this.localRow = 0;

    }

    if (this.cells[this.localRow][0].length > 0) {

      return this.cells[this.localRow++];

    } else {

      return null;

    }

  };

  this.writeRow = function() {

    fetchCells(this);

    for (var i = 0; i < arguments.length; i++) {

      this.cells[this.localRow][i] = arguments[i];

    }

  };

  this.flush = function() {

    if (this.cells) {

      this.dataSheet.getRange(this.globalRow, 2, this.cells.length, 6).

          setValues(this.cells);

      this.dataSheet.getRange(1, 1).copyFormatToRange(

          this.dataSheet,

          3,

          3,

          this.globalRow,

          this.globalRow + this.cells.length);

    }

  };

  function initCells(instance) {

    if (instance.cells == null) {

      instance.globalRow = 4;

      instance.cells = instance.dataSheet.getRange(

          instance.globalRow, 2, instance.BATCH_SIZE, 6).getValues();

      instance.localRow = 0;

    }

  }

  function fetchCells(instance) {

    initCells(instance);

    while (!findEmptyRow(instance) && instance.globalRow < instance.MAX_ROWS) {

      if (instance.dataSheet.getMaxRows() <

          instance.globalRow + this.BATCH_SIZE) {

        instance.dataSheet.insertRowsAfter(

            instance.dataSheet.getMaxRows(), instance.BATCH_SIZE);

      }

      instance.flush();

      instance.globalRow += instance.cells.length;

      instance.cells = instance.dataSheet.getRange(

          instance.globalRow, 2, instance.BATCH_SIZE, 6).getValues();

      instance.localRow = 0;

    }

    if (instance.globalRow >= instance.MAX_ROWS) {

      Logger.log('WARNING: maximum length of the spreadsheet exceeded. ' +

          'Exiting.');

      throw '';

    }

  }

  function findEmptyRow(instance) {

    for (; instance.localRow < instance.cells.length &&

        !(instance.cells[instance.localRow][0] == null ||

        instance.cells[instance.localRow][0].length == 0); instance.localRow++);

    return instance.localRow < instance.cells.length;

  }

}

and the script config spreadsheet is set up as below (The script location is the destination of the folder the script is contained in - I have tried both folder and actual script address);

What's going wrong? 

TIA 

Dean

[Argyris Kaintaris Ravanis]

Hello Dean,

In "AdWords Scripts Config" you should specify the absolute "address" of the script in gDrive.

This means that if the script is nested in 3 folders, the path should be like:

FirstFolder/SecondFolder/ThirdFolder/ScriptName.js

Note:

If the owner of the script is another account than the one running it you should share the hierarchy.

Also it should be moved to "My Drive" from "Shared With Me" in the runners gDrive.

Best Regards,

Argi

[Dean Yates]

Hi Argi, 

Might be a silly question but how do I specify the absolute 'address'? 

This is the location; 

First folder - https://drive.google.com/drive/folders/0B59DywvVf9PvTUVVTHVsLWxKVzg

Script 'address' - https://script.google.com/d/1ooMY2cGwWJurtdeBSSoO1SWpdGooBTxU01N4PJbbOxGux-xECkH-qI-D/edit?usp=drive_web

The script is currently in a google apps scripts file - Is that OK? 

Thanks,

Dean

[Argyrios Kaintaris Ravanis]

Hello Dean,

The attached image is a screenshot of a drive showing a file within 2 folders.
The path to this file is "My Drive/AdWords/Scripts/Scriptception".

Russ in his code requires that "My Drive/" is omitted, nevertheless the first folder should be in My Drive.
(in this case it is the 'AdWords' folder)

So in this case if I wanted to run Scriptception I would set "AdWords/Scripts/Scriptception" as the location of the file.

I don't have experience with app script files, since i use an external application to code.
However if you encounter any problem reading the file, copy the code, paste it to a .txt file in your Desktop and upload it to gDrive.

Also here is an explanation about paths from wikipedia.

Best Regards,
Argi

[Dean Yates]

All sorted - Thank you very much Argi!

Enjoy your weekend, 

Dean

[Dean Yates]

Hi Tyler, 

I have the script running but I am seeing this error in the logs 

Any ideas? 

Kind regards,

Dean

On Tuesday, 24 May 2016 15:53:41 UTC+1, Tyler Sidell (AdWords Scripts Team) wrote:

[Jaren Callo (AdWords Scripts Team)]

Hi Dean,

Can you try the solution as suggested to this post? Since you are running an external script, AdWords Script has no way to provide authorization.

Thanks,

Jaren P. Callo

AdWords Scripts Team

[nic pederson]

Dean, how did you sort it? I'm having the same issue.

[Google Ads Scripts Forum Advisor]

Hi Nic,

Thanks for your reply.

Since this is already an old forum thread, could you create a new forum post for your concern? Please provide the details of your concern in the new forum post so that our team could better track it.

Regards,
Ejay
Google Ads Scripts Team

ref:_00D1U1174p._5001UODt0T:ref

[nic pederson]

Hi Ejay! Thanks for the reply. I've created a new post here: https://groups.google.com/forum/?hl=en-gb#!topic/adwords-scripts/9rh7OEkqhWA

I look forward to your feedback.

Nic

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/CiZxC000000000000000000000000000000000000000000000Q212WC00S0RCNF2JT3eHovsAeXhkAQ%40sfdc.net.