Script Download - Optmyzr AdWords Solutions and Tools

New to scripts ?
See step by step instructions for running AdWords Scripts in general.
Need Assistance? We are here to help! Contact Us

Script Code

Copy script code into your Clipboard.

// Copyright 2015 - Optmyzr Inc.
// All rights reserved
// For an Enhanced Script version of this script which works at the MCC level and is easier to modify, visit www.optmyzr.com

// FUNCTION: writeReportToGoogleSheet
  function writeReportToGoogleSheet(report, sheet, columnsUsedArray, overwrite, numRowsToWritePerCycle) {
    var rowCounter = 0;
    var dataToWrite = new Array();
    var dataAdded = 0;
   
    // Write Header
    if(overwrite) {
      var toWrite = new Array();
      toWrite.push(columnsUsedArray);
      var range = sheet.getRange(1, 1, toWrite.length, toWrite[0].length);
      range.setValues(toWrite);
    }
    
    // Write Data
    var reportIterator = report.rows();
    while(reportIterator.hasNext()){
      var row = reportIterator.next();
      var rowArray = new Array();
      //Logger.log("row.length: " + row.length);
      for(var i = 0; i < columnsUsedArray.length; i++){
        var key = columnsUsedArray[i];
        var data = row[key];
        //Logger.log("data: " + data);
        rowArray.push(data);
      }
      dataToWrite.push(rowArray);
      rowCounter++;
      
      if(rowCounter % numRowsToWritePerCycle == 0 || !reportIterator.hasNext()) {
        dataAdded = 1;
        Logger.log("write to sheet");
        if(!overwrite || dataAdded) {
          var startRow = sheet.getLastRow();
          var startColumn = 0; //sheet.getLastColumn();
        } else {
          var startRow = 1; // accounts for 1 row of headers
          var startColumn = 0;
        }
        var maxRows = sheet.getMaxRows();
        var maxColumns = sheet.getMaxColumns();
        Logger.log("startRow: " + startRow + " startColumn: " + startColumn + " dataToWrite.length: " + dataToWrite.length + " dataToWrite[0].length: " + dataToWrite[0].length);
        var range = sheet.getRange(startRow+1, startColumn+1, dataToWrite.length, dataToWrite[0].length);
        range.setValues(dataToWrite);
        dataToWrite = new Array();
      }
      
    } 
  }

// FUNCTION: prepareSpreadsheet
  function prepareSpreadsheet(spreadsheetName, spreadsheetUrl, spreadsheetTabName, accountManagers, overwrite)
  {
    // Error Checks
    if(spreadsheetUrl.toLowerCase().indexOf("new") == -1 && !spreadsheetTabName) {
      Logger.log("When using an existing spreadsheet for a report, you must also specify the name of the tab that will get the new data. We're starting a new spreadsheet for this report.");
      spreadsheetUrl = "new";
    }
    
    // Create the new spreadsheet
    Logger.log("spreadsheetTabName: " + spreadsheetTabName);
    Logger.log("currentSetting.spreadsheetUrl: " + spreadsheetUrl);
    var newSpreadsheetCreated = 0;
    if(spreadsheetUrl.toLowerCase().indexOf("new") != -1)
    {
      var spreadsheet = SpreadsheetApp.create(spreadsheetName);
      var spreadsheetUrl = spreadsheet.getUrl();
     
      //get all sheets except first and delete them and insert new sheets every time to avoid name error
      var allSheets = spreadsheet.getSheets();
      for(var i=1,len=allSheets.length;i<len;i++){
        spreadsheet.deleteSheet(allSheets[i]);
      }
      
      newSpreadsheetCreated = 1;
    } 
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    
    // Add Editors
    /*
    if(currentSetting.accountManagers && currentSetting.accountManagers!=""){
      currentSetting.accountManagersArray = currentSetting.accountManagers.replace(/\s/g, "").split(",");
      spreadsheet.addEditors(currentSetting.accountManagersArray);
    }
    */
    // Add and select the tab
    if(spreadsheetTabName) {
      if(newSpreadsheetCreated) {
        spreadsheet.insertSheet(spreadsheetTabName);
      } 
      var sheet = spreadsheet.getSheetByName(spreadsheetTabName);
    } else {
      var sheet = spreadsheet.getActiveSheet();
    }
    
    if(overwrite) {
      sheet.clear().clearFormats().clearNotes();
    }
    
    // return the selected sheet
    return(sheet);
  
  }

Date.prototype.yyyymmdd = function() {
  var yyyy = this.getFullYear().toString();
  var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based
  var dd  = this.getDate().toString();
  return yyyy + (mm[1]?mm:"0"+mm[0]) + (dd[1]?dd:"0"+dd[0]); // padding
};

Script Download: Lin-Rodnitzky Ratio

Script Code

Questions? Feel Free to Contact Us.