script_Bing.js

/**
* @name Week spend by Campaign
*
* 
*/

const credentials = {
    accessToken: '',
    clientId: 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
    clientSecret: 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
    refreshToken: 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
};

const spreadsheetRows = [];
var sheetRows = [["Campaign","Spend"]];
const sheetResults = [];
const dateTimeStr = new Date().toISOString().replace(/\u200e/g, '');
const spreadsheetName = `BingAd_Spend_${dateTimeStr}`;
var spreadsheetId;
var createSheetsResponse;
var sheetsByName;
var sheetId;
function main() {    
    sheetResults.push({ accountResult: "Company", sheetName: "Report" });    
    spreadsheetId = createFileIfNotExists(spreadsheetName, true);
    createSheetsResponse = createSheets(spreadsheetId, sheetResults.map(x => x.sheetName));
    sheetsByName = createSheetsResponse.updatedSpreadsheet.sheets.reduce((map, sheet) => (map[sheet.properties.title] = sheet, map), {});
    for (const sheetResult of sheetResults) {
        sheetId = sheetsByName[sheetResult.sheetName].properties.sheetId;
    }
    spreadsheetRows.push({ sheetId: sheetId, rows: [["Campaign","Spend"]] });
    var campaigns = ["Campaign1","Campaign2"];
    campaigns.forEach(getSpends); 
    writeRowsToSpreadsheet(spreadsheetRows, spreadsheetId);
}

function getSpends(value) {
    var total_cost_per_product=0; 
    var iterator = AdsApp.campaigns().withCondition("Name CONTAINS '"+value+"'").forDateRange('LAST_WEEK').get();       
    while (iterator.hasNext()) {
        var campaign = iterator.next();
        var metrics = campaign.getStats();
        var cost = metrics.getCost();         
        total_cost_per_product+=cost; 
    }
    Logger.log(`Total cost per product ${value}: ${total_cost_per_product.toFixed(2)}`);           
    //sheetRows.push([`${value}`,`${total_cost_per_product}`]);        
    //spreadsheetRows.push({ sheetId: sheetId, rows: sheetRows });
    spreadsheetRows.push({ sheetId: sheetId, rows: [[`${value}`,`${total_cost_per_product.toFixed(2)}`]] });    
    
}

// Calls to get Google services.
 
const getSheetsApi = (() => {
    let sheetsApi;
    return () => sheetsApi || (sheetsApi = GoogleApis.createSheetsService(credentials));
})();
 
const getDriveApi = (() => {
    let driveApi;
    return () => driveApi || (driveApi = GoogleApis.createDriveService(credentials));
})();
 
const getGmailApi = (() => {
    let gmailApi;
    return () => gmailApi || (gmailApi = GoogleApis.createGmailService(credentials));
})();

// Creates each sheet in the spreadsheet.

function createSheets(spreadsheetId, sheetNames) {
  const requests = sheetNames.map(x => ({ addSheet: { properties: { title: x } } }));

  const response = getSheetsApi().spreadsheets.batchUpdate({ spreadsheetId: spreadsheetId }, {
    requests: requests,
    includeSpreadsheetInResponse: true
  }).result;

  return response;
}

// Writes the entities and broken URLs to each sheet.

function writeRowsToSpreadsheet(spreadsheetRows, spreadsheetId) {
  const requests = spreadsheetRows.map(sheetRows => ({
    appendCells: {
      sheetId: sheetRows.sheetId,
      rows: sheetRows.rows.map(row => ({
        values: row.map(columnValue => ({
          userEnteredValue: { stringValue: columnValue ? columnValue.toString() : columnValue }
        }))
      })),
      fields: '*'
    }
  }));

  getSheetsApi().spreadsheets.batchUpdate({ spreadsheetId: spreadsheetId }, {
    requests: requests,
    includeSpreadsheetInResponse: false
  });
}

// Returns a link to the spreadsheet. The script includes 
// the link in the email notification.

function shareFileWithLink(fileId) {
  // Set up permissions, so the recipient doesn't have to
  // sign in.

  getDriveApi().permissions.create({ fileId: fileId }, {
    type: 'anyone',
    role: 'reader',
    allowFileDiscovery: false
  });

  const fileResponse = getDriveApi().files.get({ fileId: fileId, fields: 'webViewLink' }).result;

  return fileResponse.webViewLink;
}

function findFileId(fileName) {
  const req = escape(`name = '${fileName}'`);

  const searchResult = getDriveApi().files.list({ q: req }).result;

  if (searchResult.files.length > 0) {
    return searchResult.files[0].id;
  }

  return null;
}

function createFileIfNotExists(fileName, isSpreadsheet) {
  const existingFileId = findFileId(fileName);

  if (existingFileId) {
    return existingFileId;
  }

  const createResult = getDriveApi().files.create({}, {
    name: fileName,
    mimeType: isSpreadsheet ? 'application/vnd.google-apps.spreadsheet' : 'application/vnd.google-apps.document'
  }).result;

  return createResult.id;
}

function saveObject(obj, fileName) {
  const fileId = createFileIfNotExists(fileName, false);

  getDriveApi().files.update({ fileId: fileId }, JSON.stringify(obj), { uploadType: 'simple', contentType: 'text/plain' });
}

function loadObject(fileName) {
  const fileId = findFileId(fileName);

  if (!fileId) {
    throw new Error(`File ${fileName} not found`);
  }

  const fileData = getDriveApi().files.export({ fileId: fileId, mimeType: 'text/plain' }).body.trim();

  if (fileData) {
    return JSON.parse(fileData.trim());
  } else {
    return null;
  }
}


// Common Google library code that all Scripts that access Google
// services will include.
 
