Monday, July 28, 2014

Save a File or Spreadsheet in a Specific Folder of GDrive

Here is a quick code snippet to help you understand how to save a spreadsheet into a specific folder on Google drive.

In order to create a file in a folder, you will need to find the parent folder and pass it to the DriveApp when you create the file. Normally, file paths are in the form of "/path/to/file/file.js", so it makes sense to keep that same construct when saving files to GDrive. The following code accepts the full file path and creates a new file in that folder.

The next obvious question is how to create a spreadsheet. Unfortunately, you can't create it using the DriveApp, you need to use the SpreadsheetApp. Also, since the spreadsheet app cannot create files in a folder, you will need to create the spreadsheet, then move it to the correct folder. I usually create a separate function with similar logic to find the correct folder, then update the create logic to use SpreadsheetApp instead of DriveApp. See the second function for an example.

Thanks,
Russ

/******************************************
* Create File in a Specific Folder Path
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function createFile(filePath) {
  var pathArray = filePath.trim().split('/');
  var folder;
  for(var i in pathArray) {
    var parentName = pathArray[i];
    if(!parentName || parentName === '') { continue; } // in the root folder
    // source: http://goo.gl/P0LQ86
    if(filePath.indexOf(parentName, filePath.length - parentName.length) !== -1) {
      // This is creating the actual file
      if(folder) {
        if(folder.getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          return folder.getFilesByName(parentName).next();
        }
        return folder.createFile(parentName,'');
      } else {
        if(DriveApp.getRootFolder().getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          return DriveApp.getRootFolder().getFilesByName(parentName).next();
        }
        return DriveApp.getRootFolder().createFile(parentName,'');
      }
    }
    if(!folder && !DriveApp.getRootFolder().getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = DriveApp.getRootFolder().createFolder(parentName);
    } else if(folder && !folder.getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = folder.createFolder(parentName);
    } else {
      Logger.log('Using existing folder: '+parentName);
      folder = (!folder) ? DriveApp.getRootFolder().getFoldersByName(parentName).next() 
                         : folder.getFoldersByName(parentName).next();
    }
  }
  // Should never get here
  throw "Invalid file path: "+filePath;
}
And to create a spreadsheet:
/******************************************
* Create Spreadsheet in a Specific Folder Path
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function createSpreadsheet(filePath) {
  var pathArray = filePath.trim().split('/');
  var folder;
  for(var i in pathArray) {
    var parentName = pathArray[i];
    if(!parentName || parentName === '') { continue; } // in the root folder
    // source: http://goo.gl/P0LQ86
    if(filePath.indexOf(parentName, filePath.length - parentName.length) !== -1) {
      // This is creating the actual file
      if(folder) {
        if(folder.getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          var file = folder.getFilesByName(parentName).next();
          return SpreadsheetApp.openById(file.getId());
        }
        // creates in folder
        var spreadsheet = SpreadsheetApp.create(parentName);
        var file = DriveApp.getRootFolder().getFilesByName(parentName).next();
        folder.addFile(file);
        DriveApp.getRootFolder().removeFile(file);
        return spreadsheet;
      } else {
        if(DriveApp.getRootFolder().getFilesByName(parentName).hasNext()) {
          Logger.log('Using existing file: '+parentName);
          var file = DriveApp.getRootFolder().getFilesByName(parentName).next();
          return SpreadsheetApp.openById(file.getId());
        }
        // creates in root folder
        return SpreadsheetApp.create(parentName);
      }
    }
    if(!folder && !DriveApp.getRootFolder().getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = DriveApp.getRootFolder().createFolder(parentName);
    } else if(folder && !folder.getFoldersByName(parentName).hasNext()) {
      Logger.log('Creating folder: '+parentName);
      folder = folder.createFolder(parentName);
    } else {
      Logger.log('Using existing folder: '+parentName);
      folder = (!folder) ? DriveApp.getRootFolder().getFoldersByName(parentName).next() 
                         : folder.getFoldersByName(parentName).next();
    }
  }
  // Should never get here
  throw "Invalid file path: "+filePath;
}

Sunday, July 27, 2014

Exception Invalid reporting query INVALID_PREDICATE_ENUM_VALUE ACTIVE

UPDATE: Here is a note from the AdWords Scripts team on the topic.

A few days ago, I started getting a bunch of errors from my scripts that looked like this:
Exception: Invalid reporting query: INVALID_PREDICATE_ENUM_VALUE: ACTIVE

If anyone else is seeing this issue, it has to do with an update to the reporting API. Previously, ACTIVE used to be a valid enum value for CampaignStatus, which was always sort of an anomaly since everything else used ENABLED. It has been updated to match the other Status values but in the process, broke any script that was using the ACTIVE status. Here is an example:
    ...
    var query = ['select',cols.join(','),'from',report,
                 'where CampaignStatus = ACTIVE',
                 'and AdGroupStatus = ENABLED',
                 'and Status = ACTIVE',
                 'during','TODAY'].join(' ');
    ...
This should be changed to:
    ...
    var query = ['select',cols.join(','),'from',report,
                 'where CampaignStatus = ENABLED',
                 'and AdGroupStatus = ENABLED',
                 'and Status = ENABLED',
                 'during','TODAY'].join(' ');
    ...

I am trying to go back and update my previous scripts but it might take some time.
Thanks,
Russ

Thursday, June 19, 2014

Calling All AdWords Scripts Developers

I was thinking of putting together a directory for any developers or companies out there that are currently writing Adwords Scripts for clients and would be interested in being contacted. I figured a picture or logo, name, short bio, location, and website would be a good start for this. If you are interested in being listed, please fill out this form and I will add you to the directory that is linked in the sidebar.

Thanks,
Russ