Automated Cost Data Import – Facebook To Google Analytics

  1. Home
  2. Blog
  3. Posts
  4. Automated Cost Data Import – Facebook To Google Analytics
222 Comments Jul 03, 2017 Web / Google Analytics

Last Updated on February 12, 2020 by Ritwik B

Here, we’ll be looking at an automated setup to export Facebook Ads Data in Google Sheet & then uploading it in google analytics.

You can call this a DIY Tool.

The process is simple & we’ll be using Facebook Marketing APIs & Google Analytics APIs to do the same.

You can get started in a few minutes.

Here’s what you’ll get

  • Full Quota Usage: Export Unlimited rows of Facebook Ads data.
  • Reporting Automation: Schedule Your Facebook Reports Hourly/Daily/Weekly/Monthly.
  • Unlimited Imports To GA: Import Facebook Ads Data in Google Analytics
  • UTM Feature: Export facebook UTM values in the spreadsheet.
  • Currency Conversion: Convert Facebook Ads currency while export
  • Emailers: Send emails on upload.
  • & More…

Exited?? Let start….

Facebook_Cost_Data_Import - Digishuffle

Here’s what you’ll get in the spreadsheet.

#1 Exporting Facebook Data into Sheets

Free Facebook Reporting Tool

 

 

#2 Uploading Facebook Data into Google Analytics

Facebook-Cost-Data-Import = Digishuffle

 

#3 Reporting Automation

You can also use triggers in App Scripts to automate the facebook exporting & google analytics uploading process. More on this later…

 

Here, I have used:

Also, I am assuming the Ad URLs in Facebook are properly tagged with UTMs. 

So, lets get started with the setup.

Facebook Cost Data Import in Google Analytics – Setup Process

Step – 1 (New Library Version)

  • Copy the sheet https://docs.google.com/spreadsheets/d/1fwOulINuLvDnvUabjCTNtdrz3wVfC8_UrRwQ8zEYYxo/edit?usp=sharing (Click on File > Make a Copy)
  • Once copied, you can see now see the new menu named “Reports”.  If not, just refresh the copied sheet.
  • The next step is to go to “Tools” > “Script Editor” (in the sheet menu).  You can now see the code where the CLIENT_ID & CLIENT_SECRET is blank. To get that we’ll need to create a Facebook app.
  • After this step, you can directly move to step – 2. (Skip step -1 old version)

Step – 1 (Old Version)

Create_HTML_file_-_Facebook_Reporting_Tool

Facebook-Cost-Data-Import - Create Spreadsheet - Digishuffle


(Optional: Only for Google Analytics Import Feature)

  • If you want to import data to analytics, we’ll have to turn on the analytics API
  • Go To Resources > Advanced Google Services > Turn on Analytics API
  • Click on Google API console > Enable the Analytics API.
    (Click below gif to enlarge)


Google Analytics API - Facebook Cost Data

Step – 2

  • Go To Facebook Apps
  • Create & Name your App (I named ‘Ads to Spreadsheet’) > Click Create App ID.
Facebook_App_Creation - Digishuffle

 

  • Go To Facebook App > Settings > Basics
  • Include ‘script.google.com’ in App Domains.
  • Add a website & include ‘https://script.google.com/’

  • Open the App > Go To Dashboards.
  • Copy & Paste the
    • App ID as CLIENT_ID
    • App Secret as CLIENT_SECRET
      in Script
Facebook_ID_&_Secret - Digishuffle

 

Step – 3

  • In the Spreadsheet, click on Reports > OAuth Redirect URI & copy the URL.

Facebook_Reporting_Tool_-_Valid_oAuth_redirectURI

  • Go To Facebook App > Click Add Product > Click Facebook Login Setup

  • Go To Facebook Login > Settings
  • Now, paste the copied URL in the Valid oAuth Redirect URIs & save the settings.

 

And its DONE…!!!! Try Opening your spreadsheet & Authorizing your facebook account. Select an account & try exporting the data.

To upload the data to GA, simply set “isGaUpload” to true & “useHeaders” to false. Now create the cost data import as follows:

Step – 1: (Optional: Only For GA Upload Feature)

  • You can find the google analytics account id & property id in the account settings & property settings respectively.
Google_Analytics_Cost_Data_Import_Dataset_ID - Digishuffle

 

Step – 2: (Optional: Only For GA Upload Feature)

  • In Analytics, Go To Data Import > Create Cost Data Import > Make sure to choose Overwrite data option.

 

  • Click on Get Custom Data Source ID & copy-paste the ID in the above script as DATASET_ID value.
Custom_Data_Source_ID - Digishuffle
  • Click on Get schema & paste it in the above spreadsheet we created.

Facebook_Cost_Data_Upload_Schema_in_Spreadsheet - Digishuffle

 

IMPORTANT #1: The Ordering of the Schema Headers should match the FB_FIELDS in step 3.
IMPORTANT #2: Append ga:date, ga:source, ga:medium as the last columns. (without changing order)

Cost_Data_Upload_Spreadsheet_schema - Digishuffle

Congratulations..!! You have successfully completed the setup.

Now, lets try running the script & then automating it.

Facebook Cost Data Import in Google Analytics: Automation

Now let’s look at the automation part:

Step – 1:

  • For automation to work, you’ll need to manually fill in all the variables.
  • Go To Ads Manager > Copy & Paste FB ad account ID as ACCOUNT_ID.
Facebook Account ID - Digishuffle
  • Paste the values in below variables.
Facebook_App_ID_-_Facebook_Cost_Data_Upload - Digishuffle

Step – 2

  • You can enter the Facebook ad fields to extract via API.
  • Enter the fields separated by commas, You can find the whole list here.
  • You can even enter the breakdowns as per this doc. (it is optional)
  • You can enter the spreadsheetId, sheetindex (the leftmost sheet has 0 index), & pos (cell position) where the data would be extracted.

Step – 3

  • The date range is for the facebook ad insights data extracted via API.
  • You can enter the DATE_RANGE as ‘yesterday’ , ‘today’, ‘this_month’, etc.
    Check the full list here. (date_preset variable)
  • For custom date range, enter the values in start_date & end_date.
  • If you enter both, DATE_RANGE will take the precedence.
  • The output data will be broken down daywise. It will adopt the same format compatible for cost data upload in google analytics.
Date_-_Facebook_Data_Upload_Analytics
  • Enter DATE_RANGE or Custom date range
  • Enter the UTM values used in Facebook Ad URLs.
  • Lastly, set “isGaUpload” to true & “useHeaders” to false.

 

Running & Automation Process

Running the Script:

  • Run the onOpen function in the project.
  • You’ll get the drop down named ‘Cost Data’ in the spreadsheet.
  • Go To Cost Data > Facebook > Authorize. Click on Authorize.  Or Click On Open Sidebar > Click ‘Authorize Facebook’.
  • Complete the Authorization process & close the tab after the successful authorization.
  • Click on ‘Facebook Data Export’. You’ll get the facebook data in spreadsheet.
  • You can then click ‘Upload Data To GA’, which will upload the data in Google Analytics.
Facebook-Cost-Data-Import - Running the Script - Digishuffle
Authorizing_Free_Facebook_Reporting_Tool

OR

  • You can use the SideBar UI to operate the settings. Currently, you can select
    • Ad Accounts
    • Date Range
    • Fields
    • Breakdowns
  • Other settings will be imported from the variables defined manually.

Automating the Script:

  • For Automation, Go To Edit > Current Project’s Trigger.
  • You can create trigger’s for 2 functions namely, ‘facebookData’ & ‘uploadDataToGa’ & trigger them consecutively. (export the data then upload the data.)
  • You can use DATE_RANGE = ‘yesterday’ , so the script will upload the facebook data daily.

 

Important Points:

  • Make sure to keep an eye on the Quotas for facebook & google.
  • You can log out & re-authorize if you face any problem (Reports> Facebook > Log Out) & Re-run authorization.
  • Make sure the ordering of FB_FIELDS is the same as the schema headers in the spreadsheet. The last 3 columns (date, source, medium) will automatically be appended to FB_FIELDS. So keep them untouched.
  • You can automate the ‘Facebook Export’ & ‘Upload Data To Ga’ functions daily with the date range = ‘yesterday’.
  • You can also upload the data from other sources & just run ‘Upload Data To Ga’.

 

Lastly, do comment if you successfully implement it or face any problems & subscribe for code updates.

Errors:

If you encounter the following errors, follow the respective solutions.

#1: Authorization is required to perform that action

  • Click On View > Show Manifest File.
  • Paste the below code in appscript.json file.
  • Save the file > run onOpen > Authorize

“oauthScopes”: [“https://www.googleapis.com/auth/analytics”,
“https://www.googleapis.com/auth/analytics.readonly”,
“https://www.googleapis.com/auth/script.container.ui”,
“https://www.googleapis.com/auth/script.external_request”,
“https://www.googleapis.com/auth/script.send_mail”,
“https://www.googleapis.com/auth/spreadsheets”]

Facebook_To_GA_Script_-_Error

 

#2: (#803) Some of the aliases you requested do not exist: act_

  • Enter the account_ID variable. Check automation > step – 1.

#3: (#100) Current combination of data breakdown columns (action_type, xxxxxxxxxx) is invalid

  • Not a valid combination of breakdown. Re-check the breakdown fields. Check the FB breakdown docs.

#4: Unknown Error

  • Large data rows requested. You can try to import data in chunks by reducing date range or removing granular parameters.

#5:  SignIn with Google temporary disabled for this app

 

Updates:

Updates[12/02/2020]:

  • v5.0 Marketing API Upgrade
  • DigiFb Library v1.3


Updates[15/11/2019]:

  • v5.0 Marketing API Upgrade
  • Added Append rows, clear sheet, use headers, callback function returning FB data added.
  • DigiFb Library v1.2

Updates[02/09/2019]:

  • v4.0 Marketing API Upgrade
  • Page Post Insights: Likes, Shares & Comment
  • DigiFb Library v1.1

Updates[21/05/2019]:

  • v3.3 Marketing API Upgrade
  • Breakdown Feature
  • Data Import Alerts
  • UI Improvements & Bug Fixes

Updates[08/03/2019]:

  • SplitBy Feature
  • oAuth
  • Parser Update

Updates[17/12/2018]:

  • v3.2 Upgrade
  • Added Predefined Date Range UI
  • Bug Fixes.

Updates[23/08/2018]:

  • v3.1 Upgrade
  • Full Facebook Ad Export Functionality
  • Added Level to UI.

Updates[23/04/2018]:

  • Added SideBar UI
  • Bug Fixes.

Updates[26/01/2018]:

  • Currency Multiplier Added For Currency Conversions.
  • Emailers Added For Upload Automation.

Updates[18/11/2017]:

  • v2.11 & Error Display.

Updates [10-10-2017]

  • UTM Feature Added. If you have added any of utm_source, utm_medium, utm_campaign & utm_content parameter in Facebook Tracking template, it will override the source, medium, campaign_name & ad_name fields respectively.
  • Recommended fields: ad_name, campaign_name

DigiXport – Facebook  Ads/Page, G.Ads/Analytics & Bing Reporting Tool

If you run an agency or handle 100+ clients check out this reporting tool. Some Paid Features Include

  • Multiple Accounts Export
  • Connectors: Facebook Ads/Page, Bing Ads & Google Ads/Analytics
  • Save & Export Multiple Queries.
  • Export in Multiple Sheets Or Spreadsheets.
  • UTM Export & Currency Converter
  • Unlimited Rows Export
  • Format Data Using Formulas
  • & more..

Socal Ads To GA



Ritwik is a product owner at DigiXport. He also loves to write technical & easy to understand blogs for Marketers & Entrepreneurs. A Firm Believer in Teaching -> Learning -> Growing. :)

