APPSCRIPT VARIABILI PACK

function facebookAdsReporting() {

  //Retrieve all campaign ids
var adAccountId = "tuoaccount id facebook //tuo account fb"
var url = "https://graph.facebook.com/v6.0/act_" + adAccountId + "/campaigns?access_token=TUOTOKEN"
var response = UrlFetchApp.fetch(url)
var json = JSON.parse(response)
var rawData = json.data
var array = []
 for (i = 0 ; i < rawData.length ; i++) {
   var data = rawData[i]
   var test = JSON.stringify(data)
   var str = test.substring(0,test.length-2).substring(7)
   array.push(str)
 }
Logger.log(array)

   //Get insights from each campaign

for (j = 0 ; j < array.length ; j++) {
    var campaignId = array[j]
    var urlCampaignInsights = "https://graph.facebook.com/v6.0/" + campaignId + "/insights?fields=campaign_name%2cimpressions%2cspend%2ccpc%2cctr%2ccpm%2cclicks%2ccpp%2cfrequency%2creach%2cconversions&date_preset=last_90d&access_token=TUOTOKEN"
    var responseCampaignInsights = UrlFetchApp.fetch(urlCampaignInsights)
    var json = JSON.parse(responseCampaignInsights)
    var data = json.data
    if ( data.length > 0 ) {
      var et = JSON.stringify(data)
      var sub = et.substring(1,et.length-1)
      var campaignData = JSON.parse(sub)
      var campaignName = campaignData.campaign_name
      var impressions = campaignData.impressions
      var spend = campaignData.spend
      var cpc = campaignData.cpc
      var cpm = campaignData.cpm
      var ctr = campaignData.ctr
      var clicks = campaignData.clicks
      var cpp = campaignData.cpp
      var frequency = campaignData.frequency
      var reach = campaignData.reach

      Logger.log(campaignData)

    //Update spreadsheet

      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
      var sheet = spreadsheet.getSheetByName('Facebook Ads Campaigns')
        sheet.getRange(1, 1).setValue("Campaign Name")
        sheet.getRange(1, 2).setValue("impressions")
        sheet.getRange(1, 3).setValue("spend")
        sheet.getRange(1, 4).setValue("cpc")
        sheet.getRange(1, 5).setValue("cpm")
        sheet.getRange(1, 6).setValue("ctr")
        sheet.getRange(1, 7).setValue("clicks")
        sheet.getRange(1, 8).setValue("cpp")
        sheet.getRange(1, 9).setValue("frequency")
        sheet.getRange(1, 10).setValue("reach")
        sheet.getRange(j+2, 1).setValue(campaignName)
        sheet.getRange(j+2, 2).setValue(impressions)
        sheet.getRange(j+2, 3).setValue(spend)
        sheet.getRange(j+2, 4).setValue(cpc)
        sheet.getRange(j+2, 5).setValue(cpm)
        sheet.getRange(j+2, 6).setValue(ctr)
        sheet.getRange(j+2, 7).setValue(clicks)
        sheet.getRange(j+2, 8).setValue(cpp)
        sheet.getRange(j+2, 9).setValue(frequency)
        sheet.getRange(j+2, 10).setValue(reach)


        }
 }

    //Get insights from Ad Account

    var urlAdAccountInsights = "https://graph.facebook.com/v6.0/act_" + adAccountId + "/insights?fields=campaign_name%2cimpressions%2cspend%2ccpc%2cctr%2ccpm%2cclicks%2ccpp%2cfrequency%2creach&date_preset=last_90d&access_token=TUOTOKEN"
    var responseCampaignInsights = UrlFetchApp.fetch(urlAdAccountInsights)
    var json = JSON.parse(responseCampaignInsights)
    var data = json.data
    if ( data.length > 0 ) {
      var et = JSON.stringify(data)
      var sub = et.substring(1,et.length-1)
      var campaignData = JSON.parse(sub)
      var campaignName = campaignData.campaign_name
      var impressions = campaignData.impressions
      var spend = campaignData.spend
      var cpc = campaignData.cpc
      var cpm = campaignData.cpm
      var ctr = campaignData.ctr
      var clicks = campaignData.clicks
      var cpp = campaignData.cpp
      var frequency = campaignData.frequency
      var reach = campaignData.reach

      Logger.log(campaignData)

    //Update spreadsheet

      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
      var sheet = spreadsheet.getSheetByName('Facebook Ads Ad Account')

        sheet.getRange(1, 1).setValue("Campaign Name")
        sheet.getRange(1, 2).setValue("impressions")
        sheet.getRange(1, 3).setValue("spend")
        sheet.getRange(1, 4).setValue("cpc")
        sheet.getRange(1, 5).setValue("cpm")
        sheet.getRange(1, 6).setValue("ctr")
        sheet.getRange(1, 7).setValue("clicks")
        sheet.getRange(1, 8).setValue("cpp")
        sheet.getRange(1, 9).setValue("frequency")
        sheet.getRange(1, 10).setValue("reach")

      var array = sheet.getRange("b1:b").getValues()
      var lastRow = array.filter(String).length

        sheet.getRange(lastRow+1, 1).setValue(campaignName)
        sheet.getRange(lastRow+1, 2).setValue(impressions)
        sheet.getRange(lastRow+1, 3).setValue(spend)
        sheet.getRange(lastRow+1, 4).setValue(cpc)
        sheet.getRange(lastRow+1, 5).setValue(cpm)
        sheet.getRange(lastRow+1, 6).setValue(ctr)
        sheet.getRange(lastRow+1, 7).setValue(clicks)
        sheet.getRange(lastRow+1, 8).setValue(cpp)
        sheet.getRange(lastRow+1, 9).setValue(frequency)
        sheet.getRange(lastRow+1, 10).setValue(reach)

     //Add yesterday's date

        var yesterday = new Date(Date.now() - 1000*60*60*24)
        sheet.getRange(lastRow+1, 1).setValue(yesterday)
        }
 }

function facebookAccessTokenExpiringAlert() {

 //Get token date expiration

 var token = "TUO TOKEN"
 var clientSecret = "CHIAVESEGRETA"
 var clientId = "CLIENTID"
 var url = "https://graph.facebook.com/v6.0/oauth/access_token?grant_type=fb_exchange_token&client_id=" + clientId + "&client_secret=" + clientSecret + "&fb_exchange_token=" + token
 var response = UrlFetchApp.fetch(url)
 var json = JSON.parse(response)
 var expiresIn = json.expires_in
 Logger.log(expiresIn)

 //Send mail 7 days before token expires

 var mailAdress ="TUAEMAIL"
 var subject = "Your Facebook Token will Expire in 7 Days"
 var message = "Hi, don't forget to renew your Facebook Token"
 if (expiresIn < 604800){
 MailApp.sendEmail(mailAdress, subject , message)
 }
}