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(https://docs.google.com/spreadsheets/d/1qmnY5fkvOFgEZHqQ9U
FlOmRRjC0yTFPZTJfBHYad3DE/edit#gid=2125645366);
// Select the sheet where you want to update data.
var sheet = spreadsheet.getSheetByName('Google'); // Replace 'Sheet1' 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(25/09/2023); // Set it to the current date.
var startDate = new Date(24/09/2023); // Set it to the start of the previous day.
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'; // Replace with your actual JWT token.
var campaignSpendData = getCampaignSpend(startDate, endDate,
jtKt1g1awWhCNRGCUJs8Uw);
// 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 using JWT
token.
function getCampaignSpend(startDate, endDate, jtKt1g1awWhCNRGCUJs8Uw) {
// Use the JWT token for authentication with the Google Ads API.
// Make API requests to fetch campaign spend data.
// Replace this with your API calls to retrieve the data.
// Function to retrieve campaign spend data from the Google Ads API using JWT
token.
function getCampaignSpend(startDate, endDate, jtKt1g1awWhCNRGCUJs8Uw) {
// Define the URL of the Google Ads API endpoint for the report you need.
var apiUrl =
'https://googleads.googleapis.com/v9/customers/YOUR_CUSTOMER_ID/googleAds:searchStr
eam';
// Set up 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.
};
// Make a POST request to the Google Ads API.
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.
}
return spendData;
}
}
}
