Export Google Ads Reports into BigQuery JS Script
684 views
Skip to first unread message
Stanislav Egorov
Mar 10, 2022, 3:24:56 AM3/10/22
to Google Ads Scripts Forum
Good afternoon,
I took the script from the documentation - Export Google Ads Reports into BigQuery Manager Account, but it doesn't work.
My script:
var CONFIG = {
BIGQUERY_PROJECT_ID: 'orbital-caldron-207714',
BIGQUERY_DATASET_ID: 'google_ads_report',
// Truncate existing data, otherwise will append.
TRUNCATE_EXISTING_DATASET: false,
TRUNCATE_EXISTING_TABLES: false,
// Back up reports to Google Drive.
WRITE_DATA_TO_DRIVE: false,
// Folder to put all the intermediate files.
DRIVE_FOLDER: 'INSERT_FOLDER_NAME',
// Default date range over which statistics fields are retrieved.
DEFAULT_DATE_RANGE: 'YESTERDAY',
// Lists of reports and fields to retrieve from Google Ads.
REPORTS: [{NAME: 'Campaign_status',
CONDITIONS: '',
FIELDS: {'Campaign' : 'STRING',
'CampaignStatus': 'STRING',
'CampaignID': 'STRING',
'Budget': 'STRING'
}}
],
RECIPIENT_EMAILS: [
'RECIPIENT_EMAIL'
]
};
// Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.
var MAX_INSERT_SIZE = 10;
/**
* Main method
*/
function main() {
createDataset();
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
createTable(reportConfig);
}
var folder;
if (CONFIG.WRITE_DATA_TO_DRIVE) {
folder = getDriveFolder();
}
// Get an account iterator.
var accountIterator = AdsManagerApp.accounts().withLimit(10).get();
var jobIdMap = {};
while (accountIterator.hasNext()) {
// Get the current account.
var account = accountIterator.next();
// Select the child account.
AdsManagerApp.select(account);
// Run reports against child account.
var accountJobIds = processReports(folder, account.getCustomerId());
jobIdMap[account.getCustomerId()] = accountJobIds;
}
waitTillJobsComplete(jobIdMap);
sendEmail(jobIdMap);
}
/**
* Creates a new dataset.
*
* If a dataset with the same id already exists and the truncate flag
* is set, will truncate the old dataset. If the truncate flag is not
* set, then will not create a new dataset.
*/
function createDataset() {
if (datasetExists()) {
if (CONFIG.TRUNCATE_EXISTING_DATASET) {
BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});
Logger.log('Truncated dataset.');
} else {
Logger.log('Dataset %s already exists. Will not recreate.',
CONFIG.BIGQUERY_DATASET_ID);
return;
}
}
// Create new dataset.
var dataSet = BigQuery.newDataset();
dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;
dataSet.datasetReference = BigQuery.newDatasetReference();
dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);
Logger.log('Created dataset with id %s.', dataSet.id);
}
/**
* Checks if dataset already exists in project.
*
* @return {boolean} Returns true if dataset already exists.
*/
function datasetExists() {
// Get a list of all datasets in project.
var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);
var datasetExists = false;
// Iterate through each dataset and check for an id match.
if (datasets.datasets != null) {
for (var i = 0; i < datasets.datasets.length; i++) {
var dataset = datasets.datasets[i];
if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {
datasetExists = true;
break;
}
}
}
return datasetExists;
}
/**
* Creates a new table.
*
* If a table with the same id already exists and the truncate flag
* is set, will truncate the old table. If the truncate flag is not
* set, then will not create a new table.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
*/
function createTable(reportConfig) {
if (tableExists(reportConfig.NAME)) {
if (CONFIG.TRUNCATE_EXISTING_TABLES) {
BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);
Logger.log('Truncated dataset %s.', reportConfig.NAME);
} else {
Logger.log('Table %s already exists. Will not recreate.',
reportConfig.NAME);
return;
}
}
// Create new table.
var table = BigQuery.newTable();
var schema = BigQuery.newTableSchema();
var bigQueryFields = [];
// Add account column to table.
var accountFieldSchema = BigQuery.newTableFieldSchema();
accountFieldSchema.description = 'AccountId';
accountFieldSchema.name = 'AccountId';
accountFieldSchema.type = 'STRING';
bigQueryFields.push(accountFieldSchema);
// Add each field to table schema.
var fieldNames = Object.keys(reportConfig.FIELDS);
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i];
var bigQueryFieldSchema = BigQuery.newTableFieldSchema();
bigQueryFieldSchema.description = fieldName;
bigQueryFieldSchema.name = fieldName;
bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName];
bigQueryFields.push(bigQueryFieldSchema);
}
schema.fields = bigQueryFields;
table.schema = schema;
table.friendlyName = reportConfig.NAME;
table.tableReference = BigQuery.newTableReference();
table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
table.tableReference.tableId = reportConfig.NAME;
table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
Logger.log('Created table with id %s.', table.id);
}
/**
* Checks if table already exists in dataset.
*
* @param {string} tableId The table id to check existence.
*
* @return {boolean} Returns true if table already exists.
*/
function tableExists(tableId) {
// Get a list of all tables in the dataset.
var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
var tableExists = false;
// Iterate through each table and check for an id match.
if (tables.tables != null) {
for (var i = 0; i < tables.tables.length; i++) {
var table = tables.tables[i];
if (table.tableReference.tableId == tableId) {
tableExists = true;
break;
}
}
}
return tableExists;
}
/**
* Process all configured reports
*
* Iterates through each report to: retrieve Google Ads data,
* backup data to Drive (if configured), load data to BigQuery.
* Returns a list of job ids for insert jobs.
*
* @param {Folder} folder Google Drive folder to store reports.
* @param {string} accountId Account Id to run reports.
*
* @return {Array.<string>} jobIds The list of job ids.
*
*/
function processReports(folder, accountId) {
var jobIds = [];
// Iterate over each report type.
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
Logger.log('Running report %s for account %s', reportConfig.NAME,
accountId);
// Get data as an array of CSV chunks.
var csvData = retrieveAdsReport(reportConfig, accountId);
// If configured, back up data.
if (CONFIG.WRITE_DATA_TO_DRIVE) {
for (var r = 0; r < csvData.length; r++) {
var fileName = reportConfig.NAME + '_' + accountId + '_' + (r + 1);
saveCompressedCsvFile(folder, fileName, csvData[r]);
}
Logger.log('Exported data to Drive folder ' +
CONFIG.DRIVE_FOLDER + ' for report ' + fileName);
}
for (var j = 0; j < csvData.length; j++) {
// Convert to Blob format.
var blobData = Utilities.newBlob(csvData[j], 'application/octet-stream');
// Load data
var jobId = loadDataToBigquery(reportConfig, blobData, !j ? 1 : 0);
jobIds.push(jobId);
}
}
return jobIds;
}
/**
* Writes a CSV file to Drive, compressing as a zip file.
*
* @param {!Folder} folder The parent folder for the file.
* @param {string} fileName The name for the file.
* @param {string} csvData The CSV data to write to the file.
*/
function saveCompressedCsvFile(folder, fileName, csvData) {
var compressed = Utilities.zip([Utilities.newBlob(csvData)]);
compressed.setName(fileName);
folder.createFile(compressed);
}
/**
* Retrieves Google Ads data as csv and formats any fields
* to BigQuery expected format.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {string} accountId Account Id to run reports.
*
* @return {!Array.<string>} a chunked report in csv format.
*/
function retrieveAdsReport(reportConfig, accountId) {
var fieldNames = Object.keys(reportConfig.FIELDS);
var report = AdsApp.report('SELECT CampaignName, CampaignStatus, CampaignId, RecommendedBudgetAmount ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT');
//var report = AdsApp.report(
// 'SELECT ' + fieldNames.join(',') +
// ' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS +
// ' DURING ' + CONFIG.DEFAULT_DATE_RANGE);
//var query = "Select CampaignName, CampaignStatus, CampaignId, Amount from CAMPAIGN_PERFORMANCE_REPORT,BUDGET_PERFORMANCE_REPORT";
//Logger.log(query);
//var report = AdsApp.report(query);
var rows = report.rows();
var chunks = [];
var chunkLen = 0;
var csvRows = [];
var totalRows = 0;
// Header row
var header = 'AccountId,' + fieldNames.join(',');
csvRows.push(header);
chunkLen += Utilities.newBlob(header).getBytes().length + 1;
// Iterate over each row.
while (rows.hasNext()) {
var row = rows.next();
if (chunkLen > MAX_INSERT_SIZE) {
chunks.push(csvRows.join('\n'));
totalRows += csvRows.length;
chunkLen = 0;
csvRows = [];
}
var csvRow = [];
csvRow.push(accountId);
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i];
var fieldValue = row[fieldName].toString();
var fieldType = reportConfig.FIELDS[fieldName];
// Strip off % and perform any other formatting here.
if (fieldType == 'FLOAT' || fieldType == 'INTEGER') {
if (fieldValue.charAt(fieldValue.length - 1) == '%') {
fieldValue = fieldValue.substring(0, fieldValue.length - 1);
}
fieldValue = fieldValue.replace(/,/g,'');
}
// Add double quotes to any string values.
if (fieldType == 'STRING') {
fieldValue = fieldValue.replace(/"/g, '""');
fieldValue = '"' + fieldValue + '"';
}
csvRow.push(fieldValue);
}
var rowString = csvRow.join(',');
csvRows.push(rowString);
chunkLen += Utilities.newBlob(rowString).getBytes().length + 1;
}
if (csvRows) {
totalRows += csvRows.length;
chunks.push(csvRows.join('\n'));
}
Logger.log('Downloaded ' + reportConfig.NAME + ' for account ' + accountId +
' with ' + totalRows + ' rows, in ' + chunks.length + ' chunks.');
return chunks;
}
/**
* Creates a new Google Drive folder. If folder name is already in
* use will pick the first folder with a matching name.
*
* @return {Folder} Google Drive folder to store reports.
*/
function getDriveFolder() {
var folders = DriveApp.getFoldersByName(CONFIG.DRIVE_FOLDER);
// Assume first folder is the correct one.
if (folders.hasNext()) {
Logger.log('Folder name found. Using existing folder.');
return folders.next();
}
return DriveApp.createFolder(CONFIG.DRIVE_FOLDER);
}
/**
* Creates a BigQuery insertJob to load csv data.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {Blob} data Csv report data as an 'application/octet-stream' blob.
* @param {number=} skipLeadingRows Optional number of rows to skip.
*
* @return {string} jobId The job id for upload.
*/
function loadDataToBigquery(reportConfig, data, skipLeadingRows) {
// Create the data upload job.
var job = {
configuration: {
load: {
destinationTable: {
projectId: CONFIG.BIGQUERY_PROJECT_ID,
datasetId: CONFIG.BIGQUERY_DATASET_ID,
tableId: reportConfig.NAME
},
skipLeadingRows: skipLeadingRows ? skipLeadingRows : 0,
nullMarker: '--'
}
}
};
var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);
Logger.log('Load job started for %s. Check on the status of it here: ' +
'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME,
CONFIG.BIGQUERY_PROJECT_ID);
return insertJob.jobReference.jobId;
}
/**
* Polls until all jobs are 'DONE'.
*
* @param {Object.<string, Array.<string>>} jobIdMap A map, with key as the
* accountId, and value as an array of job ids.
*/
function waitTillJobsComplete(jobIdMap) {
var complete = false;
var remainingJobs = [];
var accountIds = Object.keys(jobIdMap);
for (var i = 0; i < accountIds.length; i++){
var accountJobIds = jobIdMap[accountIds[i]];
remainingJobs.push.apply(remainingJobs, accountJobIds);
}
while (!complete) {
if (AdsApp.getExecutionInfo().getRemainingTime() < 5){
Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') +
' are still incomplete.');
}
remainingJobs = getIncompleteJobs(remainingJobs);
if (remainingJobs.length == 0) {
complete = true;
}
if (!complete) {
Logger.log(remainingJobs.length + ' jobs still being processed.');
// Wait 5 seconds before checking status again.
Utilities.sleep(5000);
}
}
Logger.log('All jobs processed.');
}
/**
* Iterates through jobs and returns the ids for those jobs
* that are not 'DONE'.
*
* @param {Array.<string>} jobIds The list of running job ids.
*
* @return {Array.<string>} remainingJobIds The list of remaining job ids.
*/
function getIncompleteJobs(jobIds) {
var remainingJobIds = [];
for (var i = 0; i < jobIds.length; i++) {
var jobId = jobIds[i];
var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
if (getJob.status.state != 'DONE') {
remainingJobIds.push(jobId);
}
}
return remainingJobIds;
}
BIGQUERY_PROJECT_ID: 'orbital-caldron-207714',
BIGQUERY_DATASET_ID: 'google_ads_report',
// Truncate existing data, otherwise will append.
TRUNCATE_EXISTING_DATASET: false,
TRUNCATE_EXISTING_TABLES: false,
// Back up reports to Google Drive.
WRITE_DATA_TO_DRIVE: false,
// Folder to put all the intermediate files.
DRIVE_FOLDER: 'INSERT_FOLDER_NAME',
// Default date range over which statistics fields are retrieved.
DEFAULT_DATE_RANGE: 'YESTERDAY',
// Lists of reports and fields to retrieve from Google Ads.
REPORTS: [{NAME: 'Campaign_status',
CONDITIONS: '',
FIELDS: {'Campaign' : 'STRING',
'CampaignStatus': 'STRING',
'CampaignID': 'STRING',
'Budget': 'STRING'
}}
],
RECIPIENT_EMAILS: [
'RECIPIENT_EMAIL'
]
};
// Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.
var MAX_INSERT_SIZE = 10;
/**
* Main method
*/
function main() {
createDataset();
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
createTable(reportConfig);
}
var folder;
if (CONFIG.WRITE_DATA_TO_DRIVE) {
folder = getDriveFolder();
}
// Get an account iterator.
var accountIterator = AdsManagerApp.accounts().withLimit(10).get();
var jobIdMap = {};
while (accountIterator.hasNext()) {
// Get the current account.
var account = accountIterator.next();
// Select the child account.
AdsManagerApp.select(account);
// Run reports against child account.
var accountJobIds = processReports(folder, account.getCustomerId());
jobIdMap[account.getCustomerId()] = accountJobIds;
}
waitTillJobsComplete(jobIdMap);
sendEmail(jobIdMap);
}
/**
* Creates a new dataset.
*
* If a dataset with the same id already exists and the truncate flag
* is set, will truncate the old dataset. If the truncate flag is not
* set, then will not create a new dataset.
*/
function createDataset() {
if (datasetExists()) {
if (CONFIG.TRUNCATE_EXISTING_DATASET) {
BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});
Logger.log('Truncated dataset.');
} else {
Logger.log('Dataset %s already exists. Will not recreate.',
CONFIG.BIGQUERY_DATASET_ID);
return;
}
}
// Create new dataset.
var dataSet = BigQuery.newDataset();
dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;
dataSet.datasetReference = BigQuery.newDatasetReference();
dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);
Logger.log('Created dataset with id %s.', dataSet.id);
}
/**
* Checks if dataset already exists in project.
*
* @return {boolean} Returns true if dataset already exists.
*/
function datasetExists() {
// Get a list of all datasets in project.
var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);
var datasetExists = false;
// Iterate through each dataset and check for an id match.
if (datasets.datasets != null) {
for (var i = 0; i < datasets.datasets.length; i++) {
var dataset = datasets.datasets[i];
if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {
datasetExists = true;
break;
}
}
}
return datasetExists;
}
/**
* Creates a new table.
*
* If a table with the same id already exists and the truncate flag
* is set, will truncate the old table. If the truncate flag is not
* set, then will not create a new table.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
*/
function createTable(reportConfig) {
if (tableExists(reportConfig.NAME)) {
if (CONFIG.TRUNCATE_EXISTING_TABLES) {
BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);
Logger.log('Truncated dataset %s.', reportConfig.NAME);
} else {
Logger.log('Table %s already exists. Will not recreate.',
reportConfig.NAME);
return;
}
}
// Create new table.
var table = BigQuery.newTable();
var schema = BigQuery.newTableSchema();
var bigQueryFields = [];
// Add account column to table.
var accountFieldSchema = BigQuery.newTableFieldSchema();
accountFieldSchema.description = 'AccountId';
accountFieldSchema.name = 'AccountId';
accountFieldSchema.type = 'STRING';
bigQueryFields.push(accountFieldSchema);
// Add each field to table schema.
var fieldNames = Object.keys(reportConfig.FIELDS);
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i];
var bigQueryFieldSchema = BigQuery.newTableFieldSchema();
bigQueryFieldSchema.description = fieldName;
bigQueryFieldSchema.name = fieldName;
bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName];
bigQueryFields.push(bigQueryFieldSchema);
}
schema.fields = bigQueryFields;
table.schema = schema;
table.friendlyName = reportConfig.NAME;
table.tableReference = BigQuery.newTableReference();
table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
table.tableReference.tableId = reportConfig.NAME;
table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
Logger.log('Created table with id %s.', table.id);
}
/**
* Checks if table already exists in dataset.
*
* @param {string} tableId The table id to check existence.
*
* @return {boolean} Returns true if table already exists.
*/
function tableExists(tableId) {
// Get a list of all tables in the dataset.
var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
var tableExists = false;
// Iterate through each table and check for an id match.
if (tables.tables != null) {
for (var i = 0; i < tables.tables.length; i++) {
var table = tables.tables[i];
if (table.tableReference.tableId == tableId) {
tableExists = true;
break;
}
}
}
return tableExists;
}
/**
* Process all configured reports
*
* Iterates through each report to: retrieve Google Ads data,
* backup data to Drive (if configured), load data to BigQuery.
* Returns a list of job ids for insert jobs.
*
* @param {Folder} folder Google Drive folder to store reports.
* @param {string} accountId Account Id to run reports.
*
* @return {Array.<string>} jobIds The list of job ids.
*
*/
function processReports(folder, accountId) {
var jobIds = [];
// Iterate over each report type.
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
Logger.log('Running report %s for account %s', reportConfig.NAME,
accountId);
// Get data as an array of CSV chunks.
var csvData = retrieveAdsReport(reportConfig, accountId);
// If configured, back up data.
if (CONFIG.WRITE_DATA_TO_DRIVE) {
for (var r = 0; r < csvData.length; r++) {
var fileName = reportConfig.NAME + '_' + accountId + '_' + (r + 1);
saveCompressedCsvFile(folder, fileName, csvData[r]);
}
Logger.log('Exported data to Drive folder ' +
CONFIG.DRIVE_FOLDER + ' for report ' + fileName);
}
for (var j = 0; j < csvData.length; j++) {
// Convert to Blob format.
var blobData = Utilities.newBlob(csvData[j], 'application/octet-stream');
// Load data
var jobId = loadDataToBigquery(reportConfig, blobData, !j ? 1 : 0);
jobIds.push(jobId);
}
}
return jobIds;
}
/**
* Writes a CSV file to Drive, compressing as a zip file.
*
* @param {!Folder} folder The parent folder for the file.
* @param {string} fileName The name for the file.
* @param {string} csvData The CSV data to write to the file.
*/
function saveCompressedCsvFile(folder, fileName, csvData) {
var compressed = Utilities.zip([Utilities.newBlob(csvData)]);
compressed.setName(fileName);
folder.createFile(compressed);
}
/**
* Retrieves Google Ads data as csv and formats any fields
* to BigQuery expected format.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {string} accountId Account Id to run reports.
*
* @return {!Array.<string>} a chunked report in csv format.
*/
function retrieveAdsReport(reportConfig, accountId) {
var fieldNames = Object.keys(reportConfig.FIELDS);
var report = AdsApp.report('SELECT CampaignName, CampaignStatus, CampaignId, RecommendedBudgetAmount ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT');
//var report = AdsApp.report(
// 'SELECT ' + fieldNames.join(',') +
// ' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS +
// ' DURING ' + CONFIG.DEFAULT_DATE_RANGE);
//var query = "Select CampaignName, CampaignStatus, CampaignId, Amount from CAMPAIGN_PERFORMANCE_REPORT,BUDGET_PERFORMANCE_REPORT";
//Logger.log(query);
//var report = AdsApp.report(query);
var rows = report.rows();
var chunks = [];
var chunkLen = 0;
var csvRows = [];
var totalRows = 0;
// Header row
var header = 'AccountId,' + fieldNames.join(',');
csvRows.push(header);
chunkLen += Utilities.newBlob(header).getBytes().length + 1;
// Iterate over each row.
while (rows.hasNext()) {
var row = rows.next();
if (chunkLen > MAX_INSERT_SIZE) {
chunks.push(csvRows.join('\n'));
totalRows += csvRows.length;
chunkLen = 0;
csvRows = [];
}
var csvRow = [];
csvRow.push(accountId);
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i];
var fieldValue = row[fieldName].toString();
var fieldType = reportConfig.FIELDS[fieldName];
// Strip off % and perform any other formatting here.
if (fieldType == 'FLOAT' || fieldType == 'INTEGER') {
if (fieldValue.charAt(fieldValue.length - 1) == '%') {
fieldValue = fieldValue.substring(0, fieldValue.length - 1);
}
fieldValue = fieldValue.replace(/,/g,'');
}
// Add double quotes to any string values.
if (fieldType == 'STRING') {
fieldValue = fieldValue.replace(/"/g, '""');
fieldValue = '"' + fieldValue + '"';
}
csvRow.push(fieldValue);
}
var rowString = csvRow.join(',');
csvRows.push(rowString);
chunkLen += Utilities.newBlob(rowString).getBytes().length + 1;
}
if (csvRows) {
totalRows += csvRows.length;
chunks.push(csvRows.join('\n'));
}
Logger.log('Downloaded ' + reportConfig.NAME + ' for account ' + accountId +
' with ' + totalRows + ' rows, in ' + chunks.length + ' chunks.');
return chunks;
}
/**
* Creates a new Google Drive folder. If folder name is already in
* use will pick the first folder with a matching name.
*
* @return {Folder} Google Drive folder to store reports.
*/
function getDriveFolder() {
var folders = DriveApp.getFoldersByName(CONFIG.DRIVE_FOLDER);
// Assume first folder is the correct one.
if (folders.hasNext()) {
Logger.log('Folder name found. Using existing folder.');
return folders.next();
}
return DriveApp.createFolder(CONFIG.DRIVE_FOLDER);
}
/**
* Creates a BigQuery insertJob to load csv data.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {Blob} data Csv report data as an 'application/octet-stream' blob.
* @param {number=} skipLeadingRows Optional number of rows to skip.
*
* @return {string} jobId The job id for upload.
*/
function loadDataToBigquery(reportConfig, data, skipLeadingRows) {
// Create the data upload job.
var job = {
configuration: {
load: {
destinationTable: {
projectId: CONFIG.BIGQUERY_PROJECT_ID,
datasetId: CONFIG.BIGQUERY_DATASET_ID,
tableId: reportConfig.NAME
},
skipLeadingRows: skipLeadingRows ? skipLeadingRows : 0,
nullMarker: '--'
}
}
};
var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);
Logger.log('Load job started for %s. Check on the status of it here: ' +
'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME,
CONFIG.BIGQUERY_PROJECT_ID);
return insertJob.jobReference.jobId;
}
/**
* Polls until all jobs are 'DONE'.
*
* @param {Object.<string, Array.<string>>} jobIdMap A map, with key as the
* accountId, and value as an array of job ids.
*/
function waitTillJobsComplete(jobIdMap) {
var complete = false;
var remainingJobs = [];
var accountIds = Object.keys(jobIdMap);
for (var i = 0; i < accountIds.length; i++){
var accountJobIds = jobIdMap[accountIds[i]];
remainingJobs.push.apply(remainingJobs, accountJobIds);
}
while (!complete) {
if (AdsApp.getExecutionInfo().getRemainingTime() < 5){
Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') +
' are still incomplete.');
}
remainingJobs = getIncompleteJobs(remainingJobs);
if (remainingJobs.length == 0) {
complete = true;
}
if (!complete) {
Logger.log(remainingJobs.length + ' jobs still being processed.');
// Wait 5 seconds before checking status again.
Utilities.sleep(5000);
}
}
Logger.log('All jobs processed.');
}
/**
* Iterates through jobs and returns the ids for those jobs
* that are not 'DONE'.
*
* @param {Array.<string>} jobIds The list of running job ids.
*
* @return {Array.<string>} remainingJobIds The list of remaining job ids.
*/
function getIncompleteJobs(jobIds) {
var remainingJobIds = [];
for (var i = 0; i < jobIds.length; i++) {
var jobId = jobIds[i];
var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
if (getJob.status.state != 'DONE') {
remainingJobIds.push(jobId);
}
}
return remainingJobIds;
}
I changed a piece of the query with "select"
I have a error: TypeError: Cannot call method "toString" of undefined. (file Code.gs, line 314)
Google Ads Scripts Forum Advisor
Mar 11, 2022, 12:02:34 AM3/11/22
to adwords...@googlegroups.com
Hello Stanislav,
I’m James from the Google Ads Scripts support team. Allow me to assist you.
Can you please provide us with the following information below so that I can check this further?
- Google Account ID / CID
- Name of the script wherein the given code is implemented.
- Some screenshot that shows the issue.
Kindly send the requested details above via ‘Reply privately to author’ option, if the private option is not available on your end, then please send it over through this email <googleadsscr...@google.com> instead.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 11, 2022, 1:36:06 AM3/11/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello James,
I took this script from the Manager Account documentation, but when it
runs, it calls to a separate account for some reason, even though it
is not listed anywhere. I don't understand this point either.
1. Our Google Account ID for MCC Accounts - 920-254-5064
2. Name of the script - Campaign Status
3. Attached is a screenshot
And I guess I didn't fully understand the point of this script, but I
thought it would take data from custom reports inside GAds and
transfer them to BigQuery.
Now I understand that the script works by calling GAQL, asking for the
necessary parameters from tables (e.g. CAMPAIGN_PERFORMANCE_REPORT)
пт, 11 мар. 2022 г. в 08:02, Google Ads Scripts Forum on behalf of
adsscripts <adwords...@googlegroups.com>:
>
> --
> -- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/mU2WL000000000000000000000000000000000000000000000R8KDZZ007zayeXV0SoOXV9Z_22foeQ%40sfdc.net.
I took this script from the Manager Account documentation, but when it
runs, it calls to a separate account for some reason, even though it
is not listed anywhere. I don't understand this point either.
1. Our Google Account ID for MCC Accounts - 920-254-5064
2. Name of the script - Campaign Status
3. Attached is a screenshot
And I guess I didn't fully understand the point of this script, but I
thought it would take data from custom reports inside GAds and
transfer them to BigQuery.
Now I understand that the script works by calling GAQL, asking for the
necessary parameters from tables (e.g. CAMPAIGN_PERFORMANCE_REPORT)
пт, 11 мар. 2022 г. в 08:02, Google Ads Scripts Forum on behalf of
adsscripts <adwords...@googlegroups.com>:
>
> Hello Stanislav,
>
> I’m James from the Google Ads Scripts support team. Allow me to assist you.
>
> Can you please provide us with the following information below so that I can check this further?
>
> Google Account ID / CID
> Name of the script wherein the given code is implemented.
> Some screenshot that shows the issue.
>
>
> Kindly send the requested details above via ‘Reply privately to author’ option, if the private option is not available on your end, then please send it over through this email <googleadsscr...@google.com> instead.
>
> Regards,
>
> Hello Stanislav,
>
> I’m James from the Google Ads Scripts support team. Allow me to assist you.
>
> Can you please provide us with the following information below so that I can check this further?
>
> Google Account ID / CID
> Name of the script wherein the given code is implemented.
> Some screenshot that shows the issue.
>
>
> Kindly send the requested details above via ‘Reply privately to author’ option, if the private option is not available on your end, then please send it over through this email <googleadsscr...@google.com> instead.
>
> Regards,
>
> James Howell
> Google Ads Scripts Team
>
>
>
> ref:_00D1U1174p._5004Q2XnCnX:ref
> Google Ads Scripts Team
>
>
>
>
> --
> -- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/mU2WL000000000000000000000000000000000000000000000R8KDZZ007zayeXV0SoOXV9Z_22foeQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 14, 2022, 4:14:42 AM3/14/22
to adwords...@googlegroups.com
Hello,
Based on my investigation, I noticed that the implementation of the said solution script is fine. But, I noticed that you didn't provide any value to DRIVE_FOLDER and RECIPIENT_EMAILS, see screenshot attached. That could be the reason why you encountered the said error on your end. That said, take note that you need to update those resources based on the setup of the said solution script.
If the issue persists after you provided values to those attributes, then kindly retry to recreate the script and observe if you will encounter the said error again.
Hope this helps. Let me know if you have any questions.
Regards,
|
| |||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 14, 2022, 4:49:30 AM3/14/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello, James
If I understand correctly, exporting data to Google Drive and sending notifications to the mail is an additional feature, which should not affect the work of the main script. We don't need these functions, so I didn't specify anything.
You did not answer my questions about how the script works properly and there is no explanation of why this error occurs.
You did not answer my questions about how the script works properly and there is no explanation of why this error occurs.
пн, 14 мар. 2022 г. в 11:14, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/5pxhm000000000000000000000000000000000000000000000R8Q6WC00lQwIArxDRd-7Coih0hFp8g%40sfdc.net.
Stanislav Egorov
Mar 14, 2022, 6:49:51 AM3/14/22
to Google Ads Scripts Forum on behalf of adsscripts
It's in the documentation:
"processing a report consists of: retrieving a report as a csv from Google Ads, converting it to a Blob, and creating an insert job to load data into BigQuery."
I pre-created a custom report inside GAds from those parameters that are listed in config. But in the original function of referring to them:
var report = AdsApp.report(
'SELECT ' + fieldNames.join(',') +
' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS +
' DURING ' + CONFIG.DEFAULT_DATE_RANGE);
But when you run the script, an error appears: Exception: Could not identify resource for search query "SELECT Campaign,Campaign_status,CampaignID,Budget FROM Campaign_status DURING TODAY". Double-check your FROM clause.
That's why I'm asking how the script works.
пн, 14 мар. 2022 г. в 11:49, Stanislav Egorov <s.eg...@rbfx.com>:
Google Ads Scripts Forum Advisor
Mar 14, 2022, 10:31:01 PM3/14/22
to adwords...@googlegroups.com
Hello,
Thanks for getting back to us.
With regard to how the solution script (BigQuery Exporter) works, it is definitely explained on this guide. As for you not setting on some of those config fields, I agree that those shouldn't affect on how the current functionality works.
Focusing on the error (Could not identify resource for search query "SELECT Campaign,Campaign_status,CampaignID,Budget FROM Campaign_status DURING TODAY". Double-check your FROM clause.) you've encountered, it seems that the cause of this is the invalid report name in the FROM clause. To remediate this, you should update the report name (line 18 of our script) to CAMPAIGN_PERFORMANCE_REPORT instead of the Campaign_status. You can also check on this report guide for the valid field names that will be supplied under the FIELDS (line 20) config.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 15, 2022, 2:30:14 AM3/15/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
That's what I thought too, so I rewrote your query from the documentation to CAMPAIGN_PERFORMANCE_REPORT. If you look at the first post. But in this case, another error appears - TypeError: Cannot call method "toString" of undefined. (file Code.gs, line 314). That's why I am writing to you.
вт, 15 мар. 2022 г. в 05:31, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/BX9yu000000000000000000000000000000000000000000000R8RLNI00QA4RwLkgQbCsxvO67NxbXQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 15, 2022, 11:24:06 PM3/15/22
to adwords...@googlegroups.com
Hello,
Thanks for getting back to us.
With regard to the error (TypeError: Cannot call method "toString" of undefined. (file Code.gs, line 314)) you've encountered and since our team can't run the said script, can you include logging statements before the error line to further check on the undefined value?
Regards,
Stanislav Egorov
Mar 16, 2022, 2:41:19 AM3/16/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
You sent me a link to the Google Ads Legacy documentation. How can you include logging statements before the error in the new interface?
ср, 16 мар. 2022 г. в 06:24, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/gnXT9000000000000000000000000000000000000000000000R8TIRY00JzKdtH8ARDWKbdSkMC3XYQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 16, 2022, 5:56:05 AM3/16/22
to adwords...@googlegroups.com
Hi,
The logging statement for new script experience is the same as the legacy one. You may check the logging statement to the new script experience here.
Regards,
ref:_00D1U1174p._5004Q2XnCnX:ref
The logging statement for new script experience is the same as the legacy one. You may check the logging statement to the new script experience here.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 16, 2022, 7:10:37 AM3/16/22
to Google Ads Scripts Forum on behalf of adsscripts
Maybe I don't understand something, but I don't see where you can select Execution Logs. The instructions show only a screenshot, but it is not clear where it is.
I attached a screenshot of the logs that appear when you run the script itself, I can do it again.
I attached a screenshot of the logs that appear when you run the script itself, I can do it again.
ср, 16 мар. 2022 г. в 12:56, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/N0Zjf000000000000000000000000000000000000000000000R8U0X900DZk88cu6RK28t-rV5C_vpg%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 17, 2022, 3:40:35 AM3/17/22
to adwords...@googlegroups.com
Hello,
Thank you for getting back to us.
With regard to your concern, please do note that toString() is a Javascript method and not Google Ads Scripts. The TypeError: Cannot call method 'toString' of undefined error usually occurs when you're passing a null value to the said method. This is the reason why my colleague, Mark, is requesting for you to log the row[fieldName] in order to check if the you indeed passing a null value. To avoid this error, you just need to check if the said variable is not null before calling toString() method.
Thank you for getting back to us.
With regard to your concern, please do note that toString() is a Javascript method and not Google Ads Scripts. The TypeError: Cannot call method 'toString' of undefined error usually occurs when you're passing a null value to the said method. This is the reason why my colleague, Mark, is requesting for you to log the row[fieldName] in order to check if the you indeed passing a null value. To avoid this error, you just need to check if the said variable is not null before calling toString() method.
Stanislav Egorov
Mar 17, 2022, 4:50:59 AM3/17/22
to Google Ads Scripts Forum on behalf of adsscripts
It turned out this way
чт, 17 мар. 2022 г. в 10:40, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/WCbOD000000000000000000000000000000000000000000000R8VPBF00XXjfLBA2Q5-GOjh1qlD6Kw%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 17, 2022, 10:27:15 PM3/17/22
to adwords...@googlegroups.com
Hello,
Thanks for getting back to us.
It seems that the row[fieldName] is causing this issue. Is this expected that the said variable is NULL? You can simply check again on your script and see why this variable is set to NULL. Other way of trapping these errors and still continue on the script process is by enclosing those variables in a try/catch statement.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 18, 2022, 3:18:42 AM3/18/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
I don't understand JS scripts. I will repeat the task I would like to perform. I have a report in GAds that contains 4 parameters CampaignName, CampaignStatus, CampaignId, Budget (Daily) and this report should be rewritten every day to get actual status data on campaigns.
I took your script from the documentation and changed the fields that needed to be changed, but I haven't been able to run anything for a week and a half.
I took your script from the documentation and changed the fields that needed to be changed, but I haven't been able to run anything for a week and a half.
Your advice about adding the try/catch operator doesn't work or I'm doing something wrong.
But I would like to solve the problem already instead of the daily messages.
пт, 18 мар. 2022 г. в 05:27, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/qW-UP000000000000000000000000000000000000000000000R8X5H900yWiAFiN8TyerEVSSXDFONQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 21, 2022, 6:08:38 AM3/21/22
to adwords...@googlegroups.com
Hello,
Thank you for getting back to us.
I understand inconvenience that this cause to you. However, I'm afraid that we weren't able to directly test your script as we don't have an access to the BigQuery. I would like to request for your patience during this time.
As per your concern, could you kindly change the line 315 from Logger.log(fieldName); to Logger.log("FieldName: " + fieldName)? Once done, please preview the script in question on your end, then provide to us a screenshot of the result.
Regards,
ref:_00D1U1174p._5004Q2XnCnX:ref
Thank you for getting back to us.
I understand inconvenience that this cause to you. However, I'm afraid that we weren't able to directly test your script as we don't have an access to the BigQuery. I would like to request for your patience during this time.
As per your concern, could you kindly change the line 315 from Logger.log(fieldName); to Logger.log("FieldName: " + fieldName)? Once done, please preview the script in question on your end, then provide to us a screenshot of the result.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 21, 2022, 6:41:03 AM3/21/22
to Google Ads Scripts Forum on behalf of adsscripts
Same thing.
пн, 21 мар. 2022 г. в 13:08, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/az9r-000000000000000000000000000000000000000000000R93AU700dDPdt0fuQsCwYGXqz7RebA%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 22, 2022, 5:45:24 AM3/22/22
to adwords...@googlegroups.com
Hello,
Thank you for getting back to us.
Thank you for getting back to us.
I've scrutinized your script, compare it with our code template using diffchecker, and it appears that there's unintended removal of the code on your end which causes the issue not to run. Could you kindly recreate the script using this code, then let me know how it goes and if you still getting an issue? If so, please provide to us the name of the new script created.
Stanislav Egorov
Mar 22, 2022, 10:15:22 AM3/22/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
Especially for you, I created a new separate script, took your entire script and changed only what needed to be changed.
Script name - Status test new
вт, 22 мар. 2022 г. в 12:45, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/1l0HK000000000000000000000000000000000000000000000R954FH00Yzj2tMtfTEqP8Ei8_LTAjg%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 24, 2022, 4:52:23 AM3/24/22
to adwords...@googlegroups.com
Hello,
I’m James, also a member of the Google Ads Scripts support team. Thank you for your response.
As per checking the newly created scripts, I noticed that there are a lot of modifications that you made to our Export Google Ads Reports into BigQuery solution script, which could be the possible reason why you’re persistently encountering the said error.
Take note that the said solution script is being made as a whole in accordance to its stated purpose. That said, modifying and troubleshooting it will be complicated as you might encounter unexpected issues. Having said that, I would suggest that you recreate your script from scratch and just use the source code of said solution scripts as a reference that aligns to your use case.
Hoping for your understanding.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 24, 2022, 5:18:07 AM3/24/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
I did exactly that in a previous post and sent you the name of the new script to check.
Script name - Status test new
чт, 24 мар. 2022 г. в 11:52, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/MlgQq000000000000000000000000000000000000000000000R98RAR00acWGMevkRqOUrobUMvHqTw%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 25, 2022, 3:47:02 AM3/25/22
to adwords...@googlegroups.com
Hello,
I've adjusted the script based on the error that you're getting. Could you kindly try it on your end, then let me know how it goes? Please don't forget enter your project ID and dataset id.
Regards,
ref:_00D1U1174p._5004Q2XnCnX:ref
I've adjusted the script based on the error that you're getting. Could you kindly try it on your end, then let me know how it goes? Please don't forget enter your project ID and dataset id.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 25, 2022, 5:46:02 AM3/25/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello
Thanks for your help. Unfortunately, the script did not execute.
Script name - New Script status campaign (by support)
пт, 25 мар. 2022 г. в 10:47, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/KLoKr000000000000000000000000000000000000000000000R9AIY8007CxeqqzyRuK6xQAGWn1QOQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 29, 2022, 3:33:23 AM3/29/22
to adwords...@googlegroups.com
Hello,
Michael here and I work along with Teejay.
Upon checking the script, I noticed that you haven't changed the report type name of the resource where you are trying to pull the data which is "Campaign_status". Please note that there are only two options you can use in for your FROM clause which is CAMPAIGN_PERFORMANCE_REPORT if you are using AWQL or CAMPAIGN if you are using GAQL.
Additionally, kindly ensure that the fields you are declaring matches the fields that are available to whichever report type above you opt to use. Upon updating this, I believe your script should work successfully or let us know if you still encounter any kind of errors.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 30, 2022, 2:35:02 AM3/30/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
I did as you said, but now a new error appears.
вт, 29 мар. 2022 г. в 10:33, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/82P--000000000000000000000000000000000000000000000R9HWZH007CfQukpUSTaQx50I1X1PKQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Mar 31, 2022, 2:09:42 AM3/31/22
to adwords...@googlegroups.com
Hello,
Based on the given screenshot, it appears that you’re having an issue with the configuration and construction of your query because I noticed that you’re both using AWQL fields and GAQL resources. Having said that, usage of both AWQL and GAQL is not feasible on a specific query.
I made some adjustments to your REPORT config and your query in order for it to work properly. Having said that, can you please apply the given code(query already migrated to GAQL format) below and observe if you will still encounter some errors?
var CONFIG = {
// Default date range over which statistics fields are retrieved.
DEFAULT_DATE_RANGE: 'YESTERDAY',
// Lists of reports and fields to retrieve from Google Ads.
REPORTS: [{
NAME: 'campaign',
CONDITIONS: 'segments.date',
FIELDS: {'campaign.id' : 'STRING',
'campaign.name': 'STRING',
'campaign.status': 'STRING',
'metrics.cost_micros': 'STRING',
'segments.date' : 'STRING'
}}
],
};
function main() {
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
var reportDateRange = CONFIG.DEFAULT_DATE_RANGE;
}
var fieldNames = Object.keys(reportConfig.FIELDS);
var report = AdsApp.report(
"SELECT " + fieldNames.join(',') +
" FROM " + reportConfig.NAME +
" WHERE " + reportConfig.CONDITIONS +
" DURING " + reportDateRange);
var rows = report.rows();
// Iterate over each row.
while (rows.hasNext()) {
var row = rows.next();
}
}
For better construction and migration of query, you may try to use the following tool below:
Hope this helps. Let me know if you have any questions.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Mar 31, 2022, 2:56:05 AM3/31/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello
New error:
| ReferenceError: reportDateRange is not defined at retrieveAdsReport (Code:282:18) at processReports (Code:230:21) at main (Code:59:28) |
My script
var CONFIG = {
BIGQUERY_PROJECT_ID: 'orbital-caldron-207714',
BIGQUERY_DATASET_ID: 'google_ads_report',
// Truncate existing data, otherwise will append.
TRUNCATE_EXISTING_DATASET: false,
TRUNCATE_EXISTING_TABLES: false,
// Back up reports to Google Drive.
WRITE_DATA_TO_DRIVE: false,
// Folder to put all the intermediate files.
DRIVE_FOLDER: 'CAMPAIGN',
// Default date range over which statistics fields are retrieved.
DEFAULT_DATE_RANGE: 'YESTERDAY',
// Lists of reports and fields to retrieve from Google Ads.
REPORTS: [{
NAME: 'campaign',
CONDITIONS: 'segments.date',
FIELDS: {'campaign.id' : 'STRING',
'campaign.name': 'STRING',
'campaign.status': 'STRING',
'metrics.cost_micros': 'STRING',
'segments.date' : 'STRING'
}}
],
};
// Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.
var MAX_INSERT_SIZE = 10;
/**
* Main method
*/
function main() {
createDataset();
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
var reportDateRange = CONFIG.DEFAULT_DATE_RANGE;
}
var folder;
if (CONFIG.WRITE_DATA_TO_DRIVE) {
folder = getDriveFolder();
}
// Get an account iterator.
var accountIterator = AdsManagerApp.accounts().withLimit(10).get();
var jobIdMap = {};
while (accountIterator.hasNext()) {
// Get the current account.
var account = accountIterator.next();
// Select the child account.
AdsManagerApp.select(account);
// Run reports against child account.
var accountJobIds = processReports(folder, account.getCustomerId());
jobIdMap[account.getCustomerId()] = accountJobIds;
}
waitTillJobsComplete(jobIdMap);
sendEmail(jobIdMap);
}
/**
* Creates a new dataset.
*
* If a dataset with the same id already exists and the truncate flag
* is set, will truncate the old dataset. If the truncate flag is not
* set, then will not create a new dataset.
*/
function createDataset() {
if (datasetExists()) {
if (CONFIG.TRUNCATE_EXISTING_DATASET) {
BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});
Logger.log('Truncated dataset.');
} else {
Logger.log('Dataset %s already exists. Will not recreate.',
CONFIG.BIGQUERY_DATASET_ID);
return;
}
}
// Create new dataset.
var dataSet = BigQuery.newDataset();
dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;
dataSet.datasetReference = BigQuery.newDatasetReference();
dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);
Logger.log('Created dataset with id %s.', dataSet.id);
}
/**
* Checks if dataset already exists in project.
*
* @return {boolean} Returns true if dataset already exists.
*/
function datasetExists() {
// Get a list of all datasets in project.
var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);
var datasetExists = false;
// Iterate through each dataset and check for an id match.
if (datasets.datasets != null) {
for (var i = 0; i < datasets.datasets.length; i++) {
var dataset = datasets.datasets[i];
if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {
datasetExists = true;
break;
}
}
}
return datasetExists;
}
/**
* Creates a new table.
*
* If a table with the same id already exists and the truncate flag
* is set, will truncate the old table. If the truncate flag is not
* set, then will not create a new table.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
*/
function createTable(reportConfig) {
if (tableExists(reportConfig.NAME)) {
if (CONFIG.TRUNCATE_EXISTING_TABLES) {
BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);
Logger.log('Truncated dataset %s.', reportConfig.NAME);
} else {
Logger.log('Table %s already exists. Will not recreate.',
reportConfig.NAME);
return;
}
}
// Create new table.
var table = BigQuery.newTable();
var schema = BigQuery.newTableSchema();
var bigQueryFields = [];
// Add account column to table.
var accountFieldSchema = BigQuery.newTableFieldSchema();
accountFieldSchema.description = 'AccountId';
accountFieldSchema.name = 'AccountId';
accountFieldSchema.type = 'STRING';
bigQueryFields.push(accountFieldSchema);
// Add each field to table schema.
var fieldNames = Object.keys(reportConfig.FIELDS);
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i];
var bigQueryFieldSchema = BigQuery.newTableFieldSchema();
bigQueryFieldSchema.description = fieldName;
bigQueryFieldSchema.name = fieldName;
bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName];
bigQueryFields.push(bigQueryFieldSchema);
}
schema.fields = bigQueryFields;
table.schema = schema;
table.friendlyName = reportConfig.NAME;
table.tableReference = BigQuery.newTableReference();
table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
table.tableReference.tableId = reportConfig.NAME;
table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
Logger.log('Created table with id %s.', table.id);
}
/**
* Checks if table already exists in dataset.
*
* @param {string} tableId The table id to check existence.
*
* @return {boolean} Returns true if table already exists.
*/
function tableExists(tableId) {
// Get a list of all tables in the dataset.
var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,
CONFIG.BIGQUERY_DATASET_ID);
var tableExists = false;
// Iterate through each table and check for an id match.
if (tables.tables != null) {
for (var i = 0; i < tables.tables.length; i++) {
var table = tables.tables[i];
if (table.tableReference.tableId == tableId) {
tableExists = true;
break;
}
}
}
return tableExists;
}
/**
* Process all configured reports
*
* Iterates through each report to: retrieve Google Ads data,
* backup data to Drive (if configured), load data to BigQuery.
* Returns a list of job ids for insert jobs.
*
* @param {Folder} folder Google Drive folder to store reports.
* @param {string} accountId Account Id to run reports.
*
* @return {Array.<string>} jobIds The list of job ids.
*
*/
function processReports(folder, accountId) {
var jobIds = [];
// Iterate over each report type.
for (var i = 0; i < CONFIG.REPORTS.length; i++) {
var reportConfig = CONFIG.REPORTS[i];
Logger.log('Running report %s for account %s', reportConfig.NAME,
accountId);
// Get data as an array of CSV chunks.
var csvData = retrieveAdsReport(reportConfig, accountId);
// If configured, back up data.
if (CONFIG.WRITE_DATA_TO_DRIVE) {
for (var r = 0; r < csvData.length; r++) {
var fileName = reportConfig.NAME + '_' + accountId + '_' + (r + 1);
saveCompressedCsvFile(folder, fileName, csvData[r]);
}
Logger.log('Exported data to Drive folder ' +
CONFIG.DRIVE_FOLDER + ' for report ' + fileName);
}
for (var j = 0; j < csvData.length; j++) {
// Convert to Blob format.
var blobData = Utilities.newBlob(csvData[j], 'application/octet-stream');
// Load data
var jobId = loadDataToBigquery(reportConfig, blobData, !j ? 1 : 0);
jobIds.push(jobId);
}
}
return jobIds;
}
/**
* Writes a CSV file to Drive, compressing as a zip file.
*
* @param {!Folder} folder The parent folder for the file.
* @param {string} fileName The name for the file.
* @param {string} csvData The CSV data to write to the file.
*/
function saveCompressedCsvFile(folder, fileName, csvData) {
var compressed = Utilities.zip([Utilities.newBlob(csvData)]);
compressed.setName(fileName);
folder.createFile(compressed);
}
/**
* Retrieves Google Ads data as csv and formats any fields
* to BigQuery expected format.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {string} accountId Account Id to run reports.
*
* @return {!Array.<string>} a chunked report in csv format.
*/
function retrieveAdsReport(reportConfig, accountId) {
var fieldNames = Object.keys(reportConfig.FIELDS);
var report = AdsApp.report(
"SELECT " + fieldNames.join(',') +
" FROM " + reportConfig.NAME +
" WHERE " + reportConfig.CONDITIONS +
" DURING " + reportDateRange);
var rows = report.rows();
var chunks = [];
var chunkLen = 0;
var csvRows = [];
var totalRows = 0;
// Header row
var header = 'AccountId,' + fieldNames.join(',');
csvRows.push(header);
chunkLen += Utilities.newBlob(header).getBytes().length + 1;
// Iterate over each row.
while (rows.hasNext()) {
var row = rows.next();
if (chunkLen > MAX_INSERT_SIZE) {
chunks.push(csvRows.join('\n'));
totalRows += csvRows.length;
chunkLen = 0;
csvRows = [];
}
var csvRow = [];
csvRow.push(accountId);
for (var i = 0; i < fieldNames.length; i++) {
var fieldName = fieldNames[i];
var fieldValue = row[fieldName];
var fieldType = reportConfig.FIELDS[fieldName];
// Strip off % and perform any other formatting here.
if (fieldType == 'FLOAT' || fieldType == 'INTEGER') {
if (fieldValue.charAt(fieldValue.length - 1) == '%') {
fieldValue = fieldValue.substring(0, fieldValue.length - 1);
}
fieldValue = fieldValue.replace(/,/g,'');
}
// Add double quotes to any string values.
if (fieldType == 'STRING') {
fieldValue = fieldValue.replace(/"/g, '""');
fieldValue = '"' + fieldValue + '"';
}
csvRow.push(fieldValue);
}
var rowString = csvRow.join(',');
csvRows.push(rowString);
chunkLen += Utilities.newBlob(rowString).getBytes().length + 1;
}
if (csvRows) {
totalRows += csvRows.length;
chunks.push(csvRows.join('\n'));
}
Logger.log('Downloaded ' + reportConfig.NAME + ' for account ' + accountId +
' with ' + totalRows + ' rows, in ' + chunks.length + ' chunks.');
return chunks;
}
/**
* Creates a new Google Drive folder. If folder name is already in
* use will pick the first folder with a matching name.
*
* @return {Folder} Google Drive folder to store reports.
*/
function getDriveFolder() {
var folders = DriveApp.getFoldersByName(CONFIG.DRIVE_FOLDER);
// Assume first folder is the correct one.
if (folders.hasNext()) {
Logger.log('Folder name found. Using existing folder.');
return folders.next();
}
return DriveApp.createFolder(CONFIG.DRIVE_FOLDER);
}
/**
* Creates a BigQuery insertJob to load csv data.
*
* @param {Object} reportConfig Report configuration including report name,
* conditions, and fields.
* @param {Blob} data Csv report data as an 'application/octet-stream' blob.
* @param {number=} skipLeadingRows Optional number of rows to skip.
*
* @return {string} jobId The job id for upload.
*/
function loadDataToBigquery(reportConfig, data, skipLeadingRows) {
// Create the data upload job.
var job = {
configuration: {
load: {
destinationTable: {
projectId: CONFIG.BIGQUERY_PROJECT_ID,
datasetId: CONFIG.BIGQUERY_DATASET_ID,
tableId: reportConfig.NAME
},
skipLeadingRows: skipLeadingRows ? skipLeadingRows : 0,
nullMarker: '--'
}
}
};
var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);
Logger.log('Load job started for %s. Check on the status of it here: ' +
'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME,
CONFIG.BIGQUERY_PROJECT_ID);
return insertJob.jobReference.jobId;
}
/**
* Polls until all jobs are 'DONE'.
*
* @param {Object.<string, Array.<string>>} jobIdMap A map, with key as the
* accountId, and value as an array of job ids.
*/
function waitTillJobsComplete(jobIdMap) {
var complete = false;
var remainingJobs = [];
var accountIds = Object.keys(jobIdMap);
for (var i = 0; i < accountIds.length; i++){
var accountJobIds = jobIdMap[accountIds[i]];
remainingJobs.push.apply(remainingJobs, accountJobIds);
}
while (!complete) {
if (AdsApp.getExecutionInfo().getRemainingTime() < 5){
Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') +
' are still incomplete.');
}
remainingJobs = getIncompleteJobs(remainingJobs);
if (remainingJobs.length == 0) {
complete = true;
}
if (!complete) {
Logger.log(remainingJobs.length + ' jobs still being processed.');
// Wait 5 seconds before checking status again.
Utilities.sleep(5000);
}
}
Logger.log('All jobs processed.');
}
/**
* Iterates through jobs and returns the ids for those jobs
* that are not 'DONE'.
*
* @param {Array.<string>} jobIds The list of running job ids.
*
* @return {Array.<string>} remainingJobIds The list of remaining job ids.
*/
function getIncompleteJobs(jobIds) {
var remainingJobIds = [];
for (var i = 0; i < jobIds.length; i++) {
var jobId = jobIds[i];
var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
if (getJob.status.state != 'DONE') {
remainingJobIds.push(jobId);
}
}
return remainingJobIds;
}
/**
* Sends a notification email that jobs have completed loading.
*
* @param {Object.<string, Array.<string>>} jobIdMap A map, with key as the
* accountId, and value as an array of job ids.
*/
function sendEmail(jobIdMap) {
var html = [];
html.push(
'<html>',
'<body>',
'<table width=800 cellpadding=0 border=0 cellspacing=0>',
'<tr>',
'<td colspan=2 align=right>',
"<div style='font: italic normal 10pt Times New Roman, serif; " +
"margin: 0; color: #666; padding-right: 5px;'>" +
'Powered by Google Ads Scripts</div>',
'</td>',
'</tr>',
"<tr bgcolor='#3c78d8'>",
'<td width=800>',
"<div style='font: normal 18pt verdana, sans-serif; " +
"padding: 3px 10px; color: white'>Ads Manager data load to " +
"Bigquery report</div>",
'</td>',
'</table>',
'<table width=800 cellpadding=0 border=1 cellspacing=0>',
"<tr bgcolor='#ddd'>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
"text-align: left'>Report</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
"text-align: left'>Account</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
"text-align: left'>JobId</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
"text-align: left'>Rows</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
"text-align: left'>State</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
"text-align: left'>Errors</td>",
'</tr>',
createTableRows(jobIdMap),
'</table>',
'</body>',
'</html>');
MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
'Ads data load to Bigquery Complete', '',
{htmlBody: html.join('\n')});
}
/**
* Creates table rows for email report.
*
* @param {Object.<string, Array.<string>>} jobIdMap A map, with key as the
* accountId, and value as an array of job ids.
*/
function createTableRows(jobIdMap) {
var html = [];
var accountIds = Object.keys(jobIdMap);
for (var i = 0; i< accountIds.length; i++){
var accountJobIds = jobIdMap[accountIds[i]];
for (var j = 0; j < accountJobIds.length; j++) {
var jobId = accountJobIds[j];
var job = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
var errorResult = '';
if (job.status.errorResult) {
errorResult = job.status.errorResult;
}
html.push('<tr>',
"<td style='padding: 0px 10px'>" +
job.configuration.load.destinationTable.tableId + '</td>',
"<td style='padding: 0px 10px'>" + accountIds[i] + '</td>',
"<td style='padding: 0px 10px'>" + jobId + '</td>',
"<td style='padding: 0px 10px'>" +
(job.statistics.load ? job.statistics.load.outputRows : 0) +'</td>',
"<td style='padding: 0px 10px'>" + job.status.state + '</td>',
"<td style='padding: 0px 10px'>" + errorResult + '</td>',
'</tr>');
}
}
return html.join('\n');
}
чт, 31 мар. 2022 г. в 09:09, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/HwPl2000000000000000000000000000000000000000000000R9LIFZ007VDmfPqqSPetvnrhDjNVnA%40sfdc.net.
Google Ads Scripts Forum Advisor
Apr 1, 2022, 4:02:56 AM4/1/22
to adwords...@googlegroups.com
Hello,
I observed that you have declared the `reportDateRange` variable in the main function of your script, but you utilized it on a different function(under retrieveAdsReport()) without passing it as a parameter which is the main reason why our system wasn’t able to recognize the `reportDateRange` upon usage on your query under line 282.
Having said that, would you be able to pass it as a parameter the same way you do with `reportConfig` in order for you to utilize the `reportDateRange` in other methods?
Regards,
Stanislav Egorov
Apr 1, 2022, 6:04:29 AM4/1/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
New day - new error :)
GoogleJsonResponseException: API call to bigquery.jobs.insert failed with error: No schema specified on job or table.
пт, 1 апр. 2022 г. в 11:02, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/E0Xys000000000000000000000000000000000000000000000R9NICO00bn_f8z4CSa2P4CrhZGj-vQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Apr 5, 2022, 5:10:22 AM4/5/22
to adwords...@googlegroups.com
Hello,
Based on your recent error, can you please confirm if the table on your Google cloud project is really existing and you have followed this configuration?
Regards,
Stanislav Egorov
Apr 5, 2022, 6:05:06 AM4/5/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
I changed TRUNCATE_EXISTING_TABLES: true in script
I am attaching a screenshot:
I changed TRUNCATE_EXISTING_TABLES: true in script
but the table is not overwritten when I restart the script. And this error is repeated.
вт, 5 апр. 2022 г. в 12:10, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/r3OKv000000000000000000000000000000000000000000000R9V05300WdcEM9XfSnq87_xV4Hd54A%40sfdc.net.
Google Ads Scripts Forum Advisor
Apr 7, 2022, 3:50:52 AM4/7/22
to adwords...@googlegroups.com
Hello,
Thank you for your response. I’ll be raising this issue to our internal team for further investigation and troubleshooting. Rest assured that someone from our team will get back to you with an update.
Regards,
Google Ads Scripts Forum Advisor
Apr 7, 2022, 4:07:46 AM4/7/22
to adwords...@googlegroups.com
Hello,
Kindly disregard my previous email as I have few questions regarding this.
Can you please confirm first if you’re executing our Export Google Ads Reports into BigQuery solution script using the new script experience when you encounter the error? Also, have you tried to run the said solution script using the legacy version of the script(new script experience toggled off in the script interface)? If not, can you please try and let me know if you will still encounter the same issue?
Regards,
Stanislav Egorov
Apr 7, 2022, 4:21:50 AM4/7/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
I disabled the New scripts experience. And now I get a new error: API call to bigquery.jobs.insert failed with error: No schema specified on job or table.
чт, 7 апр. 2022 г. в 11:07, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/f-pQy000000000000000000000000000000000000000000000R9YMKS000vdw5LKAT2q_6ph6Cckmxg%40sfdc.net.
Google Ads Scripts Forum Advisor
Apr 8, 2022, 6:32:48 AM4/8/22
to adwords...@googlegroups.com
Hi,
Thank you for providing updates to your concern.
It appears that the error you've encountered is Apps scripts specific, not Google Ads scripts. With this, I am afraid that we would not be able to provide support here as it is outside of our scope already. That said, I would suggest reaching out to the Google Apps scripts team via this link as they are more equipped to provide support here.
Regards,
ref:_00D1U1174p._5004Q2XnCnX:ref
Thank you for providing updates to your concern.
It appears that the error you've encountered is Apps scripts specific, not Google Ads scripts. With this, I am afraid that we would not be able to provide support here as it is outside of our scope already. That said, I would suggest reaching out to the Google Apps scripts team via this link as they are more equipped to provide support here.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Apr 11, 2022, 2:31:23 AM4/11/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
I understand correctly that I took the script from your official site, did everything as you said, talked with support for a month, and still could not run it. You tell me to contact another department and they tell me that they can not help me and need to go to Stack Overflow.
What's the point then, if the script didn't run and support couldn't solve my issue?
What's the point then, if the script didn't run and support couldn't solve my issue?
пт, 8 апр. 2022 г. в 13:32, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/4UZ56000000000000000000000000000000000000000000000RA0NYI00hiO6ntVARfuRfkDSz-jDOQ%40sfdc.net.
Google Ads Scripts Forum Advisor
Apr 12, 2022, 3:31:51 AM4/12/22
to adwords...@googlegroups.com
Hello,
I've raised this (including the reports and discussion thus far) to the rest of the team for further investigation and to get their insight as well. I'll update this thread the soonest the team provide their feedback.
Regards,
ref:_00D1U1174p._5004Q2XnCnX:ref
I've raised this (including the reports and discussion thus far) to the rest of the team for further investigation and to get their insight as well. I'll update this thread the soonest the team provide their feedback.
Regards,
|
||||||
ref:_00D1U1174p._5004Q2XnCnX:ref
Stanislav Egorov
Apr 25, 2022, 8:22:55 AM4/25/22
to Google Ads Scripts Forum on behalf of adsscripts
Hello,
It's been almost two weeks, is there still no news?
вт, 12 апр. 2022 г. в 10:31, Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>:
--
-- 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 a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/fv-0cmW-PWc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/ZkV7p000000000000000000000000000000000000000000000RA7U8X00GmSVJEOwQUW8U93dpXbzFg%40sfdc.net.
Google Ads Scripts Forum Advisor
May 4, 2022, 6:41:22 AM5/4/22
to adwords...@googlegroups.com
Hello,
I'm afraid that this is still under investigation. I'll update this thread the soonest the team share their findings.
I'm afraid that this is still under investigation. I'll update this thread the soonest the team share their findings.
Google Ads Scripts Forum Advisor
Mar 24, 2023, 6:45:28 AM3/24/23
to adwords...@googlegroups.com
Hi,
Just circling back to this issue. Could you please confirm if you're still in need of assistance with this? We've heard back from our internal team and they've stated that this solution script has been deprecated in the new script experience.
Best regards,
 |
Google Ads Scripts Team |
ref:_00D1U1174p._5004Q2XnCnX:ref
Reply all
Reply to author
Forward
0 new messages