var GoogleApis;
(function (GoogleApis) {
    GoogleApis.createSheetsService = credentials => createService("https://sheets.googleapis.com/$discovery/rest?version=v4", credentials);
    GoogleApis.createDriveService = credentials => createService("https://www.googleapis.com/discovery/v1/apis/drive/v3/rest", credentials);
    GoogleApis.createGmailService = credentials => createService("https://www.googleapis.com/discovery/v1/apis/gmail/v1/rest", credentials);
 
    // Creation logic based on https://developers.google.com/discovery/v1/using#usage-simple
    function createService(url, credentials) {
        const content = UrlFetchApp.fetch(url).getContentText();
        const discovery = JSON.parse(content);
        const accessToken = getAccessToken(credentials);
        const standardParameters = discovery.parameters;
        const service = build(discovery, {}, discovery['rootUrl'], discovery['servicePath'], standardParameters, accessToken);
        return service;
    }
 
    function createNewMethod(method, rootUrl, servicePath, standardParameters, accessToken) {
        return (urlParams, body, uploadParams) => {
            let urlPath = method.path;
            if (uploadParams) {
                if (!method.supportsMediaUpload) {
                    throw new Error(`Media upload is not supported`);
                }
                const uploadProtocols = method.mediaUpload.protocols;
                const uploadType = uploadParams.uploadType;
                switch (uploadType) {
                    case 'simple':
                        const simpleProtocol = uploadProtocols.simple;
                        if (!simpleProtocol) {
                            throw new Error(`Upload protocol ${uploadType} is not supported`);
                        }
                        urlPath = simpleProtocol.path;
                        break;
                    case 'resumable':
                        const resumableProtocol = uploadProtocols.resumable;
                        if (!resumableProtocol) {
                            throw new Error(`Upload protocol ${uploadType} is not supported`);
                        }
                        urlPath = resumableProtocol.path;
                        break;
                    default:
                        throw new Error(`Unknown upload type ${uploadType}`);
                }
            }
            const queryArguments = [];
            for (const name in urlParams) {
                const paramConfg = method.parameters[name] || standardParameters[name];
                if (!paramConfg) {
                    throw new Error(`Unexpected url parameter ${name}`);
                }
                switch (paramConfg.location) {
                    case 'path':
                        urlPath = urlPath.replace('{' + name + '}', urlParams[name]);
                        break;
                    case 'query':
                        queryArguments.push(`${name}=${urlParams[name]}`);
                        break;
                    default:
                        throw new Error(`Unknown location ${paramConfg.location} for url parameter ${name}`);
                }
            }
            if (uploadParams) {
                queryArguments.push(`uploadType=${uploadParams.uploadType === 'simple' ? 'media' : uploadParams.uploadType}`);
            }
            let url = rootUrl;
            if (urlPath.startsWith('/')) {
                url += urlPath.substring(1);
            } else {
                url += servicePath + urlPath;
            }
            if (queryArguments.length > 0) {
                url += '?' + queryArguments.join('&');
            }
            const payload = uploadParams ? body : JSON.stringify(body);
            const contentType = uploadParams ? uploadParams.contentType : 'application/json';
            const fetchParams = { contentType: contentType, method: method.httpMethod, payload: payload, headers: { Authorization: `Bearer ${accessToken}` }, muteHttpExceptions: true };
            const httpResponse = UrlFetchApp.fetch(url, fetchParams);            
            const responseContent = httpResponse.getContentText();
            const responseCode = httpResponse.getResponseCode();
            let parsedResult;
            try {
                parsedResult = JSON.parse(responseContent);
            } catch (e) {
                parsedResult = false;
            }
            const response = new Response(parsedResult, responseContent, responseCode);
            if (responseCode >= 200 && responseCode <= 299) {
                return response;
            }
            throw new Error(response.toString());
        }
    }
 
    function Response(result, body, status) {
        this.result = result;
        this.body = body;
        this.status = status;
    }
    Response.prototype.toString = function () {
        return this.body;
    }
 
    function build(discovery, collection, rootUrl, servicePath, standardParameters, accessToken) {
        for (const name in discovery.resources) {
            const resource = discovery.resources[name];
            collection[name] = build(resource, {}, rootUrl, servicePath, standardParameters, accessToken);
        }
        for (const name in discovery.methods) {
            const method = discovery.methods[name];
            collection[name] = createNewMethod(method, rootUrl, servicePath, standardParameters, accessToken);
        }
        return collection;
    }
 
    function getAccessToken(credentials) {
        if (credentials.accessToken) {
            return credentials.accessToken;
        }
        const tokenResponse = UrlFetchApp.fetch('https://www.googleapis.com/oauth2/v4/token', { method: 'post', contentType: 'application/x-www-form-urlencoded', muteHttpExceptions: true, payload: { client_id: credentials.clientId, client_secret: credentials.clientSecret, refresh_token: credentials.refreshToken, grant_type: 'refresh_token' } });
        const responseCode = tokenResponse.getResponseCode();
        const responseText = tokenResponse.getContentText();
        if (responseCode >= 200 && responseCode <= 299) {
            const accessToken = JSON.parse(responseText)['access_token'];
            return accessToken;
        }
        throw new Error(responseText);
    }
})(GoogleApis || (GoogleApis = {}));
class Base64 {
    static encode(input) {
        const keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
        let output = "";
        let chr1, chr2, chr3, enc1, enc2, enc3, enc4;
        let i = 0;
        input = this.utf8Encode(input);
        while (i < input.length) {
            chr1 = input.charCodeAt(i++);
            chr2 = input.charCodeAt(i++);
            chr3 = input.charCodeAt(i++);
            enc1 = chr1 >> 2;
            enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
            enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
            enc4 = chr3 & 63;
            if (isNaN(chr2)) {
                enc3 = enc4 = 64;
            }
            else if (isNaN(chr3)) {
                enc4 = 64;
            }
            output = output + keyStr.charAt(enc1) + keyStr.charAt(enc2) + keyStr.charAt(enc3) + keyStr.charAt(enc4);
        }
        return output.replace(/\+/g, "-").replace(/\//g, "_").replace(/=+$/, "");
    }
    static utf8Encode(input) {
        input = input.replace(/\r\n/g, "\n");
        let utftext = "";
        for (let n = 0; n < input.length; n++) {
            const c = input.charCodeAt(n);
            if (c < 128) {
                utftext += String.fromCharCode(c);
            }
            else if ((c > 127) && (c < 2048)) {
                utftext += String.fromCharCode((c >> 6) | 192);
                utftext += String.fromCharCode((c & 63) | 128);
            }
            else {
                utftext += String.fromCharCode((c >> 12) | 224);
                utftext += String.fromCharCode(((c >> 6) & 63) | 128);
                utftext += String.fromCharCode((c & 63) | 128);
            }
        }
        return utftext;
    }
}