function main() {
// Define the Google Sheet where you want to update the data.
var spreadsheetUrl =
'https://docs.google.com/spreadsheets/d/1qmnY5fkvOFgEZHqQ9UFlOmRRjC0yTFPZTJfBHYad3D
E/edit#gid=2125645366';
// Open the specified Google Sheet.
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
// Select the sheet where you want to update data.
var sheet = spreadsheet.getSheetByName('Google'); // Replace 'Google' with your
sheet's name.
// Get the current date.
var currentDate = new Date();
// Calculate the start and end dates for the reporting period (e.g., the last 24
hours).
var endDate = new Date();
var startDate = new Date();
startDate.setDate(currentDate.getDate() - 1); // Adjusted for the previous day.
// Fetch the campaign spend data using the Google Ads API and your JWT token.
var jwtToken = 'jtKt1g1awWhCNRGCUJs8Uw';
var apiUrl =
'https://googleads.googleapis.com/v9/customers/618-717-4174/googleAds:searchStream'
;
// Define the headers for the API request, including authorization with the JWT
token.
var headers = {
'Authorization': 'Bearer ' + jwtToken,
'Content-Type': 'application/json',
};
// Define the request parameters, such as the query and date range.
var requestBody = {
'query': 'SELECT campaign.id, campaign.name, metrics.impressions,
metrics.clicks, metrics.cost_micros ' +
'FROM keyword_view ' +
'WHERE segments.date DURING ' + startDate.toISOString() + ',' +
endDate.toISOString(),
'pageSize': 1000, // Adjust as needed.
};
// Fetch the campaign spend data.
var startDate = new Date(currentDate); // Clone the currentDate to startDate.
startDate.setDate(startDat.getDate() - 1); // Adjusted for the previous day.
// Clear existing data in the sheet (optional).
sheet.clear();
// Update the Google Sheet with the retrieved data.
updateSheetWithSpendData(sheet, campaignSpendData);
}
// Function to retrieve campaign spend data from the Google Ads API.
var campaignSpendData = getCampaignSpend(apiUrl, headers, requestBody);
// Make a POST request to the Google Ads API using headers.
var response = UrlFetchApp.fetch(apiUrl, {
'method': 'post',
'headers': headers,
'payload': JSON.stringify(requestBody),
});
// Parse the JSON response.
var responseData = JSON.parse(response.getContentText());
// Extract the data you need from the API response and format it as an array.
var spendData = [];
for (var i = 0; i < responseData.length; i++) {
var row = responseData[i];
spendData.push([row.campaign.name, row.metrics.cost_micros / 1000000]); //
Convert micros to dollars.
}
// Function to update the Google Sheet with campaign spend data.
function updateSheetWithSpendData(sheet, data) {
// Define the header row.
var headers = ['Campaign Name', 'Spend'];
// Insert the headers into the first row of the sheet.
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
// Insert the data starting from the second row.
if (data.length > 0) {
sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}
}
