Tag Archives: using google apps script

Google Shopping ads, Marketing, Woocommerce

Woocommerce Rest API – link data với google sheet

Posted on by bientapvien
08
Jan

1. Generate Keys – tạo API key trong Woocommerce setting

To start using REST API, you first need to generate API keys.

  1. Go to WooCommerce > Settings > Advanced
  2. Go to the REST API tab and click Add key.
  3. Give the key a description for your own reference, choose a user with access to orders etc, and give the key read/write permissions.
  4. Click Generate api key.
  5. Your keys will be shown – do not close this tab yet, the secret will be hidden if you try to view the key again.

To test API in test tool (ex: https://reqbin.com/), username = consumer key, password = consumer secret 
Note: + Using https, header with authentication (username & password) can not be read.

+ If error 401 => check security measures (server firewall and/or security plugin – ex: ithemes security … to block API access)

2. Woocommerce Rest API endpoints: (orders/products)

Endpoints are located at WooCommerce > Settings > Advanced.

List all products: domain//wp-json/wc/v3/products

Ref: https://woocommerce.github.io/woocommerce-rest-api-docs/#introduction

WooCommerce GET request examples

+ GET request to view all orders
https://yourcompany.com/wp-json/wc/v3/orders/
+ GET request to view a single order
https://yourcompany.com/wp-json/wc/v3/orders/{insert order ID}
+ GET request to view all products
https://yourcompany.com/wp-json/wc/v3/products/
https://yourcompany.com/wp-json/wc/v3/products?type=variable&per_page=50     //Type = variable products & 1<per_page<100

+ Get all variations of a product: => to get all variations of all products, we need to loop through all variable product IDs.
/wp-json/wc/v3/products/<product_id>/variations

+ GET request to view a single product
https://yourcompany.com/wp-json/wc/v3/products/{insert product ID}
+ GET request to view a single product variation
https://yourcompany.com/wp-json/wc/v3/products/{insert product ID}/variations/{insert variation ID}
+ GET request to view all customers
https://yourcompany.com/wp-json/wc/v3/customers/
+ GET request to view a single customer
https://yourcompany.com/wp-json/wc/v3/customers/{insert customer ID}

Ref: https://sgwebpartners.com/how-to-use-woocommerce-api/

Routes vs Endpoints

A route is the “name” you use to access endpoints, used in the URL. A route can have multiple endpoints associated with it, and which is used depends on the HTTP verb.

For example, with the URL http://example.com/wp-json/wp/v2/posts/123:

  • The “route” is wp/v2/posts/123 – The route doesn’t include wp-json because wp-json is the base path for the API itself.
  • This route has 3 endpoints:
    • GET triggers a get_item method, returning the post data to the client.
    • PUT triggers an update_item method, taking the data to update, and returning the updated post data.
    • DELETE triggers a delete_item method, returning the now-deleted post data to the client.

3. Authentication with google scripts:

Solution from google – basic authentication

var USERNAME = 'your_username';
var PASSWORD = 'your_password';
var API_URL = 'http://<place_api_url_here>';

var authHeader = 'Basic ' + Utilities.base64Encode(USERNAME + ':' + PASSWORD);
var options = {
  headers: {Authorization: authHeader}
}
// Include 'options' object in every request
var response = UrlFetchApp.fetch(API_URL, options);

Ref: https://developers.google.com/google-ads/scripts/docs/features/third-party-apis
Ref: https://stackoverflow.com/questions/23546255/how-to-use-urlfetchapp-with-credentials-google-scripts
Ref: https://badlywired.com/2018/01/how-to-authenticate-wordpress-rest-api-from-google-sheets-scripts/

https://stackoverflow.com/questions/16027002/google-apps-script-and-external-api-authorization-failing-in-header

4. Output Woocommerce data to Google Sheet

After all 3 above steps, we need to output data to Google Sheet.

Issue 1: To protect server, Woocommerce API returns 100 result/page (Ref: Rest API pagination)

=> Solution: loop to all API URL để lấy toàn bộ các API URL  => Loop through all Url returned data to append each product/order to Google Sheet.

Issue 2: Setup the script to run on a specific time (ex: at 00:00 each day)

=> Solution: Using trigger

4.1 Woo API Pagination

Woocommerce API limit per page: maximum 100 results/page => If the result set is 500 products/order => 5 API URLs 

Ex: URL 1: yourdomain/wp-json/wc/v3/products?per_page=100&page=1 (first 100 results)

      URL 2: yourdomain/wp-json/wc/v3/products?per_page=100&page=2 (2nd 100 results)

      URL 3: yourdomain/wp-json/wc/v3/products?per_page=100&page=3 (3rd 100 results)

      URL 4: yourdomain/wp-json/wc/v3/products?per_page=100&page=4 (4th 100 results)

      URL 5: yourdomain/wp-json/wc/v3/products?per_page=100&page=5 (5th 100 results)

How to know how many pages & products/orders does the data set have:  

based on the API header below:  

  • X-WP-Total: Total returned records – Using Postman or reqbin.com to see 
  • X-WP-TotalPages: Total returned pages

Ref: Retrieve entire data from paginated API recursively 

4.2 Actual code:

Script example:

function myFunction() {
var USERNAME = 'comsumer key';
var PASSWORD = 'secret key';
var headers = {
"Accept": "application/xml",
"Content-Type": "application/xml",
"Authorization": "Basic "+ Utilities.base64Encode(USERNAME+":"+PASSWORD)
};
var options = {
"method" : "get",
"headers" : headers 
};
Logger.clear();
for (page = 1; page < 4; page++) { //see header X-WP-TotalPages to know total how many pages returned
var URL = 'Yourdomain/wp-json/wc/v3/products?per_page=20'+'&page='+page;
var response = UrlFetchApp.fetch(URL,options);
var data = JSON.parse(response); 
Logger.log(data.length);
var sheet = SpreadsheetApp.getActiveSheet();
// loop through the map and output to sheet
for (i = 0; i < data.length; i++) { 
sheet.appendRow([data[i].id,
data[i].name,
data[i].price,
data[i].stock_quantity,
data[i].stock_status,
]);
}
}
}

Ref: https://badlywired.com/2018/01/linking-wordpress-to-a-spreadsheet-using-wp-rest-api-and-google-sheets-scripts/

Ref: get all api url& fetch: https://stackoverflow.com/questions/56671010/how-can-i-iterate-through-multiple-urls-to-fetch-json-response-in-google-apps-sc

5. Using time-based script to automate script: 

+ Using time-driven trigger to wake up the main script everyday/week…

+ Clear the spreadsheet everytime the main script run to get fresh data

function startTimeTrigger() {
ScriptApp.newTrigger('main')
.timeBased()
.atHour(7)
.everDays(1)
.create();
};

6. Cell formatting in google sheet: 

Format a single column: 

var column = sheet.getRange("B2:B"); //select column B from cell B2
column.setNumberFormat("M/d/yy");

Ref: https://www.blackcj.com/blog/2015/05/18/cell-number-formatting-with-google-apps-script/