Search API Connector Documentation

Print

Import TikTok Ads Data to Google Sheets

Last Updated On
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.

Note that the main reports endpoint changed from /reports/integrated/get to /report/integrated/get in TikTok's Aug 2022 API update. More info >

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 (paid feature), 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

44 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

    • I haven't tested this myself but it looks like their video API requires OAuth, which isn't that simple. If you'd like to give it a shot, though, these are the basic steps:
      1. Register a TikTok Developer account
      2. Create a Tiktok app
      3. Use API Connector's OAuth manager to create a custom OAuth connection. Auth URL = https://www.tiktok.com/auth/authorize/?scope=video.list, Token URL = https://open-api.tiktok.com/oauth/access_token/, Client ID & secret come from TikTok
      4. Select the new custom OAuth connection and run one of their example request URLs like POST https://open.tiktokapis.com/v2/video/list/?fields=cover_image_url,id,title

      Reply

      • I keep getting the error "We couldn't log in with TikTok. This may be due to specific app settings. If you're a developer, correct the following and try again: client_key"

        Not sure what I am doing wrong. Any thoughts?

      • I can't say for sure, but the error message says the client_key is incorrect, and their docs say the client_key is "The unique identification key provisioned to the partner." So it sounds like there's some client key you need to get and add to the end of your authorization URL like https://www.tiktok.com/auth/authorize/?scope=video.list&client_key=12345.

      • That worked great! It is now telling me to include a redirect_uri. Is this the redirect I set up in my app or is this something else? What should it look like?

      • Thank you for all the helpful resources! The oauth connection works. When I try and use their example request I get a 404 error that says "Unsupported path(Janus)". Do I need to run a request to get an access token or something else before I try and pull the data?

      • Hey Mason, as long as you've selected your custom OAuth connection, you shouldn't need any other tokens or authentication before you pull the data. The error message says "unsupported path" so that sounds like some problem in the URL. What happens when you try a really simple request like GET https://open.tiktokapis.com/v2/user/info/?

      • Hi Ana,

        I have run into another issue and would love your thoughts. I tested the request URL you sent last but I keep getting this error
        {"data":{},"error":{"code":"access_token_invalid","message":"The access token is invalid or not found in the request.","log_id":"20230222185113619BA4A626D82C298B27"}}
        I went to this page (https://developers.tiktok.com/doc/login-kit-manage-user-access-tokens/) on the website and was able to build a request to get an access token. I have the token but I don't know where to put it. Do I add it to the OAuth manager or to the header? I have tried the header but it doesn't work for me. Any thoughts?

      • If you have the token, you'd put it in a header, where Key = Authorization, Value = Bearer your_token. In that case you wouldn't add any OAuth since you're manually including the token yourself. But this is a bit odd, you shouldn't have to manually add a token if you've set up an OAuth connection. Let me set up a TikTok account and test this out myself, I'll get back to you soon.
        Update: Well they rejected my TikTok app, so I haven't fully tested this out. But I think I found the issue, again TikTok doesn't fully follow the OAuth2 standard (they use the parameter client_key instead of the standard client_id). So again this will require a set of custom requests.

Leave a Comment

Jump To...