排骨干货节 篇二：学会这波Excel操作，海淘省下一个亿！汇率计算，店家比价一键搞定！

一. 前言

本文关键字: 亚马逊, 海淘, 爬网, 比价, 汇率, Excel, VBA, Google Sheets, JavaScript, HTML, HTTP.
本文虽然不是基础教学, 但是纯靠照抄文中步骤复制文中代码也能达到最终效果.

技术不拘泥于形式, 购物不限制于手段. 自觉懂一丢丢技术, 会点皮毛编程, 买过几回洋货的排骨, 这次给大家分享一下几年前疯狂海淘的极客经验. 如何只用一个在线表格就搞定亚马逊商品的价格监控与比价.

本文用例: LEGO Creator Mighty Dinosaurs 31058 (乐高凶猛霸王龙 3合1).

各国亚马逊的多个店家都有卖, 价格天天在变, 到底该何时出手?

二. 需求分析

想要知道 Amazon 上哪里买乐高 31058最便宜? Amazon 好多国家有卖, 好多第三方店家有卖, 到底哪一家最便宜? 如果使用 Google Sheets 简便直观的看到价格差别?

排骨将需求细分成下面几个, 以便分析出所需的技术基础和清晰的实现思路.

1. 自动获取商品的价格

每次都要打开商品页面去看价格也太麻烦了.

我们需要直接看到商品的当前价格.

2. 自动获取商品在不同店家的价格

有些商品 Amazon 自营会缺;
有些商品 Amazon 自营不能直邮而第三方店家可以;
有些商品 Amazon 自营运费比第三方店家贵;
有些商品只有第三方店家有卖;

所以视情况留意一些价格和信誉都不错的第三方店家价格是个好主意.

我们需要直接看到不同店家的价格.

3. 自动获取商品在各国 Amazon 的价格

当一个商品在各国 Amazon 都在卖时, 海淘老手是不会放过各国之间的差价的.

我们需要直接看到各国 Amazon (自营或第三方) 的当地货币价格.

4. 自动获取当天汇率

最近人民币跌得很快, 汇率的每日变化对海淘买家来说是很敏感的. 汇率直接影响着海淘的购物成本.

我们需要直接看到各国 Amazon 当地货币的当日汇率.

5. 自动将商品价格转率成人民币

如果有了它国当地货币价格和当日汇率, 就可以直接算出对应的人民币价格来比较差价了.

我们需要直接看到商品在各国各商家的对应人民币价格.

6. 按人民币价格生成条形图表

如果得到了商品在各国各店家的对应人民币价格, 生成一张图表不是能更直观的看出最低价, 最高价和各自差价了吗?

我们需要通过图表直观的看到各个实时(伪)价格的差距.

点我查看最终效果

三. 技术基础

技术宅拯救海淘, 当然需要以技术为前提条件.

缺少以下技术知识基础的同学, 可能无法完整的理解本文的全部内容.

1. Excel 表格使用基础

Google 近些年在云办公方面领跑, 从当初的 Google Docs 到现在的 Google Apps, 逼得微软紧赶慢赶推出了 Office 365.

本文使用的 Google Sheets 几乎就是的 Excel 的网页版, 兼容大多数常用的 Excel 函数. 免费使用免费存储又能免费分享. 在家做表格, 到公司给老板看, 到外面给客户看, 免U盘免本本...... 扯远了.

所以! 本文需要实现的所有功能和操作都在 Google Sheets 里, 会用 Excel 是基础中的基础.

访问不了 Google Sheets? 不论是海淘老手还是技术老手都不会有这种问题.

2. HTTP/HTML基础

商品的价格显示在 Amazon 的商品页面中, 我们向 Google Sheets 表格提供商品的连接, Google Sheets 会获取 Amazon 的商品页面, 从中析出商品的价格, 标题等数据.

上图发送 URL 和回复 HTML 就是 HTTP 协议的请求和回应.

浏览器接收到 HTTP 服务器回复的 HTML 源码后, 再将渲染结果显示给用户. 我们需要从 HTML 源码中解析出商品价格.

使用 Chrome 可以轻松找到商品价格的源码位置.

3. JavaScript 基础

20年来人气一路飙升的 JavaScript, 这次被集成到 Google Sheets 当中, 代替老旧的 VBA 成为其扩展脚本语言. 这样任何一个 Web 前端开发者都可以给 Google Sheets 表格开发扩展功能, 还免学 VBA 这种...唉...

BTW: 目前 Excel 也已经开始支持使用 JavaScript 编写扩展功能.

本文内容虽然是 Google Sheets + JavaScript, 但是完全可以在 Excel + VBA 中完成.

四. 初级实现

以乐高 31058 恐龙3合1 这个商品为例, 我们一步步来"比价"吧.

1. 收集各国各店家的商品连接 URL.

我们首先需要拿到各种价格的 URL, 粘贴 Google Sheets 里. 然后才能获取得价格信息.

① 查询商品, 获取连接

我们先在美亚搜索 "lego 31058" 时, 会找到该商品的页面连接 (见上图红框), 可以得到一个格式如: https://www.amazon.com/xxx/dp/B01KJEOCDW/xxx 的 URL.

② 获取商品对付各店家的 URL

使用上一步获得的 URL 打开商品页面, 根据你默认收货地址是否能直邮到, 你可能遇到以下三种情况

美亚自营可以直邮, 商品页面显示美亚自营价格.

美亚自营不能直邮, 自动选取可以直邮且价格最低的第三方店家.

如上图: 美亚自营不能直邮 乐高 31059 到中国, 系统默认显示成了第三方店家 Brick Dudes 的价格.

美亚自营和第三方都不能直邮时, 商品页面不显示价格, 需要手动选择从哪个店家购买.

通过以上三种情况可知, 搜索结果里的商品连接并不确定是自营还是哪一个第三方店家, 我们需要从 "See All Buying Options" 找到需要的店家.

在这里我们可以把需要比价的店家和美亚自营都加入购物车.

如上图打开购物车, 红框里商品名称的连接就是对应各店家的商品页面 URL 了.

Amazon US: https://www.amazon.com/gp/product/B01KJEOCDW/ref=ox_sc_act_title_1?smid=ATVPDKIKX0DER&psc=1
Buy 4 Less Shop: https://www.amazon.com/gp/product/B01KJEOCDW/ref=ox_sc_act_title_1?smid=A244NFQODTIILZ&psc=1
ToyWiz: https://www.amazon.com/gp/product/B01KJEOCDW/ref=ox_sc_act_title_1?smid=AJF0HB30PBJAV&psc=1

2. 创建 Google Sheets 表格

上一步收集的 URL, 就是我们需要的全部输入数据. 可以建表了.

① 进入sheets.google.com, 新建议一个空白表格: LEGO 31058 Price Match.

将前面收集到的店家名称和 URL 填入表格. 准备好格子显示价格. 和平时用 Excel 一毛一样. 排骨就不啰嗦了. 下面来说点不一样的.

② 打开 Tools -> Script editor, 创建 JavaScript 脚本.

上图就是脚本编辑器 Script editor 了.

③ 在表格里测试脚本函数.

我们先在脚本编辑器写一个 getOne 函数, 然后在 C2 中调用, 会得到数值 1.

3. 截取商品价格

① 获取商品页 HTML 源码.

这次我们写一个 getAmazonInfo 函数, 根据输入的 url 获取 HTML, 然后返回 HTML 在的尺寸.

Google Sheets 的脚本中发送 HTTP 请求需要用到 Google Apps Script 内置的 UrlFetchApp 类.

我们将 B2 的 URL 传给 getAmazonInfo 函数, 返回的结果显示在 C2中.

第一次执行时, 会提示需要你授权.

② 定位商品价格

上一次我们已经取到了商品页面的 HTML 源码, 而我们真正需要的是源码中的价格信息. 如何在字符数上万的 HTML 源码里找到商品的价格信息呢?

用 Chrome 打开商品页面, 鼠标右键点价格, 再选择检查 (Inspect), Chrome 的开发工具会跳出来, 定位到商品的价格标签位置.

可以看到 $11.99 这个价格信息被包在 这个标签里.
我们只需要从上一步得到的 HTML 源码中找到这个标签, 就能解析出商品的价格了.

③ 解析并取得价格信息

前两步我们得到了 HTML 源码, 也找到了包含价格的 HTML 标签, 那么如果取到价格呢?

我们将上述标签分析一下:
价格信息在 '>' index2 和 '<' index3 两个符号中间. 找到 index2 就能顺便找到 index3, 然后就可以拿到价格了.
HTML 中有上千个 '>' 和 '<' 字符, 所以无法直接找到 index2/index3的位置.
index2 左边的 '' index1 是 HTML 源码里唯一的, 可以直接定位到.
所以我们先找 index1, 再顺到 index2/index3, 就能取得价格了.

只需要用 indexOf 和 substring 这两个函数就能轻松截取价格了.

④ 价格是空的?

我们上面写的脚本很可能出现一个问题, 就是取不到价格.

真相是因为美亚网页认为我们写的脚本是机器人, 返回了一个验证网页的 HTML, 需要我的填验证码才能继续.

上图就是验证页面的 HTML.

排骨想到了一个简单粗暴的解决方案: 如果出现验证要求就刷新重载 URL.

不能看出验证页面有一行 'api-services-support@amazon.com', 而商品页面里没有. 只要检查一下这行代码有没有出现, 就能知道返回的 HTML 是不是验证页面. 如果是验证页, 就过半秒重载一次 URL 再取 HTML, 只到取得真正的商品页面 HTML.

这样价格就都出来了 :)

至此, 一个简单的亚马逊商品价格监控表就完成了.

六. 小结

上篇先写到这里, 初级小目标已经达到了. 我们已经创造出来一个像三骆驼 (camelcamelcamel.com) 一样的价格监控功能了.

下篇排骨将继续深化扩展这个表格, 将转汇与图表等功能完成, 从价格监控表变成价格比较表.

谢谢支持!