Comments (222)

    1. Hi Mahadev,

      Thanks for the comment..!!! I actually had missed one important step. I have updated it as step – 5.
      Try it & let me know.

      Thanks Again,
      Ritwik

      Ritwik B July 24, 2017 at 5:55 pm Reply

        1. Hi Mahadev,

          I ran through the whole process & found that facebook v2.9 has been deprecated. I have updated the script to v2.10. That works!!

          You already have completed the app creation process.

          Now just copy paste the new script above > Fill in the App ID, Secret & account ID. Authorize & Try exporting the data.

          Thanks

          Ritwik B July 25, 2017 at 8:33 pm Reply

          1. Hi, I am using the V2.10 script but i am still getting an error:
            TypeError: Cannot read property “length” from undefined, would you be able to help?

            Mag August 1, 2017 at 10:14 am

          2. Hey Mag,

            Make sure to use latest v2.10 fields.(check link above). For Eg: ‘website_clicks’ is now replaced by ‘clicks’, etc
            Also check the date range.

            Thanks

            Ritwik B August 1, 2017 at 3:45 pm

          3. Hi Ritwik,

            yes you are right it was my date range. Thank you for this script, it’s awesome! 🙂

            Mag August 2, 2017 at 11:24 am

          4. Hi Mag,

            Thanks a lot for pointing it out. Script has been Updated !!

            Now there is new variable ‘limit’ set to 100 datapoints/request. (you can increase it to 400-500, as per your needs)

            Now it should fetch all campaign data.

            Thanks

            Ritwik B August 11, 2017 at 8:51 pm

    1. Hi Petr,

      You have to authorize before you can access facebook data. Click on Cost Data > Authorize > Complete the process.

      Only then you can use ‘Cost Data > Facebook Data Export’.

      NOTE: First, copy the New Facebook Cost Data Upload Script above. I have updated the script to v2.10.

      Ritwik B July 25, 2017 at 8:28 pm Reply

  3. Hi Ritwik, I have tried your script and it works magic. Thanks!

    I just have 2 questions regarding the source and display variables.
    1. Is there any way to return few display variables? For example i want to see all ‘referral’, ‘social’ and ‘display’
    2. Similarly with source, I want to see all sources from every facebook campaigns that are running, or alternatively, just the one that contains “facebook_display” for example. Right now it is only returning the exact match, I tried putting in blank value and it doesn’t really work

    Sorry if it’s a very basic javascript question. Don’t have much experience with it. And thanks beforehand!

    Ceri Mon July 28, 2017 at 11:29 am Reply

    1. Hi Ceri,

      I guess you meant if we can apply filters to source & medium variables. Right now, we cannot.

      But I’ll update this script soon with Campaign Filter.

      So, if you are using different source, medium for campaigns in Facebook, you can use export specific campaigns having same source/medium.

      Thanks for the suggestion though.!!

      Ritwik B July 30, 2017 at 1:59 am Reply

      1. Sounds good! Looking forward to it. Thanks again 🙂

        Ceri Mon July 31, 2017 at 5:57 am Reply

      2. Hi again, Ritwik! I have tried your facebook data export add-on. Unfortunately it doesn’t return the URL parameters. When I choose the field ‘Link URL Asset’, it is returning ‘NOT_FOUND’. My campaigns and ads naming aren’t set up the same with the URL tracking parameters. Also I’m not sure why, but I have authorized my Bing account, but I can’t see my account on the list.

        Thanks again for building this awesome tool. Looking forward to hear your reply.

        Ceri Mon February 19, 2018 at 10:37 am Reply

        1. Thanks Ceri.!!!

          The link url field is not yet available to export & also the fields with ‘asset’ would be removed soon. But if you are looking to use URL params, check the GA Upload feature & add the source medium. fields.

          The exported data would use data from the Facebook’s URL Tags field.

          For bing, make sure you have linked accounts to the email id you are using to access.

          Thanks,
          Ritwik.

          Ritwik B February 19, 2018 at 5:35 pm Reply

          1. Ah I see. That’s too bad because we have set up various utm_sources and not just one that fits all our ads. I can use the new tool for simple weekly reporting, but having the Link URL will enable more in-depth analysis. And having other url params would be even more helpful.

            Regarding Bing, I think I had done that. Now when I try to click on “Authorize Bing”, it opens a new tab that immediately closes. I see my facebook accounts but not bing.

            I have also signed up for your mailing list. Cant wait to hear more updates for this nifty tool 🙂

            Ceri Mon February 20, 2018 at 4:47 am

  4. Hey Ritwik.

    Awesome guide, huge thanks! Surprised the post gained that few comments so far.

    Dmitri Ilin August 16, 2017 at 9:24 pm Reply

    1. Hey @dmitri_ilin:disqus,

      Thanks a lot.!!. Learned a lot from your articles too. keep ’em coming.!!!

      Ritwik B August 19, 2017 at 2:46 pm Reply

    1. Hi,

      I have the same one.

      Petr David Gyüre August 18, 2017 at 2:16 pm Reply

    2. Hey Allissa & Petr,

      I guess there might be no facebook data for the date range selected. Kindly check step – 4 to change the date range.

      Also, i have updated the script so it will now show error as ‘No Facebook Data For The Applied Date Range’.

      Thanks

      Ritwik B August 19, 2017 at 2:35 pm Reply

      1. Thank you mylord.

        I have a one more, and i hope last, question. Is any possible to make exchange of currency? Fb is in Euros, but i have GA in CZK, is there any way to deal with it? Thank you one more time and I am realy sorry for those troubles.

        Petr David Gyüre August 21, 2017 at 5:41 pm Reply

        1. interested to know about the exchange of currency also

          Matthew Miao November 14, 2017 at 7:01 am Reply

      2. https://uploads.disquscdn.com/images/5e41b60b3c46e6af47df49eb8ecf72f5de8e06235dd7f6a7c7f2efdd0a56d8d9.png Hey @ritwikb:disqus ,

        I’m getting this same error… “TypeError: Cannot read property “next” from undefined”

        Since you indicate the problem lies with Step 4, I’ve double-checked it. I’m using “lifetime” as the DATE_RANGE.

        I’ve tried a variety of things, resetting the token and reauthorizing with each change but can’t seem to crack this.

        Any ideas?

        I’ve attached a screenshot of my DATE_RANGE variables.

        Thank you.

        Wafflecone November 11, 2017 at 7:10 pm Reply

        1. I’ve triple check everything, I still have this exact mistake.
          But the upload to GA seems to works! (when I manually added data)

          Jean Lecompte November 15, 2017 at 7:19 pm Reply

    3. Hey Allissa / Anyone,

      Did you find the solution for this? I’m running into this same TypeError.

      Mike Woodland November 10, 2017 at 4:44 am Reply

    4. Hi,

      I have discovered the issue. Please see line 3 re: deprecated API version – Ritwik can you please amend the code to pull through the latest API?

      {
      “error”: {
      “message”: “(#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v2.11.”,
      “type”: “OAuthException”,
      “code”: 2635,
      “fbtrace_id”: “BWjclCpazKr”
      }
      }

      Cheers,
      John

      John Stefanidis November 17, 2017 at 4:22 am Reply

  6. Thank for this, great work. Works great to get the data out of Facebook. I only have one issue with ‘Upload to GA’.
    I assume I have the wrong details in the Account_ID or Property_ID, as if I save as csv and manually import at Google, it works.
    The error I get is ‘ReferenceError: “Analytics” is not defined.’
    I have the Account_ID as 8 numbers, The Property ID as XX-NNNNNNNN-1. I also tried the other 9 digit number from the URL after ‘p’ for the Property ID. Unsure what else it may be. The Dataset_ID I’m sure is correct, as that is described in your guide.

    Timothy Pointer August 18, 2017 at 3:18 pm Reply

    1. Hey Timothy, Thanks you, as I had missed the Analytics API step.I have updated that in step – 1. Kindly let me know.

      The Account & Property IDs are available in Analytics Settings & Property Settings respectively.
      (Eg: Account ID : xxxxxxxx Property ID : UA-xxxxxxxx-n)

      I guess you had correctly put the no. but I missed the analytics api step. Most probably, that was the issue for the error ( ‘ReferenceError: “Analytics” is not defined.’ )

      Thanks

      Ritwik B August 19, 2017 at 2:32 pm Reply

      1. Yes, I am running great now. If others follow your guide, with the edit, should have no issues. Thanks saved me figuring it out from scratch, much appreciated.

        Timothy Pointer August 19, 2017 at 2:50 pm Reply

  7. Great, thanks for sharing! I had an syntax error on line 147. It looks like a code block got duplicated that needed to be deleted. But managed to get it working with help from a dev friend.

    If you write a similar post to automate cost data from Bing Ads into Google Analytics, that would be awesome!

    Rebecca August 29, 2017 at 2:48 pm Reply

    1. Thanks Rebecca..!!! My bad. :/ . I have removed the duplicated code. Now it should be fine.

      Yes Bing Ads is in the pipeline.

      Ritwik B August 29, 2017 at 6:42 pm Reply

  8. Thanks for this guide, it’s a great guide ! I have a problem though when I run the function : no drop down menu appears on the active spreadsheet. Any advice or help would be super useful 🙂

    Dosso Lucas August 31, 2017 at 5:26 pm Reply

  9. Hi Ritwik, this is an awesome tool, thanks for putting this together!

    Would you have any suggestions how to extract the utm_campaign and utm_content used in the Facebook ads and use these values as ga:campaign and ga:adContent? These parameters can be added under the section ‘URL Parameters’ for each ad.
    https://uploads.disquscdn.com/images/aebb547e78a7df321888693c0939d9e0fb340f3f64dcb029bcdfcf6072a126fb.jpg

    This would make reporting in Google Analytics a bit more clear and this way the media delivery numbers could be matched to website activity numbers.

    Jaak Saarepuu September 26, 2017 at 6:20 pm Reply

    1. Hi @Jaak,
      Yes I completely agree with your point. (and most of them suggested the same). Will be updating the script till next week, to include the source, medium, campaign, adcontent values from FB URL (utm) parameters.

      Actually I had exported the data at ‘adset’ level but now i’ll export it at ‘ad’ level so i can get the URL parameters through API. 🙂

      Thanks,

      Ritwik B September 29, 2017 at 2:37 pm Reply

      1. Can you update the scripting to include source, medium, campaign, adcontent, and adterm, I always tag with 5 parmater, the more, the better, i can have details campaign performance right in the GA report.

        I tried to add ad_name in the FB Field, and also add ad_name in the spreadsheet.

        But when i export data from facebook, there is nothing there, and still the same data.

        let me know if i did anything wrong, or i need to wait for the script updates?

        Luiza Franceschini October 5, 2017 at 8:06 am Reply

        1. Script Updated Now. Let me know 🙂 .

          Ritwik B October 11, 2017 at 2:08 am Reply

    1. Hey, would you able to fix that bug?

      Nickolas Lago October 10, 2017 at 6:41 pm Reply

    2. You need to use facebook login plugin now. Check updated step – 5. Thanks

      Ritwik B October 11, 2017 at 5:09 am Reply

      1. thanks! that worked. but still getting this error:

        Cannot call SpreadsheetApp.getUi() from this context. (line 266, file “Code”)

        when uploading data to GA. sorry for bothering, but I appreciate all your efforts!

        蘭婷馬 October 17, 2017 at 4:46 pm Reply

  11. Hey Ritwik! Great tutorial, man! That is so important for us!
    I had the same problem twice… I thought that had anything to do with my facebook page access, but it doesn’t seems to be the problem.

    I did all those steps, but when I try to authorize the app on facebook, after I had run the code on scripts, facebook shows me a message saying that the domain it is not included in the app’s domains. Perhaps, I had added script.google.com to facebook ads.

    Do you know what could be happening?

    Thanks man, congrats for the blog 🙂
    https://uploads.disquscdn.com/images/cd1b7638ba72e68a14083aeccab31a0790fecc6968ae221d2bba017201a4f47e.png

    Nickolas Lago October 9, 2017 at 6:11 pm Reply

    2. Hi Nickolas,
      Its Facebook Update :/ . It now needs its Login plugin to do the oAuth job. Check Step – 5. Its updated.
      Thanks for pointing it out. 🙂

      Ritwik B October 11, 2017 at 5:08 am Reply

  12. Hi Ritwik, thanks for your script! For some reason I can’t get it going. I get the “REPORTS SUCCESS…!!!” message, but there is no data in the spreadsheet. I tried changing date range and FB_FIELDS – no result. Any thoughts will be appreciated. Thank you!

    Alex Khomyakov October 12, 2017 at 7:04 pm Reply

  13. Hi Ritwik,
    i have a problem “cannot read property next from undefined”. I saw in comments it might be a problem with fb data, but i think there is everything ok. I have set DATE_RANGE – this_month and i have a data in FB account. Could you help me please? Thank you Tom

    Tomáš Straděj October 24, 2017 at 3:43 pm Reply

  14. Hi Ritwik,

    Thanks a lot for this amazing script.

    Juste have one final problem that I can manage to solve. Each time I run the function “Import Data to GA”, I get this error message “ReferenceError: “Analytics” is not defined”.

    Can’t find when this comes from. May be you have the answer 🙂

    Thanks a lot for your help !

    Thomas

    Thomas Mouflard November 8, 2017 at 8:13 pm Reply

    1. Hi Thomas,

      I guess you missed last part of Step – 1. (Check GIF)
      Enable Analytics API from console.

      Thanks 🙂

      Ritwik B November 10, 2017 at 1:07 pm Reply

  15. Hi Ritwik,

    Thanks for the extensive guide! I do however, end up with a “TypeError: Cannot read property “next” from undefined.” error. As I read in the comments, there were some people with similar issues. However, whatever I do (using the 2.10 version, triple-checking the date ranges), the error stays.

    Any idea where this error’s coming from?

    Thanks in advance!

    Jesper Uittenbogaard November 16, 2017 at 6:33 pm Reply

  16. Hi there – is there a way to combine the Facebook campaign spend data for one day into one cell? right now I’m getting many cells for the same day and for the same campaign, which limits how far back I can go due to the limit on the number of cells i can export.

    Jenna Stauffer November 21, 2017 at 8:03 pm Reply

    1. Hi Jenna,

      The data is exported is at ad level & daywise by default. Once you upload it in GA you can apply filters there. Also, you can increase the limit. 🙂

      Thanks,
      Ritwik

      Ritwik B December 5, 2017 at 1:22 am Reply

  17. I followed the script, if i export and updated the cost manually, it works fine.

    If i set up the trigger to updated automatically, keep getting below mistakes:

    facebookData Cannot call SpreadsheetApp.getUi() from this context. (line 143, file “Code”) time-based 12/5/17 2:30 AM

    And then

    uploadDataToGa Cannot call SpreadsheetApp.getUi() from this context. (line 328, file “Code”) time-based 12/5/17 3:27 AM

    Anyone have the same problem?

    Matthew

    Matthew Miao December 5, 2017 at 11:41 am Reply

    1. If you are implementing manually. Ignore the error when automated where you get “Spreadsheet.getUi()” errors.

      It is because when you are offline, it cannot show the UI messages like ‘Report success’ or ‘Upload Successful’ as in spreadsheet.

      But the exporting & uploading will be done. Let me know if that works?

      Ritwik B December 6, 2017 at 12:38 am Reply

      1. Hi Ritwik:

        Thank you for your reply.

        Let me explain it as below:
        1) I follow the script. and it works great, if i manually export Facebook Ads Cost data, and then manually upload to GA. All works Fine.

        2) Then I go to the script editor, and edit the project trigger to automate the process, you can see the screenshot below:
        https://uploads.disquscdn.com/images/3685126b988aa28f11874248a8837dcfc04efb393264a35ae320c03081c62462.png

        Then the automation is not happening.

        I received below error message about script.
        https://uploads.disquscdn.com/images/f8985d10cb865cbea436204c4498cc5396d7d6684861e4097aa4143c5d0dbbd9.png

        3) So when i go to the script editor, and run the facebookData function, it takes very long time to running.

        Maybe it is not clear. I can record the video if you want.

        Thanks for all the good article on your blog.

        I have learn huge from you.

        Matthew

        Matthew Miao December 6, 2017 at 7:36 pm Reply

        1. Fixed !! Recheck & let me know.

          Ritwik B December 11, 2017 at 6:58 pm Reply

    1. Thanks Matthew. Its a small bug & it will be fixed soon. To explain it, when you export the facebook data the old data is only cleared from column A to H. (Now this limit will be increased)

      Ritwik B December 6, 2017 at 12:34 am Reply

      1. Great! I will come back to follow up and updated the new version

        Matthew Miao December 6, 2017 at 7:21 pm Reply

      2. So, Ritwik, Have the bug been fixed?

        Matthew Miao December 10, 2017 at 7:04 pm Reply

        1. Sorry Matthew, I have been stuck on 2 things badly.
          1.) Google Add-On Review Process.
          2.) Exceeding Facebook User Limit. New user cannot export facebook data now.

          Both are taking a hell lot of time.

          Meanwhile, try manual method. The email error you got have been fixed. Recheck & let me know.

          Ritwik B December 11, 2017 at 6:57 pm Reply

          1. Hey Matthew,
            Add-on has been reviewed & Bugs are fixed. You can now try exporting through Addon.
            Let me know if you encounter anything else.

            Ritwik B December 11, 2017 at 10:57 pm

  19. In the addon, in order to automate the process, in the last step, where to find the query id?

    Matthew Miao December 5, 2017 at 12:17 pm Reply

    1. Check the box – Save Settings while exporting facebook data. You’ll then get Query ID

      Ritwik B December 6, 2017 at 12:23 am Reply

      1. Thanks, I got it figured out.

        Matthew Miao December 6, 2017 at 7:21 pm Reply

  20. I think i got it figure it out.

    But there is still a potential bug, Let me refrase this way, say i automate the process to update the cost daily, I have 8 entries cost yesterday, and tomorrow, i have less than 8 entries to upload to GA.

    But there will be extra lines with only ga:medium have content.

    All the other column don’t have data.

    So this will create error in the upload process.

    Please have a look, thanks

    Matthew Miao December 5, 2017 at 12:51 pm Reply

    1. Its a bug & will be fixed in a soon. Thanks for pointing it Matthew. Only columns A To H are refreshed while exporting. (But now it will be fixed)

      Ritwik B December 6, 2017 at 12:44 am Reply

      1. Its Fixed..!! Thanks

        Ritwik B December 11, 2017 at 10:57 pm Reply

    1. Its Fixed.!!

      Ritwik B December 11, 2017 at 10:58 pm Reply

      1. HI, ritwik:

        I have tried last week, the same problem still happening…….

        Can you double check if it is fixed?

        Thanks

        Matthew

        Neo Wireless January 21, 2018 at 9:33 am Reply

  22. Hi Ritwik,

    Thanks so much for putting all this together, I would have never been able to make it with your instructions!

    One question: I need to export FB ads data to spreadsheet and the amount of data exceeds the limit.

    Thought about two solutions but don’t know how to implement them:

    1. How to exclude the columns “source” and “medium”?
    2. Would possible to convert the metric from daily (date) to weekly (calendar week)?

    Shared your tutorial on my social media!

    Federico Cristoforoni December 19, 2017 at 3:17 pm Reply

    1. Thanks a lot Federico..!! Try increasing the var limit to 500.

      Well I planned the code to export the facebook in such a way as to be compatible with the GA upload. (Maybe I’ll add just the facebook data export soon)

      For now I guess only Supermetrics Add-on can help. 🙂

      Ritwik B December 25, 2017 at 1:04 am Reply

      1. Hey @federicocristoforoni:disqus,

        We have built full facebook ads export functionality now. Try it & let me know.

        Thanks,
        Ritwik

        Ritwik B January 18, 2018 at 1:55 am Reply

  23. Hi, Ritwik:

    I am testing your new update script again.

    In the setting, the Google analytics data, the property id is UA-11233434-2, right?

    When i put account id, property id, and dataset_id, it is showing error of insufficient permissions(line 346, file”code”)

    I think this have something to do with my GA account settings, but i cant find any error. I will put the debug information below:

    https://uploads.disquscdn.com/images/f2fbd49b6b896afe63e85f87129c2384f115044dfaa3b0274ff6b4091cd9b207.png

    Matthew Miao January 17, 2018 at 11:45 am Reply

    1. “Analytics’ is not defined refers to Analytics API not enabled in console. Check Step – 1 (last part).

      Thanks,
      Ritwik

      Ritwik B January 18, 2018 at 1:52 am Reply

      1. Thanks Ritwik for the quick reply.

        You are right. I forgot to enable Analytics API. Now it is all working fine.

        Last time, I am having problem with automatically upload, I will test again this time. Will give you feedback how it works.

        Thanks for the awesome work.

        Matthew

        Neo Wireless January 18, 2018 at 4:25 am Reply

  25. Hi Ritwik:

    Also i am trying the spreedsheet add on, I can’t open the side bar, it is showing

    “TypeError: Cannot read property “length” from undefined.

    So i can’t use this add on to export or upload data.

    Thanks

    Matthew

    Matthew Miao January 17, 2018 at 12:15 pm Reply

    1. Try re-installing the add-on. Let me know if that works

      Thanks,
      Ritwik

      Ritwik B January 18, 2018 at 1:53 am Reply

      1. Hi, Ritwik:

        I tried re-install the addon multiplue times. Still the same error message.

        Matthew

        Neo Wireless January 18, 2018 at 4:32 am Reply

        1. Hey,
          Thanks for the video.That really helped!! I’ll check it & let you know asap.

          Ritwik B January 19, 2018 at 1:35 pm Reply

          1. Thanks.

            Meanwhile, the bug i reported to your before still exisit.

            It happen when i schedule the automaticall upload.

            Same problem.

            If yesterday have for example 5 lines entires.

            and the next update have 4 lines entries.

            It will not upload to GA successfully, because there is one entry empty, so cause error.

            Can you double check that? If you need any more information, I can shoot you a video, if it helps.

            I really want to make this work..

            Neo Wireless January 21, 2018 at 9:32 am

          2. Manual Script is updated. Check & Let me know

            Ritwik B January 21, 2018 at 9:19 pm

          4. Hey Matthew,
            Try selecting date range where data is available. In the video it shows no data for the date range. (so the sheet will not be refreshed). Let me know if that works.

            Thanks,
            Ritwik

            Ritwik B January 24, 2018 at 12:16 pm

          5. Yeah, you are right.

            There is no data Available for that range, i tried the other range, it did works.

            Thanks for your reply.

            Matthew

            Neo Wireless January 25, 2018 at 3:03 am

  27. The post we are refering to is
    [https://www.digishuffle.com/blogs/cost-data-import-facebook-to-google-analytics/](https://www.digishuffle.com/blogs/cost-data-import-facebook-to-google-analytics/)

    Hi Ritwik:

    You know that there is many paid services to export Facebook Ads Cost data and then build dashboard over the exported data. They have much monthly chargers.

    I want to use your script to free export Facebook ads cost and build google data studio dashboard on those data. But when i try to add more filed that is support in 2.10 Facebook Ads Insights API. Some of the field can pull out the data, Some of the field return error message, can’t pull anything out of Facebook.

    The details information is below, Can you have a look?

    Thanks

    What i am trying to do here is to:

    1) automate export all facebook ads cost data

    2) Build easy to understand dashboard with google data studio.

    After putting all the available field from [Facebook Ads Insight](https://developers.facebook.com/docs/marketing-api/insights/fields/v2.10) , I sort out all the working field and not working field as below:

    **Working Metrices is below**

    – account_currency
    – account_id
    – call_to_action_clicks
    – canvas_avg_view_percent
    – cpc
    – cpm
    – frequency
    – reach
    – ctr
    – cpp
    – canvas_avg_view_time
    – social_clicks
    – social_impressions
    – social_reach
    – social_spend
    – spend
    – total_action_value
    – total_actions
    – total_unique_actions
    – unique_clicks
    – unique_ctr
    – unique_inline_link_click_ctr
    – unique_inline_link_clicks
    – unique_link_clicks_ctr
    – nique_social_clicks

    **Not working Metrics is below**

    – action_values
    – actions
    – buying_type
    – outbound_clicks_ctr
    – relevance_score
    – unique_actions
    – unique_outbound_clicks
    – unique_outbound_clicks_ctr
    – unique_outbound_clicks_ctr
    – video_10_sec_watched_actions
    – video_30_sec_watched_actions
    – video_avg_percent_watched_actions
    – video_avg_time_watched_actions
    – video_avg_time_watched_actions
    – video_p100_watched_actions
    – video_p25_watched_actions
    – video_p50_watched_actions
    – video_p75_watched_actions
    – video_p95_watched_actions
    – website_ctr
    –

    If the field is not working, it will always showing the same error message below:

    **TypeError: Cannot find function replace in object [object Object]. (line 262, file “Code”)**

    The error code is below:

    rw[i].push(Data.data[i][key].replace(/,|’|”/g,”))

    Is it some easy to fix?

    If you can debug the error message for me, that would be awesome, Thanks

    Matthew

    Neo Wireless January 25, 2018 at 10:13 am Reply

    1. Hey Matthew,

      Can you please try The Add-On again. We have Fixed the Issue..!!

      Let me know

      Thanks,
      Ritwik

      Ritwik B January 28, 2018 at 2:35 am Reply

      1. Hi Ritwik:

        Now the error message is gone. It’s working again.

        Thanks for such an awesome tools. So we can also export bings data now?

        Thanks

        Matthew

        Neo Wireless January 28, 2018 at 4:02 pm Reply

        1. Yes. Kindly Try & Let Me Know.

          Ritwik B January 29, 2018 at 12:31 pm Reply

  28. Hi RITWIK,

    I just added the add on to google analytics and can’t find all the accounts I’ve a lot and want a specific account to import the data from it.

    Thank you in advance and great work 🙂

    mahmoud February 4, 2018 at 4:10 am Reply

    1. Hey Mahmoud, Thanks. I’ll check & fix it.

      Ritwik B February 11, 2018 at 4:18 pm Reply

  29. So, Ritwik:

    To use the spreedsheet addon, I can only automate one facebook & GA account?

    I have 3 facebook ads account & 3 GA account accordingly.

    I am finding hard time to set the automatically update daily. It’s keep uisng the same dataset id, and query id?

    Thanks

    Matthew

    Neo Wireless February 6, 2018 at 7:26 am Reply

    1. Hey Matthew, Thanks. I’ll check this.

      Ritwik B February 11, 2018 at 4:17 pm Reply

  30. Hi, I have been using your scripts and it has been work perfectly fine.
    But this week I got error message ” (#2) Service temporarily unavailable “.
    What should I do?
    Thanks 🙂

    Bongkoch Chaimuangma February 6, 2018 at 8:37 am Reply

    1. I face the same issue 🙂 would be happy about a solution for that!

      Niklas Meißner February 16, 2018 at 5:12 pm Reply

  31. In Step – 6 , “In Analytics” , which tool are you referring to ?

    Sahil Gumber February 10, 2018 at 9:46 pm Reply

    1. Hey Sahil, Its Google Analytics.

      Thanks

      Ritwik B February 11, 2018 at 4:16 pm Reply

  32. Thanks for the Script.
    What do I have to change, if I have the UTM Parameters added in the URL filed itself and don’t use the parameter box in Facebook?
    I know it’s not best practice, but that’s how the setup is at the moment 🙁
    I guess somehow I have to change line 147 “url_tags” to something else? But how?

    Benedikt Kienzler February 11, 2018 at 9:50 pm Reply

    1. Hi Benedikt,

      I’ll have to check. But meanwhile try replacing “url_tags” with “object_url” across ( in URL fetch & AdsId functions)
      Let me know how it did.

      Thanks,
      Ritwik

      Ritwik B February 19, 2018 at 4:57 pm Reply

      1. Thanks for the answer!
        But it doesn’t seem to work (for me at least).

        Benedikt Kienzler March 7, 2018 at 2:51 pm Reply

  33. Hi Ritwik, thanks for this great tool. I tried for a long time to create a better Reporting for Facebook Ads than the Power Editor. I went through all the steps and could successfully set up everything. However, when comparing the numbers between my Spreadsheet Export and Power Editor, there are discrepancies between the values for CTR, CPC, and Reach, whereas Impressions are always on point. Do you have any explanation for this? Might it be a matter of aggregation? I would really like to use the Report I set up, but I need to be able to rely on it, or at least understand why the numbers differ. Thanks in advance.

    Doro Luik February 14, 2018 at 11:21 pm Reply

  35. Hi Ritwik,
    thank you for the helpful addon.

    I have a problem with the authorization of my Bing Account:
    After
    logging into my Bing Account i will be redirected to a
    script.google.com URL and then the tab closes by itself. Nothing else
    happens.
    Any idea how i get the Bing Account authorized?

    Ma_Gru February 22, 2018 at 6:11 pm Reply

    1. Sorry for the error. But Yes. We’re fixing it. 🙂
      Thanks,
      Ritwik

      Ritwik B February 22, 2018 at 9:24 pm Reply

      1. Hey, Bing Error Has Been Fixed..!!

        Ritwik B March 1, 2018 at 1:37 am Reply

        1. Hey Ritwik,

          thank you for fixing it. The import of Bing Data is now working.

          Thanks,
          Maren

          Ma_Gru March 7, 2018 at 7:36 pm Reply

  36. Hi Ritwik, thanks for the great tool.
    I’m having issues uploading the data to analytics. All the other steps went fine, i just don’t see the data in analytics as the upload file doesn’t look like the usual files i upload manually – it’s not in csv format and the lines don’t break as usual. Do you know what needs to be done in this case?
    Thanks!

    Daniel Mor February 26, 2018 at 8:43 pm Reply

    1. It might take time to reflect in analytics. Yes the upload format is different (“application/octet-stream”) but it should work.

      Thanks,
      Ritiwk

      Ritwik B March 14, 2018 at 1:52 am Reply

  37. Hi, did you come across our tool dataddo.com ?
    Great way how to automate all your analytics data handling routines. This service works with many data sources such as Google Analytics, Facebook, Adobe Analytics, Instagram, YouTube, LinkedIn, Twitter,.. and can be wired to your existing CRM and BI tools. You can work with multiple accounts, set up automatic actions in data extraction, transformation, and delivery.

    Zdeněk Pecha March 9, 2018 at 7:30 pm Reply

  38. Unfortunately once i try to click to Autorize and i login to facebook then once it returns to Google Sheet i get error
    Authorisation is required to perform that action

    ΔυναμικέςΙστοσελίδες Σε Λογικέ March 12, 2018 at 2:11 am Reply

      1. Hey,

        Try updating the appscript.json file with the above mentioned code in ‘Error’ section.

        Ritwik B March 20, 2018 at 2:17 am Reply

    1. Hey Oshri,

      All it works on short lived token currently. But a single call (given in the docs you mentioned) will convert it to long-lived token.

      GET –> /oauth/access_token?grant_type=fb_exchange_token&client_id={app-id}&client_secret={app-secret}&fb_exchange_token={short-lived-token}

      Will update with it later.

      Thanks,
      Ritwik

      Ritwik B March 14, 2018 at 1:58 am Reply

  40. Hi Ritwik,

    i keep getting this error during Facebook Auth:

    Authorization is required to perform that action.

    do you know how to solve this?

    Amir Vizel March 14, 2018 at 3:19 pm Reply

    1. Hi Amir, I get the same issue, were you able to figure it out?

      Shay zaidenberg March 16, 2018 at 1:34 am Reply

    2. Same here, any solution?

      Mike March 19, 2018 at 9:10 pm Reply

    3. Hey @amirvizel:disqus @disqus_GsrxL46YD0:disqus @shayzaidenberg:disqus : Try the method mentioned in Error section & let me know if that works.

      Ritwik B March 20, 2018 at 2:21 am Reply

      1. Hi @ritwikb:disqus, I tried the method in the Error section and it gave the following error message detail:

        Illegal unquoted character ((CTRL-CHAR, code 10)): has to be escaped using backslash to be included in name at [Source: { “timeZone”: “America/Denver”, “dependencies”: { “enabledAdvancedServices”: [{ “userSymbol”: “Analytics”, “serviceId”: “analytics”, “version”: “v3” }] }, “exceptionLogging”: “STACKDRIVER”, “oauthScopes”: [“https://www.googleapis.com/auth/analytics”, “https://www.googleapis.com/auth/analytics.readonly”, “https://www.googleapis.com/auth/script.container.ui”, “https://www.googleapis.com/auth/script.external_request”, “https://www.googleapis.com/auth/script.send_mail”, “https://www.googleapis.com/auth/spreadsheets”] }; line: 12, column: 62]

        Mike March 20, 2018 at 8:36 pm Reply

        1. Make sure there are double quotes (No single quotes) around the key/value pairs.
          “https://www.googleapis.com/auth/analytics”,
          “https://www.googleapis.com/auth/spreadsheets”, & so on… having single quotes will give that error.

          Ritwik B March 22, 2018 at 1:06 am Reply

          2. @Mike: You need to fix the quote signs in that copied code to “. At least it works for me.

            Quan Ngo March 22, 2018 at 8:21 pm

          3. @quan_ngo:disqus: I noticed that as well but still got the error…Maybe my issue is somewhere else then? Super weird. Thanks for the suggestion though!

            Mike March 22, 2018 at 8:24 pm

          5. @disqus_teg5bYXqzZ:disqus I haven’t figured it out yet. I’m guessing you’re having the same problem?

            Mike March 29, 2018 at 1:12 am

          6. Hi Ritwik, I managed to authorize the script but when I clicked Facebook Data Export, it shows “(#2) Service temporarily unavailable” and no data is shown in the sheet. Do you have any idea how to fix it?

            Quan Ngo March 22, 2018 at 8:23 pm

  41. very nice script – works pretty well. Congrats.

    Would be possible on manual script or Chrome extension to get data from more than 25 Facebook ad accounts?

    I need to select 10 accounts from a list of over 50, but on Chrome extension I see only 25.

    Would be possible to extend either manual script of chrome extension to more than 25 accounts?

    Dan March 14, 2018 at 3:50 pm Reply

    1. Hi Dan,

      Thanks for the message. Yes will extend the account limit in both

      Thanks,
      Ritwik

      Ritwik B March 20, 2018 at 2:20 am Reply

  42. Hi Ritwik,

    When I use the add on to export FB data, the first row with GA field names is always overwritten by the field names I choose from the side bar. Adjusting this needs some manual work so I cannot schedule a daily upload to fully automate the process. Do you have some idea how I can avoid the overwriting?

    Thanks,
    Karen

    Karen Wang March 17, 2018 at 3:12 am Reply

  43. Just 2 little questions.

    1. When run the Cost Data Import, and overwrite, does that replace all the data you have previously imported?
    2. Can you import “Website Clicks” instead of “Clicks” (All clicks)?

    I am currently importing all clicks, which doesn’t give me a Click Through Rate, or CPC, it give me a “Click On” rate or a “Click On” ad cost. So I’d like to import the website click as it’s a better metric for me, and if possible rewrite all the previous clicks and costgs.

    Thanks!

    nicksoper March 19, 2018 at 9:32 pm Reply

    1. Hey @nicksoper,

      .1.) Yes, If you have uploaded incorrect data, you can re-upload the new data using overwrite option & that’ll replace old data.
      2.) Yes, you can upload any type of click (outbound click, link click, unique click, etc) it’s up to you, Just make sure the header columns are correct while uploading (ga:adClicks)

      Source: https://support.google.com/analytics/answer/6064634?hl=en

      Thanks,
      Ritwik

      Ritwik B March 22, 2018 at 1:12 am Reply

      1. @@ritwikb:disqus @@nicksoper:disqus

        I have just tried this exact thing – I want to export outbound_clicks instead of clicks.

        in Code.gs I have replaced: var FB_FIELDS = ‘clicks,spend,impressions,adset_name,campaign_name’;

        with: var FB_FIELDS = ‘outbound_clicks,spend,impressions,adset_name,campaign_name’;

        Do i need to update anything else in Code.gs?

        I made this one change and ran the export script again and got this error: TypeError: Cannot find function replace in object [object Object].

        Any suggestions?

        Thanks
        Di

        Dianna May 31, 2018 at 10:12 am Reply

        1. I face the same issue. I want to export outbound_click as well but it didn’t work!!
          HELP!!

          Ikjoong Choi July 17, 2018 at 12:01 pm Reply

  44. Bad news. It appears the Cambridge Analytica news triggered a new “Cracking Down on Platform Abuse” policy which means all new Facebook Apps need to be reviewed, which requires a lengthy explanation of each step and seems like a roadblock.

    I’ve tried to use the Google Sheets DigiXport Addon, but I need to rearrange the headers because I’ve been using Supermetrics Uploader and I want the format to match.

    Have you got any ideas how I could automate the upload but add columns (limit is 7), and include static columns or just customize the columns a bit more.

    nicksoper March 22, 2018 at 8:43 pm Reply

  45. Hi Ritwik,

    I’m getting the following error when I go to Cost Data > Facebook > Authorize

    Can’t Load URL: The domain of this URL isn’t included in the app’s domains. To be able to load this URL, add all domains and subdomains of your app to the App Domains field in your app settings.

    Any ideas?

    Thanks in advance!

    Gabriel Ravarini March 22, 2018 at 9:41 pm Reply

    1. Check Step – 5. (Add Domains in the Facebook App Page)

      Ritwik B March 25, 2018 at 5:35 pm Reply

      1. I don’t know why but the “Valid OAuth Redirect URIs” field was empty.

        I completed it again, and the same error still appearing.

        What can I do?

        Gabriel Ravarini March 26, 2018 at 12:58 am Reply

    1. Fixed that error in the end, now I get “An unknown error occurred”

      David Franks March 28, 2018 at 3:55 am Reply

  47. Hi,

    Great tutorial! What should I change in the code if I don’t want to split the values per day? So if I want to see the total spend for each unique campaign and ad_set, but don’t want it to be split by every day. Doing this would allow me to query longer periods

    Tjadi March 28, 2018 at 7:43 pm Reply

    1. Hi Tjadi,

      In order to avoid splitting by day. In Line 115, Remove ‘time_increment’ : ‘1’ . So

      var param = { ‘level’ : ‘ad’,
      ‘fields’: ‘ad_id,’+FB_FIELDS,
      ‘time_increment’: ‘1’,
      ‘limit’ : limit
      }

      will become

      var param = { ‘level’ : ‘ad’,
      ‘fields’: ‘ad_id,’+FB_FIELDS,
      ‘limit’ : limit
      }

      Let me know if that works.

      Thanks,
      Ritwik

      Ritwik B April 5, 2018 at 10:35 pm Reply

      1. Got it to work, thanks!

        Tjadi April 6, 2018 at 3:08 pm Reply

  48. Try to copy & paste this one, if you are also getting the error concerning an unknown character:

    {
    “timeZone”: “Europe/Paris”,
    “dependencies”: {
    “enabledAdvancedServices”: [{
    “userSymbol”: “AnalyticsReporting”,
    “serviceId”: “analyticsreporting”,
    “version”: “v4”
    }, {
    “userSymbol”: “Sheets”,
    “serviceId”: “sheets”,
    “version”: “v4”
    }, {
    “userSymbol”: “Analytics”,
    “serviceId”: “analytics”,
    “version”: “v3”
    }]
    },
    “exceptionLogging”: “STACKDRIVER”,

    “oauthScopes”: [“https://www.googleapis.com/auth/analytics”,
    “https://www.googleapis.com/auth/analytics.readonly”,
    “https://www.googleapis.com/auth/script.container.ui”,
    “https://www.googleapis.com/auth/script.external_request”,
    “https://www.googleapis.com/auth/script.send_mail”,
    “https://www.googleapis.com/auth/spreadsheets”]
    }

    Leopold Stübgen March 28, 2018 at 8:14 pm Reply

    1. Try Authorizing. Facebook > Authorize > Click To Authorize.

      Ritwik B March 30, 2018 at 2:51 pm Reply

  50. Hi, thanks alot for the great script!!

    I have one issue, the date exported from Facebook formats as: “20180116” Which gives me an error if I export to Google Analytics. How can i change this automatically to the right format?

    Flip Vermeersch March 30, 2018 at 6:36 pm Reply

  51. I’ll be honest I am in way over my head here. I have been trying to import Facebook post data into Google sheets to analyse engagement on different types of posts I do ( Including paid promotions etc) this is how I stumbled across this, I wasn’t able to get it working I get an error “Can’t load URL: The domain of this URL isn’t included in the app’s domains. To be able to load this URL, add all domains and sub-domains of your app to the App Domains field in your app settings.” When I try and authorize from my sheet.

    Two questions, is what I’m trying to do possible and is this completely missing what I’m trying to do and please send help.

    Many thanks in advance.

    Dan Dakis April 5, 2018 at 6:24 pm Reply

    1. Hey Dan,

      Check Step – 5. (Include script.google.com in app domains…)

      If you are looking for Facebook Posts Data (Or Facebook Page Related) like Page like, Comments, Emoticons, Post Likes, etc then this script might not help. ( But Yes, i’ll plan that for my later post. 🙂 )

      This script will only export Facebook Ads Data like Campaign, Adset, Ad, Clicks, Impressions, Spend, Age, etc etc.

      Thanks,
      Ritwik

      Ritwik B April 5, 2018 at 11:16 pm Reply

      1. Thanks for getting back! I’ll have a play and see if I can’t get it working. Will keep my eye out on any new posts!

        Thanks,

        Dan.

        Dan Dakis April 6, 2018 at 3:51 am Reply

    1. Hey Vishal,

      Check Step – 5 , the last part…

      Hope that helps. 🙂

      Thanks,
      Ritwik

      Ritwik B April 9, 2018 at 12:35 pm Reply

      1. Hi Ritwik,

        Thank you so much for your help. I was able to successfully run the script 🙂

        Vishal Bhatia April 9, 2018 at 4:23 pm Reply

      2. Hi Ritwik,

        Can we not add multiple facebook ad account in the single script?

        Thanks.

        Vishal Bhatia April 10, 2018 at 2:41 pm Reply

  53. I am getting a get request error? Anyone else?

    Eli Rachovitsky-Duarte April 10, 2018 at 2:59 pm Reply

  54. Hi Ritwik,

    Thanks for creating this really informative post. Authorisation, retoken, & the uploading data functions work seamlessly. However, I am getting an unsupported get request error for the Facebook Data Export function. Any suggestions on where I might be going wrong? Is it something with the permissions for the Facebook app? I just copied your code & populated the variables with my information as directed.

    Please let me know if you have any thoughts.
    Thank you,

    Eli

    Eli Rachovitsky-Duarte April 10, 2018 at 5:56 pm Reply

    1. Hello, I am experiencing a similar problem, have you managed to solve it, please?

      Salim April 17, 2018 at 6:07 pm Reply

  55. Hello Ritwik, excellent tool, thanks a lot for making it so easy to set up, im quite a newbie but managed to get it straight off the ground in one go.

    However, I am facing some issues – as i said downloading data from FB works great, uploading to GA also seems to work based on the API as no errors are shown, BUT:
    – if I upload the raw FB data that has multiple rows for single date, GA seems to pick up randomly a date and does not add up all the data for that date and this is basically meaningless once exported.

    – I managed to manipulate the data using pivots and created single row data per date BUT another issue arises – I have two mediums and when I combine them both in single export file (then it becomes 2 rows per date for 2 separate mediums), GA seems to pick up only one of the mediums and completely ignores the other in the import for some reason.
    https://uploads.disquscdn.com/images/ead6e987b869af240406c2968ea1b1bc6135fb47c8ad0f3ca59994966fba4347.jpg

    – i noticed that even though a single medium is successfully exported and visualised in GA, it erratically disappears or appears in the GA GUI with no indication why and when it happens (in the meantime I am not uploading anything else).

    Thanks for the time to respond to my issues.

    Ivo Di April 20, 2018 at 2:27 pm Reply

    1. Hi, anybody here?

      Ivo Di April 23, 2018 at 2:05 pm Reply

  56. Hi Ritwik thanks for this great tutorial! Just wondering whether this script can pull pixel data such as website purchase conversion value? Thanks again 🙂

    Lana Andrews April 23, 2018 at 12:55 pm Reply

  57. Thanks for this amaizing implementation Ritwik!

    I have a question for you:

    Is there a way to access to the Facebook Pixel Conversion Fields?

    I was trying with:

    var FB_FIELDS = ‘date_start,ad_name,adset_id,adset_name,campaign_id,campaign_name,spend,impressions,unique_inline_link_clicks,{‘action.type’:’offsite_conversion’:’fb_pixel_lead’};

    But doesn’t work 🙁

    Thanks in advance for your answer.

    Maxi Tromer April 26, 2018 at 2:23 am Reply

    1. Hi Maxi just wondering whether you were able to get this to work? I’m experiencing a similar problem 🙁

      Lana Andrews April 27, 2018 at 12:38 pm Reply

  58. Thanks for the great tutorial!

    I’m getting the following message:

    (#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v3.0.

    Can you help?

    Tiago Leão May 9, 2018 at 9:28 pm Reply

    1. Try Replacing all “v2.11” to “v3.0” in you Code,gs file. Let me know if that works

      Thanks
      Ritwik

      Ritwik B May 20, 2018 at 1:53 pm Reply

  59. Great tool, but I’m getting the following error: TypeError: Cannot find function split in object 2,1

    Any ideas?

    Thanks!

    Nick Darlington May 17, 2018 at 6:19 pm Reply

  60. Any suggestions on this? When I attempt: Cost Data > Facebook > Facebook Data Export I get this error:

    (#3) Application does not have the capability to make this API call.

    Dianna May 28, 2018 at 6:27 am Reply

    1. Note _ I have resolved this. Thanks

      Dianna May 28, 2018 at 7:18 am Reply

  61. This is just fantastic and exactly what I needed!

    Question – has anyone used the ‘website_purchase_roas’ data field? If so, how do you pull that into the sheet using a ga:XXXX? Essentially what does the XXXX need to be in order for it to match the ROAS data field?

    John May 30, 2018 at 9:03 pm Reply

  62. After changing the v2.11 to v3.0, now the scripts throws this error:
    TypeError: Cannot find function replace in object [object Object]

    I dont know why since replace is a really common javascript function, but it doesnt state in which line is happening.

    Ezequiel Boehler June 6, 2018 at 1:33 am Reply

    1. I have also changed the v2.11 to v3.0 and also received the error: TypeError: Cannot find function replace in object [object Object]

      @ritwikb:disqus did you already encounter this error and/or have solution?

      Or maybe someone else?

      Thanks in advance!

      Wiebe June 7, 2018 at 1:50 pm Reply

      1. I had the same error when using many (almost all) columns. I tried again with only the columns suggested here and it worked for me to only change it to v3.0. By the way: In the screenshot for the setup of the Analytics Data Import, you have selected ga:adGroup while in the later screenshots of the spreadsheets, the column header is ga:adContent. Is it correct like that? I just started to try and set it up yesterday. Thanks for this amazing help and contribution!

        ladiko June 20, 2018 at 3:18 pm Reply

  63. Hi. It Seems that facebook has updated the Ads API version to 3.0. Therefore when running “Facebook data Export” on sheets i get this message: (#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v3.0.

    How can this be fixed?

    Many thanks Ritwik

    Gianmarco June 19, 2018 at 4:59 am Reply

  64. Hello, I am getting an error for line 312 : “TypeError: Cannot read property “source” from undefined.”
    What’s strange is that the script works for the date range 2017-01-01 – 2017-06-30, but not for 2018. My script was working fine until a few days ago.

    Amandine Henrard June 22, 2018 at 8:20 pm Reply

    1. Hi Ritwik,

      Thanks for the awesome guide! I’m getting the same error though?

      Chris June 26, 2018 at 3:07 pm Reply

  65. I replaced all API calls by v3.0. If I export the field ad_name, i get the URL encoded version of adset_name. So for example adset_name is “Purchase 0 -10 % Purchase + Top 25% wv” and ad_name is “Purchase+0+-10+%25+Purchase+%2B+Top+25%25+wv” – any idea why?

    ladiko July 11, 2018 at 6:02 pm Reply

  66. @ritwikb:disqus thank you for the post.

    (#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v3.0.
    how do i fix this error?

    Renova Integra July 14, 2018 at 3:17 am Reply

    1. Hi,

      To fix this error, go to the facebook app panel, in the settings to see which version you use.

      After, change the 3.1 in the 3.x version you use in this line in the code.gs

      //var requestEndpoint = “https://graph.facebook.com/v3.1/act_”+FB_AD_ACCOUNT_ID+”/insights?”

      bastienSR December 3, 2018 at 2:15 am Reply

  67. Hi Ritwik,

    Thank you very much for this post, I found it extremely helpful!

    Thomas Craenen July 17, 2018 at 7:33 pm Reply

  69. Wow this is awesome! Thanks for pulling this together

    Jason Gilligan July 26, 2018 at 6:28 pm Reply

  70. Hi @ritwikb:disqus, thanks a lot for your amazing article! I was wondering, how can I see the results uploaded in Google Analytics?

    Clément Galopin July 31, 2018 at 3:40 pm Reply

  71. Hi!
    How to update the script for working with Facebook API 3.1?
    I get an error in the replace function every time I run the script(

    Artem Seyanko August 14, 2018 at 5:43 pm Reply

  72. Hi. I can’t authorise with facebook it shows me this error :

    Invalid Scopes: ads_read. This message is only shown to developers. Users of your app will ignore these permissions if present. Please read the documentation for valid permissions at: https://developers.facebook.com/docs/facebook-login/permissions

    Raimonds August 17, 2018 at 3:31 pm Reply

  74. https://uploads.disquscdn.com/images/df927e88382acc4256b67084b16cc1c71df5d94b5403926e77ffd3df88c3f7bf.png

    Hi Ritwik,
    Thank you very much for the step by step tutorial,
    I have done all the above mentioned procedures, but, when I tried to export data into excel, I’m getting below mentioned error. Also, the ‘select account’ drop down is empty for me unlike your screenshot. Did I miss something critical here?can you kindly help me on how to proceed with this ?

    Shriram Sridhar August 22, 2018 at 12:18 pm Reply

  75. Can you add “website purchase Conversion” as a field? because i want to check “spend” vs “website purchase Conversion”

    Ravikant Makwana September 2, 2018 at 9:00 pm Reply

  76. Hello Ritwik,
    Thank you so much for this great tool – it is working like a charm if I run it manually but I’m having some issues with the formating of the sheets file when I automate the process.

    I seem to get the FB header names into the sheet which are then missing the preset source / medium data and showing 2 colums for date….

    https://uploads.disquscdn.com/images/a2e29957904fd310859d2c2ff9ee0746d4b07705a1c7e818ad7a9cb31558beed.jpg

    I’m sure I’m making a simple mistake but just can’t figure out what the issue is… would be great if you could point me in the right direction….

    best
    Philipp

    Philipp Schindler September 4, 2018 at 9:51 pm Reply

    1. I’m having the exact same issue. I’ve gone through the script but cannot find a way to remove the headers from being loaded.

      Thomas Craenen September 18, 2018 at 3:01 pm Reply

      1. Hey Thomas – see my comment above, I hope it helps!

        Brad Farleigh October 9, 2018 at 12:53 pm Reply

    2. It’s not in the instructions, but in order for the headings to work properly (and for your utm tags to be output) you need to tick the “GA Upload Format” option in the sidebar widget

      Brad Farleigh October 9, 2018 at 12:53 pm Reply

  77. Hi.
    I have recently found a really useful tool for this purpose https://www.owox.com/products/bi/pipeline/facebook-to-google-analytics/
    If we are talking about automated setup to import facebook cost data in google analytics and want this process to be simple and fast, it will be the best solution and won’t take much time and afforts.

    I hope it was helpful)

    Maria Smith September 20, 2018 at 7:37 pm Reply

  78. Hi Ritwik, thanks for the tool.
    I Followed all steps and everything works just fine: the final upload gets no errors, but still I can’t see any data in GA! How can I be sure that somewhere data are taken in GA? I need to upload cost of my facebook campaigns and ga:adCost field is full of data, however I don’t find them anywhere in GA. hope you can help, thanks.

    Blu Oberon Milano October 3, 2018 at 5:31 pm Reply

  79. Bonjour Ritwik,
    Super article, merci. Le mécanisme fonctionne bien chez moi.

    Par contre j’aimerai filtrer l’import par nom de campagne (filtering : Field /operator /value) avec des valeurs que je souhaite lui indiquer.

    Mais je ne vois pas comment l’indiquer dans la syntaxe du projet.

    merci

    Kévin October 18, 2018 at 5:56 pm Reply

  80. I get the following error: (#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v3.2.

    Martina November 20, 2018 at 5:06 pm Reply

    1. Hi,

      Same error here. I tried to change the API version used in the FAcebook App settings but I don’t have the choice to select 3.1. The only version available is the 3.2.

      Any idea how to work around this problem ?

      Thanks a lot for the scripts. It’s very impressive and great to share it.

      Bastien

      bastienSR December 3, 2018 at 2:13 am Reply

    2. I find a solution :

      To fix this error, go to the facebook app panel, in the settings to see which version you use.

      After, change the 3.1 in the 3.x version you use in this line in the code.gs

      //var requestEndpoint = “https://graph.facebook.com/v3.1/act_”+FB_AD_ACCOUNT_ID+”/insights?”

      bastienSR December 3, 2018 at 2:16 am Reply

  81. Question for the community, and I’ve seen variations of this one asked a couple times in the last 7 months. “Conversions” is returning only a specific set of actions that seem to be built-in. Does anyone know of a way to access custom conversions, or what are reported as “Results” in the native ads reporting? This one has been eluding me for a really long time.

    Thanks everyone!

    Stephen Selman October 8, 2020 at 10:35 pm Reply

    1. And to clarify, because this echoes another post – “conversions” is returning 0s across the board for me.

      Stephen Selman October 8, 2020 at 10:35 pm Reply

  82. I’m stuck when I try to upload the data from the spreadsheet to GA. I’m using v8.0 of the FB app, and am only able to export from FB to spreadsheet when I use that version. However, when I try to upload it to GA nothing happens (no notification pop-up with “Data has been sent to GA, checking errors…”). This notification does show up when I change version number to 6 in appsscript.json file. I already tried solution 1 from the frequent errors, but I can’t succeed in that because it gives me an error in the coding. Could anyone help me?

    Thom Melching November 2, 2020 at 8:25 pm Reply

    1. @ritwikb:disqus could you perhaps help me with this? 🙂

      Thom Melching November 2, 2020 at 8:25 pm Reply

    2. Hi Thom, could you please share the code for 8.0 with me? I’ve been unable to make disappear the error prompting to update. Thank you!

      Sebastian Garbo Arancibia November 8, 2020 at 8:13 pm Reply

  83. Hello Ritwik, amazing work here!
    It’s working on version 8.0
    I wonder if there is a chance to add the “results” into the sheet. In my case, Leads.
    Thank you!

    Sebastian Garbo Arancibia November 9, 2020 at 12:25 am Reply

  84. Hi, when I go to step 3 and click OAuth Redirect URI I get the following error – Sign in with Google temporarily disabled for this app
    This app has not been verified yet by Google in order to use Google Sign In.

    Any idea how to fix it?
    Thank you

    Maria December 4, 2020 at 3:46 pm Reply

  85. (#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v9.0.”,”type”:”OAuthException”,”code”:2635,”fbtrace_id”:”AAHiENxHn0f0U0VJduhUqYs”}}

    I changed the .setTokenUrl to v9.0 but I gets the same error.

    Elemér Fodor December 14, 2020 at 7:42 pm Reply

  86. Hi, whenever I try to do the oAuth Redirect URI – it comes up with the following error:
    “drive.google.com failed to connect.”
    What can I do to fix this? I’ve gone through the setup twice now to make sure that I hadn’t missed any steps before this stage, but nothing seems to work. When prompted to grant permission for the script to run, I’ve granted it permission so I’m not sure what to try next.
    Thanks for your help!

    Doron June 22, 2021 at 1:30 am Reply

    1. You might have logged in to multiple emails in the browser. Can you try to login via single email.

      Ritwik B June 22, 2021 at 5:34 am Reply

      1. Yeah that was right, thanks for the reply 🙂
        Is there a way to factor in a currency conversion before upload to GA?
        For example if I’d like to multiply the cost from Facebook by 1.3, is there somewhere I can add a line of code so it’s done automatically?
        Thanks for your help!

        EDIT: Got it to work through macros and then scheduling it to run after the facebookData function but before the uploadDataToGa function – in case anyone else wanted to know how to do this.

        Doron June 24, 2021 at 7:52 pm Reply

  87. I’m trying to set up this tool just to automatically import ad results to a spreadsheet. Got it set up and authorized, but when I click “export data,” nothing happens and the spreadsheet stays blank. Any idea what’s happening here?

    Emmett Werbel October 15, 2021 at 10:43 am Reply

  88. Hi, guys! I’ve been using this add-on for years and it’s working great so far. I received an email from Facebook saying they will discontinue all API version below 15.0. With that said, how can we upgrade the add-on so it wont have any issues? I did a test and upgraded the app to v16 (in FB developers). Changed the URL in my code, but it returns versions error. it looks like the GScript is “limited” to v14.

    Henrique March 3, 2023 at 9:18 am Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Posts: