What's the code for custom date range?

[Aaron Parnes]

I got this script from freeawordsscripts.com which is supposed to organize the data from the ads report and combine all distinct rows into one value - similar to a Pivot Table, but without having to manually do it.

1. How do I set this script to automatically retrieve the last 2 years of data?
2. How can we have the data appear automatically sorted by Clicks - highest to lowest?

Here is the code:

/****************************

* Export an AdWords report to a Google Sheet and pivot on unique entities

* Version 1.0

* 

* Created By: Frederick Vallaeys 

* for FreeAdWordsScripts.com and Optmyzr.com

* to support advanced use cases of the Optmyzr Rule Engine available at www.optmyzr.com

****************************/

// Instructions: 

// You can edit the following settings

// - attributes: these are the AdWords reporting attributes. You will get a single spreadsheet row per unique combination of attributes

// - segments: these are the AdWords reporting segments. The values of each segment will be joined with metrics and create 1 column for each combination

// - metrics: the AdWords reporting metrics to include.

// - sourceReport: the report type from Google. See the link below for more information

// - spreadsheetUrl: the Url of the Google spreadsheet that this script will update.

// - tabName: the name of the sheet (tab) in the spreadsheet that should be updated.

// - reportVersion: the version of the AdWords API reports you’re using. The data available in the Ads API changes periodically so this ensures our script talks to the right version of AdWords.

//

// Here is the list of acceptable attributes, segments and metrics for the keywords performance report: 

//          https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report

//      refer to this link to find similar pages for other reports

var QUERIES = [{'attributes' : 'QUERY',

                'segments' : '',

                'metrics' : 'Clicks,Impressions,Cost,Conversions,ConversionRate,AllConversionValue,ValuePerConversion',

                'sourceReport' : 'SEARCH_QUERY_PERFORMANCE_REPORT',

                'dateRangeType' : 'CUSTOM_DATE',

'dateRange' : 'DURING 20171101, 20191118'

                'spreadsheetUrl' : 'https://docs.google.com/spreadsheets/d/1apzOJv_9LaAxnSsacC-X1dsXFXB8dy4XK2rGDQb3-sw/edit?usp=sharing',

                'tabName' : 'Sheet1',

                'reportVersion' : 'v201710'

               }

               

              ];

var USERDELIMITER = ".";

function main() {

  for(var i in QUERIES) {

    var queryObject = QUERIES[i];

    var attributes = queryObject.attributes.replace(/\s/g, '');

    var attributeArray = attributes.split(",");

    var segments = queryObject.segments.replace(/\s/g, '');

    var segmentArray = segments.split(",");

    var metrics = queryObject.metrics.replace(/\s/g, '');

    var metricArray = metrics.split(",");

    var sourceReport = queryObject.sourceReport;

    var dateRange = queryObject.dateRange;

    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var tabName = queryObject.tabName;

    var reportVersion = queryObject.reportVersion;

    //Logger.log(spreadsheetUrl + " " + query);

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(tabName);

    

    var delimiter = "../|/..";

    var map = new Array();

    var segmentKeyList = new Array();

    var query = "SELECT " + attributes + "," + segments + "," + metrics + " FROM " + sourceReport + " DURING " + dateRange;

    

    var report = AdWordsApp.report(query, {apiVersion: reportVersion});

    var rows = report.rows();

    while(rows.hasNext()) {

      var row = rows.next();

      var pivotKey = "";

     

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

        var key = attributeArray[i];

        //Logger.log(key);

        var val = row[key];

        //Logger.log(" " + val);

        pivotKey += val + delimiter;

      }

      //Logger.log(pivotKey);

      if(!map[pivotKey]) {

        map[pivotKey] = new Array();

      }

      

      

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

        var segmentKey = "";

        var key = segmentArray[i];

          //Logger.log(key);

        var val = row[key];

        //Logger.log(" " + val);

        for(var j = 0; j < metricArray.length; j++) {

          var metricName = metricArray[j];

          var metricValue = parseFloat(row[metricName]);

          segmentKey = val + delimiter + metricName;

          //Logger.log(" " + segmentKey);

          

          segmentKeyList[segmentKey] = 1;

          if(!map[pivotKey][segmentKey]) {

            map[pivotKey][segmentKey] = metricValue;

          } else {

            map[pivotKey][segmentKey] += metricValue;

          }

          //Logger.log("   " + metricValue);

          

        }

      }

      

    }

    

    

    var dataToWrite = new Array();

    

    var headerRow = attributeArray;

    for(var segmentKey in segmentKeyList){

      var cleanSegmentName = segmentKey.replace(delimiter,USERDELIMITER);

      headerRow.push(cleanSegmentName);

      //Logger.log(cleanSegmentName);

    }

    //Logger.log(headerRow);

    

    //dataToWrite.push(headerRow);

    

    for(pivotKey in map) {

      var rowToWrite = new Array();

      var row = map[pivotKey];

      var pivotParts = pivotKey.split(delimiter);

      var lastElement = pivotParts.pop();

      //Logger.log(pivotParts);

      var rowToWrite = rowToWrite.concat(pivotParts);

      for(segmentKey in segmentKeyList) {

        

        var val = map[pivotKey][segmentKey];

        if(!val) val = "";

        //Logger.log(pivotKey);

      //Logger.log(" " + segmentKey + " : " + val);

      rowToWrite.push(val);

      }

      //Logger.log(rowToWrite);

      dataToWrite.push(rowToWrite);

    }

    //Logger.log("");

    //Logger.log(dataToWrite);

    var amountOfDataWritten = writeDataToGoogleSheet(dataToWrite, sheet, headerRow, 1);

    if(amountOfDataWritten) Logger.log(amountOfDataWritten + " rows of data written to " + spreadsheetUrl);

    

  }

  

  // FUNCTION: writeDataToGoogleSheet

function writeDataToGoogleSheet(data, sheet, columnsUsedArray, overwrite) {

  

  

  

      // Write Header

      if(overwrite) {

        sheet.clear();

        var toWrite = new Array();

        toWrite.push(columnsUsedArray);

        //Logger.log (toWrite);

        var range = sheet.getRange(1, 1, toWrite.length, toWrite[0].length);

        range.setValues(toWrite);

      }

      

      //var dataAdded = 1;

      if(!overwrite) {

        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: " + data.length + " dataToWrite[0].length: " + data[0].length);

      var range = sheet.getRange(startRow+1, startColumn+1, data.length, data[0].length);

      range.setValues(data);

      var amountWritten = data.length;

      return(amountWritten); 

      

      

    } 

  

}

[Google Ads Scripts Forum Advisor]

Hi Aaron,

Thanks for reaching out.

Unfortunately, our team doesn't support third party scripts such as the one that you provided. With this, you may try to contact the owner of the script via this link to get further support.

Regards,
Ejay
Google Ads Scripts Team

ref:_00D1U1174p._5001UOChBA:ref