Hướng dẫn cách tạo trang chỉ mục siêu liên kết vào Goolge Sheets

Sẽ rất mất thời gian nếu bạn cần tìm một trang cụ thể bên trong bảng tính Google Sheets. Trong trường hợp này việc tạo chỉ mục siêu liên kết là vô cùng hữu ích và dưới đây là cách thực hiện.

Dưới đây Gitiho sẽ hướng dẫn các bạn sử dụng Apps Script để tạo trang chỉ mục siêu liên kết ở đầu sổ làm việc của bạn.

Dưới đây là hình ảnh minh họa rõ nhất về cách mà chỉ mục siêu liên kết sẽ làm việc trong các bảng dữ liệu Goolge Sheets khổng lồ. 

Vậy điều gì sẽ xảy ra nếu chúng ta đã có một trang tính có tên là chỉ mục Indext. Trong trường hợp này, Goolge Sheets sẽ yêu cầu người dùng nhập một tên chỉ mục khác hoặc hủy thao tác, giống như hình ảnh dưới đây.

Các bước để tạo trang chỉ mục siêu liên kết riêng trong Google Sheets

Bước 1: Mở bảng dữ liệu Google Sheets bạn muốn thêm trang chỉ mục vào và mở trình chỉnh sửa tập lệnh ứng dụng Script Editor (Tools > Script editor...). 

Bước 2: Xóa tất cả bảng mã myFunction soạn sẵn rồi copy và dán vào khung trống các lệnh như dưới đây: 

function onOpen() {
  
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Index Menu')
      .addItem('Create Index', 'createIndex')
      .addItem('Update Index', 'updateIndex')
      .addToUi();
}


// function to create the index
function createIndex() {
  
  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  var namesArray = sheetNamesIds(sheets);
  
  var indexSheetNames = namesArray[0];
  var indexSheetIds = namesArray[1];
  
  // check if sheet called sheet called already exists
  // if no index sheet exists, create one
  if (ss.getSheetByName('index') == null) {
    
    var indexSheet = ss.insertSheet('Index',0);
    
  }
  // if sheet called index does exist, prompt user for a different name or option to cancel
  else {
    
    var indexNewName = Browser.inputBox('The name Index is already being used, please choose a different name:', 'Please choose another name', Browser.Buttons.OK_CANCEL);
    
    if (indexNewName != 'cancel') {
      var indexSheet = ss.insertSheet(indexNewName,0);
    }
    else {
      Browser.msgBox('No index sheet created');
    }
    
  }
  
  // add sheet title, sheet names and hyperlink formulas
  if (indexSheet) {
    
    printIndex(indexSheet,indexSheetNames,indexSheetIds);

  }
    
}



// function to update the index, assumes index is the first sheet in the workbook
function updateIndex() {
  
  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var indexSheet = sheets[0];
  
  var namesArray = sheetNamesIds(sheets);
  
  var indexSheetNames = namesArray[0];
  var indexSheetIds = namesArray[1];
  
  printIndex(indexSheet,indexSheetNames,indexSheetIds);
}


// function to print out the index
function printIndex(sheet,names,formulas) {
  
  sheet.clearContents();
  
  sheet.getRange(1,1).setValue('Workbook Index').setFontWeight('bold');
  sheet.getRange(3,1,names.length,1).setValues(names);
  sheet.getRange(3,2,formulas.length,1).setFormulas(formulas);
  
}


// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {
  
  var indexSheetNames = [];
  var indexSheetIds = [];
  
  // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    indexSheetNames.push([sheet.getSheetName()]);
    indexSheetIds.push(['=hyperlink("#gid=' 
                        + sheet.getSheetId() 
                        + '","' 
                        + sheet.getSheetName() 
                        + '")']);
  });
  
  return [indexSheetNames, indexSheetIds];
  
}

Bước 3: Nhấn Save và đặt tên mới cho dự án tập lệnh của bạn là Index.

Bước 4: Chọn hàm onOpen trên thanh công cụ (1) và nhấn nút chạy Play (2)

Bước 5: Trong hộp thoại mới hiển thị, hãy cấp các quyền cần thiết cho tập lệnh ứng dụng (quy trình một lần):

Bước 6: Điều hướng trở lại trang tính Google Sheets của bạn và sử dụng Menu chỉ mục mới được tạo để chạy chương trình cũng như tạo chỉ mục siêu liên kết của bạn, giống như hình ảnh dưới đây.

Lưu ý: Ở một số quốc gia Châu Âu (và có thể ở những nơi khác), bạn sử dụng dấu chấm phẩy “;” trong công thức thay cho dấu phẩy “,”. Trong trường hợp này, bạn sẽ gặp lỗi với các công thức siêu liên kết trong ví dụ này nhưng đây là cách khắc phục rất dễ dàng. Chỉ cần thay đổi “,” thành “;” trên dòng 96 là xong.

Tạo danh sách chỉ mục siêu liên kết trong thanh Sidebar 

Thay vì tạo danh sách chỉ mục siêu liên kết của một trang tính Sheets mới ở đầu, bạn có thể tạo danh sách chỉ mục trong thanh bên Sidebar để có thể xem từ bất kỳ tab nào trong Google Sheets. 

Trong trường hợp này, mã sẽ phức tạp hơn một chút vì chúng ta có thanh bên HTML mà chúng ta cần chuyển tên trang tính và ID vào. Tiếc nhất là phiên bản thanh bên không thể mở liên kết (tức là tab bạn muốn điều hướng đến) trong tab trình duyệt mới.

Trong trình chỉnh sửa tập lệnh Script Editor của Goolge Sheets bạn sẽ cần có hai tệp sau, một tệp là GS, tệp kia là tệp HTML:

code.gs
sidebar.html

Mã trong tệp code.gs là:

/**
 * menu
 */
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Sidebar Menu')
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}

/**
 * show sidebar
 */
function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('sidebar.html')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Index Sidebar');
  
  SpreadsheetApp.getUi().showSidebar(ui);
}


/**
 * get the sheet names
 */
function getSheetNames() {
  
  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  return sheetNamesIds(sheets);
}


// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {
  
  var indexOfSheets = [];
  
  // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    indexOfSheets.push([sheet.getSheetName(),sheet.getSheetId()]);
  });
  
  //Logger.log(indexOfSheets);
  return indexOfSheets;
  
}

Sau đó, mã trong tệp sidebar.html là:

Index of all sheets in this workbook:

Và dưới đây là hình ảnh minh họa cho tập lệnh chỉ mục thanh bên đang hoạt động:

Trên đây Gitiho vừa giới thiệu đến các bạn bài Hướng dẫn cách tạo trang chỉ mục siêu liên kết vào Goolge Sheets. Hi vọng rằng những bạn đang phải làm việc với những bảng dữ liệu Google Sheets có dữ liệu lớn sẽ biết cách để tạo các chỉ mục siêu liên kết, giúp quản lý công việc trở nên đơn giản và dễ dàng hơn.

CÓ THỂ BẠN CŨNG QUAN TÂM

Hướng dẫn tạo danh sách tùy chọn trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets