Mục tiêu:
+ Thay thế chức năng gửi email xác nhận đơn hàng của Woo (function này làm thời gian chờ của khách hàng kéo dài khi đặt trên web)
Workflow:
Đơn hàng mới => webhook => insert data to google sheet => create invoice + compose email => send out
+ Server side code: get data from google sheet
+ Email template sử dụng standard scriptlet để insert data từ google sheet vào template
Các bước:
1. Server code (apps script): Get data từ google sheet: apps script – getRange
2. Tạo email html template & insert data
2.1. Hướng dẫn chi tiết để insert data từ google sheet vào html template:
+ Để chèn data từ google sheet vào htmlTemplate, cần gán variable cho property “htmltemplate”:
var htmlTemplate = HtmlService.createTemplateFromFile("Template.html");
htmlTemplate.stocks = stockData;
Sau đó sử dụng loop “For” trong html template để loop data và insert vào html template.
Code cụ thể:
1. code apps script để get data từ google sheet:
2. Chèn data vào html template:
Các công cụ:
+ html design: https://codebeautify.org/htmlviewer
Ref 2: https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/
Ref 3: https://www.groovypost.com/howto/google-sheets-send-email-based-on-cell-value/
Ref 4: https://spreadsheet.dev/send-html-email-from-google-sheets
Ref:
example order woocommerce json:
{
"id":8714,
"parent_id":0,
"number":"8714",
"order_key":"wc_order_xxxxxxxxxxxx",
"created_via":"checkout",
"version":"3.6.5",
"status":"on-hold",
"currency":"VND",
"date_created":"2019-10-21T01:12:57",
"date_created_gmt":"2019-10-20T18:12:57",
"date_modified":"2019-10-20T18:12:58",
"date_modified_gmt":"2019-10-20T11:12:58",
"discount_total":"0",
"discount_tax":"0",
"shipping_total":"22000",
"shipping_tax":"0",
"cart_tax":"0",
"total":"172000",
"total_tax":"0",
"prices_include_tax":false,
"customer_id":1,
"customer_ip_address":"183.91.4.89",
"customer_user_agent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/77.0.3865.120 Safari\/537.36",
"customer_note":"",
"billing":{
"first_name":"",
"last_name":"Store",
"company":"",
"address_1":"xxxxxx, aa bb cc",
"address_2":"bbbbbbb",
"city":"Qu\u1eadn Nam T\u1eeb Li\u00eam",
"state":"H\u00e0 N\u1ed9i",
"postcode":"",
"country":"",
"email":"testwoo@gmail.com",
"phone":"212222222222"
},
"shipping":{
"first_name":"",
"last_name":"Store",
"company":"",
"address_1":"bbbbbbbbbbbbbb",
"address_2":"cccccccccccc",
"city":"Qu\u1eadn Nam T\u1eeb Li\u00eam",
"state":"H\u00e0 N\u1ed9i",
"postcode":"",
"country":""
},
"payment_method":"bacs",
"payment_method_title":"Chuy\u1ec3n kho\u1ea3n ng\u00e2n h\u00e0ng 100% s\u1ebd \u0111\u01b0\u1ee3c free ship",
"transaction_id":"",
"date_paid":null,
"date_paid_gmt":null,
"date_completed":null,
"date_completed_gmt":null,
"cart_hash":"d819xxxxxxxxa567d287bcebd",
"meta_data":[
{
"id":134415,
"key":"_shipping_phone",
"value":"12345678"
},
{
"id":134416,
"key":"is_vat_exempt",
"value":"no"
},
{
"id":134421,
"key":"_ivole_review_reminder",
"value":"0"
},
{
"id":134422,
"key":"_wcpdf_invoice_settings",
"value":{
"enabled":"1",
"attach_to_email_ids":{
"new_order":"1",
"cancelled_order":"1",
"customer_processing_order":"1",
"customer_completed_order":"1",
"customer_invoice":"1"
},
"display_shipping_address":"1",
"display_email":"1",
"display_phone":"1",
"display_date":"invoice_date",
"number_format":{
"prefix":"",
"suffix":"",
"padding":""
},
"my_account_buttons":"available",
"paper_size":"",
"font_subsetting":false,
"header_logo":"",
"shop_name":"",
"shop_address":"",
"footer":"",
"extra_1":"",
"extra_2":"",
"extra_3":""
}
},
{
"id":134423,
"key":"_wcpdf_invoice_date",
"value":"1571544778"
},
{
"id":134424,
"key":"_wcpdf_invoice_date_formatted",
"value":"2019-10-20 11:12:58"
},
{
"id":134425,
"key":"_wcpdf_invoice_number",
"value":"170"
},
{
"id":134426,
"key":"_wcpdf_invoice_number_data",
"value":{
"number":170,
"formatted_number":"170",
"prefix":"",
"suffix":"",
"document_type":"invoice",
"order_id":8714,
"padding":""
}
}
],
"line_items":[
{
"id":508,
"name":"test xxxxx",
"product_id":5509,
"variation_id":0,
"quantity":1,
"tax_class":"",
"subtotal":"50000",
"subtotal_tax":"0",
"total":"50000",
"total_tax":"0",
"taxes":[
],
"meta_data":[
{
"id":4047,
"key":"_reduced_stock",
"value":"1"
}
],
"sku":"",
"price":50000
},
{
"id":509,
"name":"Test1",
"product_id":5495,
"variation_id":0,
"quantity":1,
"tax_class":"",
"subtotal":"100000",
"subtotal_tax":"0",
"total":"100000",
"total_tax":"0",
"taxes":[
],
"meta_data":[
{
"id":4048,
"key":"_reduced_stock",
"value":"1"
}
],
"sku":"",
"price":100000
}
],
"tax_lines":[
],
"shipping_lines":[
{
"id":510,
"method_title":"GHTK (Giaohangtietkiem.vn)",
"method_id":"ghtk_shipping_method",
"instance_id":"0",
"total":"22000",
"total_tax":"0",
"taxes":[</code>
],
"meta_data":[
{
"id":4046,
"key":"hubsid",
"value":"0"
}
]
}
],
"fee_lines":[
],
"coupon_lines":[
],
"refunds":[
]
}
Ref:
https://woocommerce.github.io/woocommerce-rest-api-docs/#order-properties
+ send full product detail over webhook: https://wpbeaches.com/send-full-product-details-over-woocommerce-webhook-action-hook/
+ cusstom json format webhook: https://stackoverflow.com/questions/49223236/custom-json-format-webhook-woocommerce-wordpress