Google Spreadsheets Advance

Hiện nay khi làm việc theo nhóm hoặc cần chia sẻ các dữ liệu thì bộ ứng dụng văn phòng của Google được đa số người dùng sử dụng. Các công cụ thường hay được sử dụng nhất đó là:

• Google Docs: Ứng dụng soạn thảo văn bản
• Google Sheets: Ứng dụng trang tính
• Google Slides: Ứng dụng trình chiếu
• Google Forms: Ứng dụng biểu mẫu
• Google Keep: Ứng dụng ghi chú

Một trong các điểm mạnh nhất của bộ ứng dụng này đó là có mặt trên tất cả các nền tảng phổ biến, cập nhận theo thời gian thực, có thể chia sẻ và phân quyền cho các người dùng khác để làm việc cũng như chia sẻ thông tin một cách nhanh chóng, tiện lợi. Trong đó Google Sheets hiện nay không chỉ sử dụng như một trang tính đơn thuần dành cho việc tính toán mà còn có thể sử dụng cho nhiều mục đích khác từ liệt kê các công việc và cập nhật status cho đến tạo schedule, plan, phân tích và thống kê…

Và để cho công việc được thuận lợi hơn, ngoài những tính năng phổ biến hay được sử dụng bài viết sẽ tập trung giới thiệu các cộng cụ cũng như tính năng nâng cao trong Google Sheets nói riêng cũng như các tính năng trong bộ ứng dụng này của Google.

Data Validation và Conditional Format

• Đây là hai tính năng hữu dụng nhất và được người dùng phổ biến nhất cho việc thống kê hoặc cập nhật trạng thái của một công việc hoặc nội dung liên quan.

• Với tính năng Data Validation, là tính năng xác thực dữ liệu nhập vào có thỏa mãn điều kiện đã được thiết lập từ trước hay không. Tính năng này sẽ đảm bảo giá trị nhập vào là chính xác với điều kiện cho trước. Chúng ta có thể lựa chọn bằng cách vào Data > Validation... Khi mở lên sẽ có một pop-up hiện ra với các thông số sau:

• Cell range: Số ô lựa chọn có data validation. Chúng ta có thể lựa chọn 1 ô dạng 'Tab_name'!Cell_number hoặc chọn nhiều ô theo dạng 'Tab_name'!Start_cell:End_cell

• Criteria: lựa chọn các giá trị để validation. Bao gồm các lựa chọn:

  • List from a range: Xác thực giá trị được nhập nằm trong danh sách từ một vùng các ô vd: 'Tab_name'!Start_cell:End_cell
  • List of items: Xác thực giá trị được nhập nằm trong danh sách từ các giá trị được nhập vào phân biệt bằng dấu phẩy
  • Number: Xác thực giá trị nằm trong vùng điều kiện các số được nhập vào(lớn hơn, nhỏ hơn, bằng, không bằng, trong khoảng...)
  • Text: Xác thực giá trị được nhập là chữ thỏa mãn điều kiện (bao gồm, không bao gồm, bằng, là email, là url)
  • Date: Xác thực giá trị được nhập là ngày tháng thỏa mãn điều kiện(là ngày tháng hợp lệ, bằng, trước, sau, nằm giữa, không nằm giữa...)

• On invalid data: Lựa chọn thông báo khi người dùng nhập giá trị không thỏa mãn điều kiện xác thực là Show warning(cảnh báo) hoặc Reject input(từ chối giá trị nhập)

• Appearance:

  • Lựa chọn nút trong ô để hiển thị danh sách với kiểu List from a range và List of items
  • Lựa chọn hiển thị thông báo trợ giúp khi user nhập giá trị

• Với Conditional Format, là tính năng dùng để định dang ô và dữ liệu theo điều kiện cho trước. Chúng ta có thể lựa chọn bằng cách vào Format>Conditional formatting... Sau khi lựa chọn sẽ mở lên panel như sau

Panel bao gồm 2 tab:

• Single Color:
  • Apply to range: Lựa chọn ô hoặc vùng ô muốn định dạng theo điều kiện
  • Format cells if...: Điều kiện định dạng ô
  • Formatting style: Kiểu định dạng cho ô
• Color scale: Sử dụng nhiều màu tùy theo giá trị nhập vào ở gần cận trên hay cận dưới mà màu của ô sẽ thay đổi

Sau khi lựa chọn điều kiện, chúng ta có thể lựa chọn thêm nhiều điều kiện khác cho cùng một ô hoặc một vùng.

Revision History

• Tính năng này sử dụng để xem lại lịch sử thay đổi dữ liệu và có thể quay ngược dữ liệu trở về thời điểm trước khi thay đổi. Tính năng này vô cùng hữu ích và tiện lơi khi trong làm việc nhóm muốn kiểm tra xem ai là người cập nhật dữ liệu hoặc dữ liệu bị sai và muốn sửa lại. Chúng ta có thể truy cập tính nằng bằng cách vào File> See Revision History

Filter

• Với những dữ liệu lớn, việc tìm kiếm thông thường trở nên rất khó khăn. Nhưng với tính năng filter của Google sheets, chúng ta sẽ dễ dàng tìm ra các thông tin mong muốn.

• Để filter dữ liệu chúng ta có hai cách:

  • Sử dụng filter mặc định: Lựa chọn vùng cần filter, sau đó chọn Data>Filter
  • Lựa chọn filter view: Chọn Data>Filter View

• Với cách sử dụng Filter View, chúng ta có thể lưu được filter với tên và filter tùy chọn. Chúng ta có thể truy cập filter đã lưu bằng cách vào Data>Filter View>Filter_Name

Apps Script

• Cũng giống như Marco của Excel, Apps Script sử dụng để viết các hàm tùy chỉnh theo từng mục đích sử dụng. Apps script sử dụng cú pháp giống như javascript nên không khó để có thể viết được, bạn chỉ cần một chút kiến thức về coding là có thể viết được các hàm một cách dễ dàng. Google đã cung cấp rất nhiều các class và method để có thể điều khiển được tất cả mọi thứ trong một spreadsheet. Đây là chi tiết các class mà Google cung cấp: https://developers.google.com/apps-script/reference/spreadsheet/sheet

Dưới đây là một vài ví dụ về apps script:

• Đầu tiên để tạo một apps script cho google sheet chúng ta chọn Tool> Script Editor

• Để tạo một menu trên tool bar, chúng ta sử dụng hàm onOpen() để tử động thêm menu vào trong toolbar khi spreadsheet được load:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Ẩn thông tin khách hàng', functionName: 'hideCustomer_'},
    {name: 'Hiện thông tin khách hàng', functionName: 'showCustomer_'},
    {name: 'Nhập Superstore', functionName: 'inputSuperstore_'}
  ];
  spreadsheet.addMenu('Công Cụ', menuItems);
}

function hideCustomer_() {
  //Ẩn cột 12 tới cột 22
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.hideColumns(12, 10);
}

function showCustomer_() {
  //Hiện cột 12 tới cột 22
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.showColumns(12, 10);
}

• Khi click vào menu chúng sẽ tự động gọi các hàm đã được đặt ở trong hàm trên. Khi sử dụng tên hàm với ký tự "" ở cuối để sử dụng cho menu và sẽ không show ở list debug. Lưu ý: nếu không có ký tự "" ở cuối hàm, sẽ không thể chạy được bằng cách gọi ở toolbar.

• Khi lần đầu tiên chạy apps script, sẽ có một confirmation pop-up để gán quyền cho apps script chạy trong spreadsheet

• Thêm nữa, apps script còn có thể tạo một pop-up nhập liệu trực tiếp trên spreadsheet

//Lấy order id list
function getOrderList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Returns');
  var values = sheet.getRange(2, 1, sheet.getLastRow() - 1);
  var result = new Array(values.getLastColumn() - 1);
  for(i =0; i < 10; i++) {
    result[i] = values.getCell(i + 1, 1).getValue();
  }
  return result;
}

//Hàm gọi pop-up nhập superstore
function inputSuperstore_() {
  var html = doGet();
  SpreadsheetApp.getUi().showModalDialog(html, 'Enter Superstore');
}

//Hàm tạo form nhập dữ liệu
function doGet() {
  var result = HtmlService
      .createTemplateFromFile('Form')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  result.setHeight(520);
  result.setWidth(520)
  return result;
}

//Hàm lấy dữ liêu từ form trả về và điền vào spreadsheet
function getValuesFromForm(form){

  var row_id = form.row_id,
      order_id = form.order_id,
      order_date = form.order_date,
      order_priority = form.order_priority,
      order_quantity = form.order_quantity,
      sales = form.sales,
      discount = form.discount,
      ship_mode = form.ship_mode,
      profit = form.profit,
      unit_price = form.unit_price,
      shipping_cost = form.shipping_cost,
      sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');
  var lastRow = sheet.getLastRow();
  //Nhập dữ liệu vào trong spreadsheet
  sheet.getRange(lastRow + 1, 1).setValue(row_id);
  sheet.getRange(lastRow + 1, 2).setValue(order_id);
  sheet.getRange(lastRow + 1, 3).setValue(order_date);
  sheet.getRange(lastRow + 1, 4).setValue(order_priority);
  sheet.getRange(lastRow + 1, 5).setValue(order_quantity);
  sheet.getRange(lastRow + 1, 6).setValue(sales);
  sheet.getRange(lastRow + 1, 7).setValue(discount);
  sheet.getRange(lastRow + 1, 8).setValue(ship_mode);
  sheet.getRange(lastRow + 1, 9).setValue(profit);
  sheet.getRange(lastRow + 1, 10).setValue(unit_price);
  sheet.getRange(lastRow + 1, 11).setValue(shipping_cost);
}

<script type="text/javascript">
   function formSubmit() {
      google.script.run.getValuesFromForm(document.forms[0]);
      google.script.host.close();
   }
</script>
<style>
input.i01 {
    margin-left: 60px;
}
input.i02 {
    margin-left: 67px;
}
input.i03 {
    margin-left: 44px;
}
input.i04 {
    margin-left: 30px;
}
input.i05 {
    margin-left: 24px;
}
input.i06 {
    margin-left: 180px;
}
input.i07 {
    margin-left: 88px;
}
input.i08 {
    margin-left: 64px;
}
input.i09 {
    margin-left: 85px;
}
input.i10 {
    margin-left: 51px;
}
input.i11 {
    margin-left: 56px;
}
</style>

 
<div id="logindiv">
<form method="POST">
<label>Row ID: </label>
<input class="i02" type="number" name="row_id" placeholder="Row ID"/><br/>
<br/>
<label>Order ID: </label>
<input class="i01" name="order_id" list="hosting-plan" type="text" />
<datalist id="hosting-plan">
    <? var data = getOrderList(); ?>
    <? for (var i = 0; i < data.length; i++) { ?>
    <option value="<?= data[i]?>"></option>
    <? } ?>
</datalist>
<br/>
<br/>
<label>Order Date : </label>
<input class="i03" type="date" name="order_date" placeholder="Order Date"/><br/>
<br/>
<label>Order Priority: </label>
<input type="radio" name="order_priority" value="Critical"> Critical
<input type="radio" name="order_priority" value="High"> High
<input type="radio" name="order_priority" value="Medium" checked> Medium
<input type="radio" name="order_priority" value="Low"> Low
<input type="radio" name="order_priority" value="Not Specified"> Not Specified
<br/>
<br/>
<label>Order Quantity: </label>
<input class="i05" type="number" name="order_quantity" placeholder="Order Quantity"/><br/>
<br/>
<label>Sales: </label>
<input class="i07" type="number" name="sales" placeholder="Sales"/><br/>
<br/>
<label>Discount: </label>
<input class="i08" type="number" name="discount" placeholder="Discount"/><br/>
<br/>
<label>Ship Mode: </label>
<input class="i10" name="ship_mode" list="ship_mode" type="text" /><datalist id="ship_mode">
   <option value="Delivery Truck"></option>
   <option value="Regular Air"></option>
   <option value="Express Air"></option>
</datalist>
<br/>
<br/>
<label>Profit: </label>
<input class="i09" type="number" name="profit" placeholder="Profit"/><br/>
<br/>
<label>Unit Price: </label>
<input class="i11" type="number" name="unit_price" placeholder="Unit Price"/><br/>
<br/>
<label>Shipping Cost: </label>
<input class="i04" type="number" name="shipping_cost" placeholder="Shipping Cost"/><br/>
<br/>
<br/>
<br/>
<input class="i06" onclick="formSubmit()" type="button" value="Add Row" />
<input onclick="google.script.host.close()" type="button" value="Exit" />
<br/>
</form>

• Trong đoạn code trên, Khi bấm vào menu "Nhập Superstore", sẽ gọi tới hàm inputSuperstore_() để mở pop-up "Enter Superstore". Pop-up này được khởi tạo bởi hàm doGet() và lấy layout từ file Form.html. Sau khi user nhập xong Form và submit, form sẽ gọi hàm getValuesFromForm(form) để lấy dữ liệu từ form và nhập vào trong spreadsheet.

Các bạn có thể tham khảo chi tiết ở link sau: https://goo.gl/HgI8tr

Add-ons

• Ngoài Apps Script, chúng ta có thể dùng sẵn các chức năng từ bên thứ ba trong Add-ons. Trong đó bao gồm rất nhiều công cụ đã được đưa lên store và hoàn toàn miễn phí.

• Chúng ta có thể lựa chọn một add-on và thêm nó vào trong spreadsheet. Lưu ý là add-on sẽ được add theo tài khoản chứ không phải theo từng sheet, nếu bạn đã add rồi thì không cần phải add lại nữa.

• Sau đó vào Add-ons>Add-ons_Name để truy xuất vào add-on và sử dụng

Tham khảo

• https://developers.google.com/apps-script/reference/spreadsheet/sheet
• https://developers.google.com/apps-script/guides/sheets/functions#getting_started