android-installs-to-bigquery.gs

function main()
{
  var bigquery_project_id = 'xxxx-xxxx';
  var bigquery_dataset_id = 'xxxx_xxxx';

  var ads_accounts = AdsManagerApp.accounts().withIds(['xxx-xxx-xxxx']).get();
  var ads_account = ads_accounts.next();

  AdsManagerApp.select(ads_account);

  var start_date = new Date();
  //start_date.setDate(start_date.getDate() - 1);
  var end_date = new Date();
  //end_date.setDate(end_date.getDate() - 1);

  //var start_date = new Date('06/01/2019');
  //var end_date = new Date('12/31/2019');

  while(start_date <= end_date)
  {
    var date_day = ((start_date.getDate()) >= 10) ? (start_date.getDate()) : '0' + (start_date.getDate());
    var date_month = ((start_date.getMonth() + 1) >= 10) ? (start_date.getMonth() + 1) : '0' + (start_date.getMonth() + 1);
    var date = start_date.getFullYear() + '-' + date_month + '-' + date_day;
    var date2 = start_date.getFullYear() + '' + date_month + '' + date_day;

    var ads_report = AdsApp.report("SELECT Date, CampaignId, Cost FROM CAMPAIGN_PERFORMANCE_REPORT WHERE CampaignName CONTAINS 'android' DURING " + date2 + ", " + date2);
    var ads_report_rows = ads_report.rows();

    var ads_report_result_1 = [];
    while (ads_report_rows.hasNext())
    {
      var row = ads_report_rows.next();

      ads_report_result_1.push(parseFloat(row['Cost'].replace(',', '')));
    }
    var ads_report_sum_result_1 = ads_report_result_1.reduce(function(a, b){
        return a + b;
    }, 0);

    var ads_report = AdsApp.report("SELECT Date, CampaignId, AllConversions FROM CAMPAIGN_PERFORMANCE_REPORT WHERE CampaignName CONTAINS 'android' AND ConversionTypeName = 'Installs (Android)' DURING " + date2 + ", " + date2);
    var ads_report_rows = ads_report.rows();

    var ads_report_result_2 = [];
    while (ads_report_rows.hasNext())
    {
      var row = ads_report_rows.next();

      ads_report_result_2.push(parseFloat(row['AllConversions'].replace(',', '')));
    }
    var ads_report_sum_result_2 = ads_report_result_2.reduce(function(a, b){
        return a + b;
    }, 0);

    var bigquery_table_id = 'daily_android_' + date2;

    var bigquery_data = BigQuery.newTableDataInsertAllRequest();
    bigquery_data.rows = [];

    var bigquery_row = BigQuery.newTableDataInsertAllRequestRows();
    bigquery_row.insertId = 1;
    bigquery_row.json = {
      'date': date,
      'cost': ads_report_sum_result_1,
      'installs': ads_report_sum_result_2
    };
    Logger.log(bigquery_row);
    bigquery_data.rows.push(bigquery_row);

    var bigquery_tables = null;
    try
    {
      var bigquery_tables = BigQuery.Tables.get(bigquery_project_id, bigquery_dataset_id, bigquery_table_id) ;
    }
    catch (e)
    {
    }

    if (bigquery_tables != null)
    {
      if (bigquery_tables.id == bigquery_project_id + ':' + bigquery_dataset_id + '.' + bigquery_table_id)
      {
        BigQuery.Tables.remove(bigquery_project_id, bigquery_dataset_id, bigquery_table_id);
      }
    }

    var bigquery_table = {tableReference: {projectId: bigquery_project_id, datasetId: bigquery_dataset_id, tableId: bigquery_table_id}, schema: {fields: [{name: 'date', type: 'DATE'}, {name: 'cost', type: 'FLOAT'}, {name: 'installs', type: 'FLOAT'}]}};
    bigquery_table = BigQuery.Tables.insert(bigquery_table, bigquery_project_id, bigquery_dataset_id);

    var bigquery_insert = BigQuery.Tabledata.insertAll(bigquery_data, bigquery_project_id, bigquery_dataset_id, bigquery_table_id);

    start_date = new Date(start_date.setDate(start_date.getDate() + 1));
  }
};