API Connector Documentation

Print

Import TikTok Ads Data to Google Sheets

Last Updated OnApril 20, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to import TikTok Ads data directly into Google Sheets, using the API Connector add-on for Sheets.

The TikTok Ads API is quite strange in that it uses a non-standard implementation of the OAuth2 standard. Therefore we will get our token through some custom API requests rather than through API Connector's automatic OAuth2 connection manager.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Create a TikTok App

  1. Navigate to https://ads.tiktok.com/marketing_api/ and register for a new account.
    tiktok-ads-img1
  2. Once you're logged in, click on My Apps in the top nav
    tiktok-ads-img2
  3. From the Apps screen, click Create New
    tiktok-ads-img3
  4. Set up your App, choosing the permissions you'd like access to (you'll likely want Ads Management and Reporting). You can set the Advertiser redirect URL to anything, but https://google.com is good.
    tiktok-appconfig
  5. Your app will initially be in Pending status. Once it's approved (may take up to 2 days) it will look like this. Click the App Name to see the app details screen.
    tiktokads-appname
  6. You'll now see your App ID and Secret. Keep those handy, we'll need them shortly.
    tiktokads-credentials

Part 2: Get Your Auth Code

  1. Still in the Basic Information page, copy the entire Advertiser authorization URL to your clipboard
    tiktokads-authurl
  2. Paste this URL into a web browser
    tiktok-ads-img9
  3. You will see a page prompting you to log in and accept the permissions you selected while creating your app. Click Confirm.
    tiktok-confirm
  4. For security, they'll send a code to your email address, which you'll need to enter.
    tiktok-code
  5. You'll now be sent to the site you sent as your callback URL (google.com in our example). On this screen you'll see a URL in your URL bar. We're looking for the value in the auth_code parameter, so grab that and keep it handy.
    tiktok-authcode
  6. All right, we're all set with our values. Let's hop over to API Connector and get our access token.

Part 3: Get Your Access Token

To get your access token, set up a request as follows:

  • Application: Custom
  • Method: POST
  • Request URL: https://business-api.tiktok.com/open_api/v1.3/oauth2/access_token/
  • Headers
    • Key = Content-type, Value = application/json
  • Request body: {"secret":"your_secret","app_id":"your_app_id", "auth_code":"your_auth_code"}

Substitute in your secret, app ID, and authorization code. Name your request and click Run . The whole thing should look like this:
tiktok-accesstoken

Note the value in the data.access_token field. Congrats, that's your token. Finally! We won't need to do those earlier steps again. This token is what we'll use for getting ad data.

Part 4: Get TikTok Ad Data in Sheets

To create a request, include your full request URL in the request URL field, and include your access token in the Headers section, where Key = Access-Token and Value = your token.

As an example, enter a request like this.

  • Application: Custom
  • Method: GET
  • Request URL: https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id=11111111111&report_type=BASIC&dimensions=["stat_time_day","ad_id"]&data_level=AUCTION_AD&start_date=2022-01-01&end_date=2022-01-31&metrics=["spend","impressions","cpc","cpm","ctr","reach"]&order_field=impressions&page_size=1000
  • Headers
    • Key = Access-Token, Value = your_token
  • Edit Fields: TikTok's API may send back data in an inconsistent order, so use the visual field editor to lock fields to specific columns

tiktok-responsedata

You'll see several types of parameters in the URL:

  • The "advertiser_id" is where you enter your own advertiser ID. You can get this by logging into your Tiktok ads account. Available advertiser IDs are also returned in your original access token request.
  • The "dimensions" parameter indicates how to group the data. Available dimensions include IDs (advertiser_id, campaign_id, adgroup_id, ad_id), Time (stat_time_day, stat_time_hour), and Location (country_code)
  • The "metrics" parameter indicates the fields you'd like to include., e.g. campaign_name, spend, cpc, clicks, etc. All available metrics are listed here.
  • The "data_level" parameter shows how granular you want your data (by account, campaign, adgroup, or ad). Possible values include AUCTION_AD, AUCTION_ADGROUP, AUCTION_ADVERTISER, AUCTION_CAMPAIGN
  • The "order_field" parameter shows which field to order by.
  • The "page_size" parameter shows the number of records to return; see the section called Handle Pagination for some more info on that.

The documentation contains the full list of available metrics (and other parameters).

Part 5: Handle Pagination

By default, TikTok will return only 10 rows at a time. To get more you need to use the page and page_size parameters as shown in their API documentation:
tiktok-ads-img17

You can add these parameters manually or cycle through pages automatically using API Connector's pagination function, like this:

  • API URL: enter your request URL, including &page_size=1000
  • Pagination type: page parameter
  • Page parameter: page
  • Run until: choose when to stop fetching data
pagination-page-parameter

Part 6: API Documentation

Official API documentation: https://ads.tiktok.com/marketing_api/docs?id=1738864915188737

55 thoughts on “Import TikTok Ads Data to Google Sheets”

  1. This does not work for me, as I only get an empty result []. When redoing the first steps I realise there are no authorised accounts (although I use the TikTok Ads account I run all the campaigns with). Could this be the problem? How do I authorise accounts?

    Reply

    • Sorry, I'm not sure how to authorize accounts either. On my side, I created the app and then plugged in my advertiser account ID, I didn't authorize an account. Which request is returning []?

      Reply

  2. Hi Ana, I've followed all of the steps above but after running it I'm getting a code 40007 saying that The advertiser [my App ID] doesn't exist or has been deleted. Please help!

    Reply

  3. Hello Ana, thanks for sharing the information on Tiktok API.

    I am a researcher and I have a question. I have identified over 200 doctors on tiktok (creating tiktok doctor yellow page ). Since their numbers of followers are constantly changing, is there a way to track & update their followers using API?

    Reply

  4. Hi Ana, great article! Is there any way to set the call to always return yesterday's data rather than a fixed date in start_date and end_date?

    Reply

    • Sure, you can create a sheet containing a dynamic date in yyyy-mm-dd format, e.g. =text(today()-1,"yyyy-mm-dd").
      Then you can reference that cell in your request like &start_date=+++Sheet1!A1+++. Please check this article for more info.

      Reply

      • Thank you! That works like a charm!

        One thing I don't quite understand though - the order in which the results appear changes every time I run the API. One time it will be request_id - page_info - campaign_id - time_day - spend - campaign_name and the next it will start with spend or with the time, even when the API hasn't changed at all. I don't understand why it does that or how I can extract meaningful data from that without manually looking up what is to be found in which column each day. Any ideas?

      • Hey Arian, some APIs change the order in which they send back data. To address, please click Edit Fields before running your report. That will open the visual field editor where you can select fields and "lock" them in place, regardless of how the API sends back data.

    • I believe there's a one year limit as their documentation says "A query timespan must be set with the start_date and end_date request fields. Timespan for reports cannot exceed 365 days." I'm not sure why you're only getting 30 days, could it be related to pagination? By default they only return 10 rows unless you set a page_size=1000 parameter. Do you get an error when you request more than 30 days data, or you just don't get as much data as you expect?

      Reply

    • Hey Kris, sorry, I didn't even know TIkTok had seller shops until now, so I don't have any instructions for that. It looks like you can get a token by following their authorization instructions as shown here, but they also require attaching a "signature" that applies the hmac-sha256 hashing algorithm. In short, this API's requirements seem pretty custom/complex and not something that API Connector handles out of the box. I'll look into whether we can add this as a preset integration in the future.

      Reply

    • Sure, I think the request in the example will automatically fetch data for all the ad IDs in the account. If you want to fetch data for different advertiser IDs, you can do that by listing out all the request URLs one after the other and cycling through them in a multi-query request.

      Reply

  10. Hello, when using the API request I'm not getting the ad data. Just the following columns:

    code message request_id data.page_info.total_number data.page_info.page data.page_info.page_size data.page_info.total_page data.list.1

    I'm using this API request:
    https://business-api.tiktok.com/open_api/v1.2/reports/integrated/get/?advertiser_id=XXX&report_type=BASIC&dimensions=["stat_time_day","ad_id"]&data_level=AUCTION_AD&start_date=2022-07-01&end_date=2022-07-02&metrics=["spend","impressions","cpc","cpm","ctr","reach"]&order_field=impressions&page_size=1000

    How can I get the metrics I request in the API?

    Reply

    • Can you try clicking Edit Fields > View raw response to check the original data response from the API?
      If you can see fields in the response that aren't in your sheet, then it sounds like you may have filtered out the fields from your sheet. In that case, remove the filters from each field via the preview pane (or just click Reset All to remove them all at once).
      If those fields aren't in the API response either, then it sounds like there's no data for the date range you've selected. In that case you'll need to choose a longer date range. Please check if that helps.

      Reply

      • Thank you, it's working now. I have a different question. Is it possible to request data for several advertiser IDs in one request. For example, something like this:

        https ://business-api.tiktok.com/open_api/v1.2/reports/integrated/get/?advertiser_id=1/2/3&...

        Currently, I have 3 ad accounts and for each I'm using a separate request. So I'm wondering if it's possible to include all 3 advertiser IDs in the request to get all data, or if there is any other way to have the data from 3 ad accounts in one sheet.

      • Tiktok doesn’t enable multiple advertiser IDs in a single request, but you can use API Connector’s multi-query function for this. Just list the URLs one after the other in the URL field and API Connector will print each response into the same sheet. Please see here for more info: Multi-Query Requests

  12. Hello Ana,
    I’ve followed all of the steps above but after running it I’m getting a code 40007 saying that The advertiser doesn’t exist or has been deleted.
    Please help

    Reply

    • This is the only info in their docs for this error code, it says "The operation/object does not exist." which is about the same as the error message you see. So it sounds like the advertiser ID you've entered doesn't exist, can you please double check that it's correct and entered without typos?

      Reply

  13. Hi Ana

    I would like to filter my data based on campaign ID. What do I need to add to the request URL to filter the data by Campaign ID. Also would it be possible to filter by a list of campaign ID's that are in a google sheet?

    Reply

    • You can make a request like this to filter by campaign ID: https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id=1111111111111&report_type=BASIC&dimensions=["stat_time_day","campaign_id"]&data_level=AUCTION_CAMPAIGN&start_date=2022-12-01&end_date=2022-12-31&metrics=["spend","impressions","cpc","cpm","ctr","reach"]&order_field=impressions&page_size=1000&filtering=[{"field_name":"campaign_ids","filter_type":"IN","filter_value":"[16880472,168993623]"}]. (Here's their documentation on filters). If you have a list of campaign IDs in your sheet, you can turn them into a comma-separated list with a function like =JOIN(", ",A2:A10), and then reference that cell in your query like "filter_value":"[+++Sheet1!A1+++]"

      Reply

    • Awesome, glad to hear it. We'll also be adding TikTok Ads into our list of integrated connectors, so this will all be a bit easier in the future.

      Reply

  16. Hi, I've got this working really well, but I have one question. Can I execute API calls via the connector using a button or cell in the sheet itself? The context is I have some dynamic parameters set up so that my API request is modified based on my data level selection (AUCTION_CAMPAIGN, AUCTION_AD, etc.) and I'd like to be able to trigger an API call ad hoc using this.

    https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id=xxxx&report_type=BASIC&dimensions=+++tik_tok_QA_sheet!b5+++&data_level=+++tik_tok_QA_sheet!b4+++&start_date=+++tik_tok_QA_sheet!c2+++&end_date=+++tik_tok_QA_sheet!c3+++&metrics=+++tik_tok_QA_sheet!b6+++&order_field=impressions&page_size=1000

    The goal is to allow people with no knowledge of the API connector to execute some standard QA checks. It all functions right now ... I just have to execute each call via the UI.

    Thanks

    Reply

    • Sort of 🙂 There are 2 ways to execute requests without going through the sidebar:
      1) Click Extensions > API Connector > Refresh All Now to run all the requests saved in that sheet
      2) Use the IMPORTAPI custom function. This can be tied to a checkbox such that it's similar to clicking a button.

      Reply

  18. Buena tarde, tengo una duda con las dimensiones ya ue no quiero que me salga las métricas desglosadas por día, sino que me salga solo una línea de campaña con las métricas del mes, ya que intento pero no se que estaré haciendo mal, me podrías dar un ejemplo, gracias

    Reply

    • If I've understood Google Translate correctly, you're asking how to group data by month instead of day. Unfortunately this API only supports the following time dimensions: stat_time_day and stat_time_hour. There is no monthly dimension; you would need to group that yourself.

      Reply

    • Are you referring to the marketing API (i.e. TikTok Ads?). If so, that's odd, as it shouldn't expire at all. On my side the token doesn't expire, and their docs say "Marketing API authentication: Refer to Marketing API authentication to learn how to generate a long-term access token. This access token does not expire."

      Reply

  20. We have a media account that has multiple brands under one account ID. We only need to pull one of them in the weekly API pull and am wondering if there is a way to filter?

    Reply

  21. Hey! Is it possible to order by date? Say that is not supported, but this is annoying.

    By the way I've tried you're solution and it still didn't work:
    I think that's the default. Can you please try leaving the "order_fields" parameter off entirely?"

    I'm sending the request so you can look what i've done:
    https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id=111111111111&report_type=BASIC&dimensions=["stat_time_day","campaign_id"]&data_level=AUCTION_CAMPAIGN&start_date=2024-06-01&end_date=2024-06-22&metrics=["spend","campaign_name","conversion"]&page_size=1000

    and i've tried also like this:
    https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id=7352547999584894993&report_type=BASIC&dimensions=["stat_time_day","campaign_id"]&data_level=AUCTION_CAMPAIGN&start_date=2024-06-01&end_date=2024-06-22&metrics=["spend","campaign_name","conversion"]&order_field=&page_size=1000

    Reply

  22. Hi, if I want to pull the data of the last 30 days every day again? What should I write in the start date + end date so that it is automatic?

    Reply

    • Let's say you have a tab called Inputs. Enter the following formulas.
      cell A1: =text(today()-30,"yyyy-mm-dd")
      cell A2: =text(today()-1,"yyyy-mm-dd")

      Now change your request URL to reference cells, like this: &start_date=+++Inputs!A1+++1&end_date=+++Inputs!A2+++. You won't need to update your request again. For more info on using referencing cells see here: Use Cell Values in Requests

      Reply

      • Still not working 🙁
        I've put cell A1=text(today()-30,"yyyy-mm-dd")
        And in cell A2= =text(today()-1,"yyyy-mm-dd")

        And my Destination sheet is Inputs
        and my Request Url is
        https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id=11111111111&report_type=BASIC&dimensions=["stat_time_day","campaign_id"]&data_level=AUCTION_CAMPAIGN&start_date=+++Inputs!A1+++1&end_date=+++Inputs!A2+++&metrics=["spend","campaign_name","conversion","online_consult"]&order_field=impressions&page_size=1000

        And when I run it the code is: 40002 + the message is start_date: Not a valid datetime.

        can you look into it?

      • From what you've pasted in here, your start_date value is start_date=+++Inputs!A1+++1 so please remove the extra "1" from the end.
        If that's not the issue, please feel free to contact support so we can take a look at your sheet.

  23. Hello,
    On step 2, part 3 I hit confirm, and was brought to my callback URL (no email verification needed for me). However, the URL didn't have an auth_code parameter, or any other parameters for that matter. It was just the same URL I entered for the callback URL when I built the app in TikTok. Any suggestions are appreciated, thanks

    Reply

Leave a Comment

Jump To