Untitled

var spreadsheetID = '1f3wl0IrgrLgRdA36ynovaAOQAV-niqdk4gsCpw_QWSo';
var sheetIndex = 0;
function main() {
updateReportByYear('2021');
updateReportByYear('2022'); 
}
function updateReportByYear(year){
try {
var rw = 3
if (year == "2020") {
rw = rw + 45
}else if (year == "2021"){
rw = rw + 15
}
var isCurMonth = false
var currentMonth = Utilities.formatDate(new Date(), 'GMT+1', "MMM");// Jan, Feb
var currentDay = Utilities.formatDate(new Date(), 'GMT+1', "dd");
var startDate = new Date('01/01/' + year);
var endDate = new Date('01/31/' + year);
campaignReport(startDate,endDate,'January',year,rw)

startDate = new Date('02/01/' + year);
if (year == 2021){
endDate = new Date('02/29/' + year);

}else{
endDate = new Date('02/28/' + year);}
campaignReport(startDate,endDate,'February',year,rw+1)

startDate = new Date('03/01/' + year);
endDate = new Date('03/31/' + year);
campaignReport(startDate,endDate,'March',year,rw+2)

startDate = new Date('04/01/' + year);
endDate = new Date('04/30/' + year);
campaignReport(startDate,endDate,'April',year,rw+3)

startDate = new Date('05/01/' + year);
endDate = new Date('05/31/' + year);
campaignReport(startDate,endDate,'May',year,rw+4)

startDate = new Date('06/01/' + year);
endDate = new Date('06/30/' + year);
campaignReport(startDate,endDate,'June',year,rw+5)

startDate = new Date('07/01/' + year);
endDate = new Date('07/31/' + year);
campaignReport(startDate,endDate,'July',year,rw+6)

startDate = new Date('08/01/' + year);
endDate = new Date('08/31/' + year);
campaignReport(startDate,endDate,'August',year,rw+7)

startDate = new Date('09/01/' + year);
endDate = new Date('09/30/' + year);
campaignReport(startDate,endDate,'September',year,rw+8)

startDate = new Date('10/01/' + year);
endDate = new Date('10/31/' + year);
campaignReport(startDate,endDate,'October',year,rw+9)

startDate = new Date('11/01/' + year);
endDate = new Date('11/30/' + year);
campaignReport(startDate,endDate,'November',year,rw+10)

startDate = new Date('12/01/' + year);
endDate = new Date('12/31/' + year);
campaignReport(startDate,endDate,'December',year,rw+11)
} catch(err) {
Logger.log(err.message + ' ' + err.lineNumber)
}
}
function campaignReport(startDate,endDate,tabName,year,rw) {
var sheet = SpreadsheetApp.openById(spreadsheetID).getSheets()[sheetIndex];
var cols = 'CampaignId,AdvertisingChannelType,Cost,CurrentModelAttributedConversionValue,Interactions,AverageCpc,Clicks';
var report = 'CAMPAIGN_PERFORMANCE_REPORT';
startDate = Utilities.formatDate(startDate, 'GMT+1', 'YYYYMMdd');
endDate = Utilities.formatDate(endDate, 'GMT+1', 'YYYYMMdd');
var where = ' where CampaignStatus = ENABLED '
var query = ['select',cols,'from',report, ' DURING ',startDate, ',', endDate].join(' ');
Logger.log(query)
var report_iter = AdsApp.report(query).rows()
var Cost = 0, ConvVal = 0, Interactions = 0, cpc = 0, clicks = 0
while (report_iter.hasNext()) {
var row = report_iter.next();
Cost = Cost + parseFloat(row['Cost'].replace(',',''))
ConvVal = ConvVal + parseFloat(row['CurrentModelAttributedConversionValue'].replace(',',''))
Interactions = Interactions + parseFloat(row['Interactions'].replace(',',''))
clicks = clicks + parseFloat(row['Clicks'].replace(',',''))
}
if (Cost > 0 && clicks > 0) {
cpc = parseFloat(Cost) / parseFloat(clicks)
cpc = cpc.toFixed(2);
}
sheet.getRange(rw, 2).setValue(cpc);
sheet.getRange(rw, 3).setValue(Interactions);
sheet.getRange(rw, 4).setValue(Cost);
sheet.getRange(rw, 5).setValue(ConvVal);
}