Cách nhập JSON vào Google Sheets bằng Script ImportJSON cực dễ

Bạn đang muốn nhập dữ liệu JSON vào Google Trang tính nhưng không biết bắt đầu từ đâu? Bài viết này sẽ hướng dãn cách nhập JSON vào Google Sheets bằng script ImportJSON nhanh chóng, dễ hiểu và cực kỳ hiệu quả. Cùng tham khảo với GCS Việt Nam ngay nhé. 

JSON trong Google Sheets là gì?

JSON (JavaScript Object Notation) là một định dạng dữ liệu dạng văn bản, thường được dùng để trao đổi dữ liệu giữa máy chủ và ứng dụng web. Dữ liệu JSON được tổ chức thành các cặp “key”: “value”, tương tự như bảng tính có thể chứa nhiều lớp phức tạp. Ví dụ, một API thời tiết sẽ trả về JSON chứa các cặp như “city”: “Hanoi”, “temperature”: 30 và bên trong trường “forecast” lại là một danh sách các ngày dự báo khác.

Trong Google Trang tính, JSON không được hỗ trợ trực tiếp như các định dạng CSV hoặc TSV. Do đó, bạn không thể cần dán URL JSON vào Sheets và xem dữ liệu tự động hiển thị. Nhưng với Google Apps Script và các tiện ích mở rộng, bạn hoàn toàn có thể đưa dữ liệu JSON sang dạng bảng. 

Tuy nhiên, về mặt nguyên tắc thì Google Sheets chỉ làm việc hiệu quả với dữ liệu dạng bảng. Tức là dữ liệu có hàng và cột cố định. Chuỗi JSON lại là dữ liệu dạng cây hoặc phân cấp. Hàm ImportJSON() được phát triển là một Apps Script tùy chỉnh được viết bằng JavaScript. Nhiệm vụ chính là:

• Lấy chuỗi JSON: Truy cập URL API hoặc tệp JSON theo yêu cầu.
• Phân tích JSON: Đọc hiểu cấu trúc phức tạp của chuỗi JSON đó.
• Chuyển đổi: Lựa chọn các trường dữ liệu được chỉ định từ dạng cây sang dạng bảng (hàng và cột).
• Hiển thị: Đưa dữ liệu đã chuyển đổi vào các ô trong Google Sheets.

Tại sao cần nhập JSON vào Google Sheets?

Việc tự động hóa quá trình nhập dữ liệu JSON vào Google Sheets mang lại những lợi ích vượt trội trong quy trình làm việc và phân tích với dữ liệu như sau:

• Thứ nhất là khả năng cập nhật dữ liệu theo thời gian thực 

Hầu hết dữ liệu của Sheets đều là dữ liệu tĩnh, nghĩa là sau khi bạn nhập hoặc dán thì sẽ không bao giờ thay đổi trừ khi bạn tự tay cập nhật lại. Nếu bạn sử dụng hàm ImportJSON(), Sheets của bạn sẽ tự động đến API nguồn mỗi khi bảng tính được mở hoặc công thức được làm mới.

• Thứ hai là nâng cao hiệu quả bằng cách tinh gọn quy trình làm việc

Thông thường, bạn sẽ phải thực hiện các thao tác như truy cập API/website, sao chép chuỗi JSON, dùng công cụ bên ngoài để định dạng lại JSON, tự tay copy từng trường dữ liệu vào Sheets. Quá trình này tốn hàng giờ đồng hồ thực hiện. 

Tuy nhiên, với hàm ImportJSON, toàn bộ công việc được thực hiện chỉ bằng một dòng công thức đơn giản trong Sheets. Dữ liệu sau khi được nhập có thể ngay lập tức được sử dụng với các hàm như QUERY(), VLOOKUP() và được trực quan hóa bằng biểu đồ hoặc Google Data Studio.

• Thứ ba là giảm thiểu lỗi và tăng độ chính xác

Dữ liệu được API trả về thường là dữ liệu chuẩn, được định dạng nhất quán. Việc nhập thủ công luôn tiềm ẩn nguy cơ sai sót hoặc lỗi định dạng. Nhưng hàm ImportJSON lại đảm bảo dữ liệu được đưa vào Sheets một cách chính xác, đúng kiểu dữ liệu (số, văn bản, ngày tháng) theo quy tắc của JSON.

Hướng dẫn chi tiết cách nhập JSON vào Google Sheets

Nhập JSON vào Google Sheets như thế nào? Google Sheets thực chất không có sẵn hàm IMPORTJSON(). Do đó, chúng ta phải sử dụng một hàm tùy chỉnh được tạo ra bằng Google Apps Script. Đoạn mã JavaScript nhỏ chạy trong nền tảng sẽ cho phép bạn tạo ra các chức năng mới cho Sheets. Quá trình này hoàn toàn miễn phí và cực kỳ đơn giản theo ba bước sau.

Bước 1: Chuẩn bị tạo Google Sheets mới và mở Apps Script

Bước đầu tiên là bạn phải thiết lập một file Google Sheets mới bằng và truy cập vào Script bằng cách sau đây:

– Trước tiên, bạn cần gõ sheet.new vào thanh địa chỉ của phần trình duyệt. Thao tác này sẽ tự động tạo một bảng tính mới, không tên và bạn cần phải đặt tên cho file.

– Sau đó trên thanh công cụ menu của Google Sheets, bạn chọn Extensions (Tiện ích mở rộng) >> chọn Apps Script.

–  Lúc này, một tab mới sẽ mở ra, đây chính là Trình chỉnh sửa Script (IDE – Integrated Development Environment) của Google. Bạn sẽ thấy một file mặc định là Code.gs với một hàm giữ chỗ.

Bước 2: Cài đặt Script ImportJSON

Bước này, chúng ta sẽ cài đặt hàm ImportJSON() vào Google Sheets. Hàm này được phát triển bởi một lập trình viên tên là Paul Gambill và đã trở thành tiêu chuẩn vàng trong cộng đồng người dùng Sheets.

– Tiếp theo, bạn chỉ cần sao chép mã nguồn thông qua nguồn file import_json_appsscript.js rồi nhập từ khóa “paulgambill ImportJSON gist github”. Bạn chỉ cần Copy toàn bộ nội dung từ đầu đến cuối.

– Sau đó, bạn trở lại tab Apps Script (với file Code.gs đang mở). Bạn hãy xóa toàn bộ nội dung mặc định trong file Code.gs. Bạn hãy dán toàn bộ mã nguồn bạn vừa sao chép vào. Sau đó, bạn hãy nhấn vào biểu tượng Save project (Lưu dự án) hoặc sử dụng phím tắt Ctrl + S. Hệ thống có thể yêu cầu bạn đặt tên cho dự án và đặt tên là ImportJSON để dễ nhớ.

– Cuối cùng, bạn đã hoàn tất việc cài đặt hàm ImportJSON() đã sẵn sàng để được gọi trong bất kỳ ô nào của Google Sheets này. Sheets sẽ tự động yêu cầu quyền khi bạn chạy hàm lần đầu tiên trong Bước 3.

Bước 3: Cách sử dụng hàm ImportJSON() trong Google Sheets

Sau khi đã cài đặt script, việc sử dụng hàm trong Sheets cực kỳ đơn giản, giống như bạn đang dùng bất kỳ hàm có sẵn nào khác như SUM() hay IF().

*Cú pháp hàm cơ bản như sau:

 =ImportJSON(URL, path, options)

Trong đó:

• URL (Bắt buộc): Là địa chỉ API HTTP/HTTPS trả về dữ liệu JSON. Ví dụ: “https://api.example.com/data”. Lưu ý URL phải được đặt trong dấu ngoặc kép.
• path (Tùy chọn): Nó chỉ định chính xác bạn muốn lấy trường dữ liệu nào từ cấu trúc JSON lồng ghép. Nếu để trống hoặc dùng dấu /, nó sẽ cố gắng lấy tất cả dữ liệu. Ví dụ: Nếu JSON có cấu trúc {“data”: [{“name”: “A”}, {“name”: “B”}]}, bạn chỉ muốn lấy tên, bạn sẽ nhập: “/data/name”.
• options (Tùy chọn): Các tùy chọn nâng cao để tùy chỉnh cách hiển thị dữ liệu (cách xử lý Header, kiểu dữ liệu, v.v.) bao gồm:
  • “noHeaders”: Không hiển thị hàng tiêu đề (Tên cột).
  • “noTruncate”: Đảm bảo dữ liệu không bị cắt ngắn.
  • “raw”: Trả về chuỗi JSON thô thay vì cố gắng phân tích.

*Ví dụ minh họa thực tế: Giả sử bạn muốn lấy tỷ giá tiền tệ mới nhất từ một API giả định https://api.exchangerate-api.com/v4/latest/USD

Công thức là:

=ImportJSON(“https://api.exchangerate-api.com/v4/latest/USD”)

Kết quả trả về như hình:

Lỗi và cách xử lý khi nhập JSON vào Google Sheets

Khi sử dụng hàm ImportJSON Script, việc gặp các lỗi là điều không thể tránh khỏi. Dưới đây là một số nguyên nhân gây ra lỗi và cách khắc phục hiệu quả nhất:

Lỗi về quyền truy cập/xác thực

Lỗi này thường xuất hiện khi Scripts của bạn không được phép giao tiếp với API bên ngoài (URL API) hoặc không được phép chỉnh sửa Sheets.

Nguyên nhân gây ra lỗi là do:

• Chưa cấp quyền Script: Sau khi cài đặt script, bạn chưa thực hiện thao tác ủy quyền cho script. Google luôn yêu cầu người dùng cho phép các script bên ngoài chạy dưới tài khoản của họ.
• API yêu cầu xác thực: Nhiều API chuyên nghiệp yêu cầu bạn phải truyền kèm theo một Khóa API hoặc Token để chứng minh bạn là người dùng hợp lệ. Script ImportJSON cơ bản không thể tự động xử lý các xác thực phức tạp.
• Lỗi chia sẻ File JSON (File JSON trên Drive): Nếu bạn cố gắng nhập JSON từ một file trên Google Drive, file đó phải được đặt chế độ “Chia sẻ công khai” hoặc script của bạn cần được cấp quyền đọc rõ ràng.

Cách khắc phục lỗi này hiệu quả nhất như sau:

• Xử lý lỗi cấp quyền: Nếu thấy thông báo lỗi “Need Authorization” hoặc tương tự, bạn cần làm theo các bước sau: Mở lại Apps Script >> Chạy thủ công hàm ImportJSON một lần >> Làm theo các bước ủy quyền chi tiết (chọn Review Permissions >> Advanced >> Go to ImportJSON >> Allow).
• Xử lý API Key: Nếu API yêu cầu khóa, bạn phải truyền Key đó vào URL hoặc sử dụng phiên bản nâng cao của script ImportJSON (ví dụ: ImportJSONBasicAuth) hoặc sử dụng Add-ons. Cách đơn giản là chèn Key vào cuối URL: “…/data?apikey=YOUR_KEY_HERE”.
• Kiểm tra URL: Đảm bảo rằng URL bạn nhập là URL API (trả về JSON thô), không phải URL trang web HTML. Bạn hãy thử dán URL vào trình duyệt; nếu nó hiển thị một chuỗi ký tự JSON, đó là URL đúng.

Lỗi sai cú pháp (JSON format error)

Lỗi này xảy ra khi chuỗi mà script nhận được không phải là JSON hợp lệ hoặc không đúng cấu trúc mà script kỳ vọng.

Nguyên nhân của lỗi là do:

• Dữ liệu không phải JSON: URL bạn cung cấp trả về HTML hoặc một định dạng khác (ví dụ: CSV, XML) thay vì JSON. Script sẽ cố gắng phân tích và thất bại.
• Lỗi phân cấp: Tham số path (ví dụ: /data/items/price) bạn nhập vào không khớp với cấu trúc thực tế của JSON. Ví dụ, bạn dùng /items, nhưng dữ liệu thực tế là /results/items.
• JSON không chuẩn: Một số API trả về chuỗi JSON bị lỗi (thiếu dấu ngoặc kép, dấu phẩy,…) do lập trình viên của API đó.

Cách khắc phục lỗi sai cú pháp này như sau:

• Kiểm tra JSON và phân cấp: Sử dụng một công cụ JSON Validator/Formatter trực tuyến (ví dụ: JSONLint) để dán URL API vào và kiểm tra xem JSON có hợp lệ không.
• Sử dụng tùy chọn “noTruncate”: Đảm bảo rằng nếu dữ liệu bị quá dài, nó không bị cắt ngắn, gây lỗi phân tích.

Lỗi giới hạn dữ liệu (Data Volume/Sync Frequency)

Đây là lỗi liên quan đến giới hạn tài nguyên mà Google cấp phép cho Scripts của bạn chạy.

Nguyên nhân của lỗi này là do:

• Dữ liệu quá lớn (Timeout): Chuỗi JSON quá lớn (ví dụ: nhiều MB). Script ImportJSON có thể mất quá nhiều thời gian để xử lý, dẫn đến vượt quá giới hạn thời gian chạy của Google Apps Script (thường là 6 phút/lần chạy) và bị ngắt giữa chừng.
• Tần suất làm mới cao: Bạn mở Sheets quá thường xuyên hoặc có quá nhiều công thức được đặt để làm mới tự động.

Cách xử lý lỗi này hiệu quả như sau:

• Tối ưu hóa phân cấp: Thay vì ImportJSON toàn bộ dữ liệu, hãy chỉ trích xuất những trường cần thiết. 
• Giảm tần suất làm mới: Thay vì dùng ImportJSON(…) trực tiếp, hãy dùng ImportJSON(A1,…) và đặt ô A1 là =”https://api.example.com…”.
• Phân đoạn dữ liệu lớn: Nếu dữ liệu quá lớn, hãy tìm cách sử dụng các tham số (ví dụ: page=1, limit=100) của API để chỉ lấy một phần nhỏ dữ liệu mỗi lần.

Lời kết

Như vậy, bài viết đã hướng dẫn chi tiết, đầy đủ và dễ hiểu các bước để bạn có thể thành công nhập JSON vào Google Sheets. Nếu bạn đã thực hiện theo các bước trên nhưng vẫn gặp phải khó khăn, hãy liên hệ ngay với GCS Việt Nam để được tư vấn và hỗ trợ chuyên nghiệp nhất!

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777