Hướng dẫn google sheets combine functions - google sheet kết hợp các chức năng

Google Sheets cung cấp hàng trăm chức năng tích hợp như AVERAGE, SUM và VLOOKUP. Khi những aren này đủ cho nhu cầu của bạn, bạn có thể sử dụng tập lệnh Google Apps để viết các chức năng tùy chỉnh-giả sử, để chuyển đổi mét thành dặm hoặc tìm nạp nội dung trực tiếp từ Internet-sau đó sử dụng chúng trong Google Sheets giống như một hàm tích hợp.

Bắt đầu

Các chức năng tùy chỉnh được tạo bằng cách sử dụng JavaScript tiêu chuẩn. Nếu bạn chưa quen với JavaScript, Codecademy cung cấp một khóa học tuyệt vời cho người mới bắt đầu. (Lưu ý: Khóa học này không được phát triển bởi và không liên kết với Google.)

Dưới đây là một chức năng tùy chỉnh đơn giản, có tên DOUBLE, nhân lên giá trị đầu vào với 2:

function DOUBLE(input) {
  return input * 2;
}

Nếu bạn không biết cách viết JavaScript và không có thời gian để tìm hiểu, hãy kiểm tra cửa hàng bổ trợ để xem liệu người khác đã xây dựng chức năng tùy chỉnh bạn cần.

Tạo một chức năng tùy chỉnh

Để viết một chức năng tùy chỉnh:

1. Tạo hoặc mở một bảng tính trong Google Sheets.
2. Chọn phần mở rộng mục menu> Tập lệnh ứng dụng.Extensions > Apps Script.
3. Xóa bất kỳ mã nào trong trình soạn thảo tập lệnh. Đối với hàm DOUBLE ở trên, chỉ cần sao chép và dán mã vào trình chỉnh sửa tập lệnh.
4. Ở đầu, nhấp vào Lưu.

Bây giờ bạn có thể sử dụng chức năng tùy chỉnh.

Nhận một chức năng tùy chỉnh từ thị trường không gian làm việc của Google

Thị trường không gian làm việc của Google cung cấp một số chức năng tùy chỉnh dưới dạng tiện ích bổ sung cho Google Sheets. Để sử dụng hoặc khám phá các tiện ích bổ sung này:

1. Tạo hoặc mở một bảng tính trong Google Sheets.
2. Chọn phần mở rộng mục menu> Tập lệnh ứng dụng.Add-ons > Get add-ons.
3. Xóa bất kỳ mã nào trong trình soạn thảo tập lệnh. Đối với hàm DOUBLE ở trên, chỉ cần sao chép và dán mã vào trình chỉnh sửa tập lệnh.
4. Ở đầu, nhấp vào Lưu.
5. Bây giờ bạn có thể sử dụng chức năng tùy chỉnh.Install to install it.
6. Nhận một chức năng tùy chỉnh từ thị trường không gian làm việc của GoogleAllow.
7. Thị trường không gian làm việc của Google cung cấp một số chức năng tùy chỉnh dưới dạng tiện ích bổ sung cho Google Sheets. Để sử dụng hoặc khám phá các tiện ích bổ sung này:Add-ons > Manage add-ons. Find the add-on you want to use and click Options > Use in this document.

Ở trên cùng, nhấp vào tiện ích bổ sung> Nhận tiện ích bổ sung.

Khi thị trường không gian làm việc của Google mở ra, hãy nhấp vào hộp tìm kiếm ở góc trên cùng bên phải.

1. Nhập "Hàm tùy chỉnh" và nhấn Enter.
2. Nếu bạn tìm thấy một tiện ích bổ sung chức năng tùy chỉnh mà bạn quan tâm, hãy nhấp vào Cài đặt để cài đặt nó.
3. Một hộp thoại có thể cho bạn biết rằng tiện ích bổ sung yêu cầu ủy quyền. Nếu vậy, hãy đọc thông báo cẩn thận, sau đó nhấp vào cho phép.

Các tiện ích bổ sung trở nên có sẵn trong bảng tính. Để sử dụng tiện ích bổ sung trong một bảng tính khác, hãy mở bảng tính khác và ở trên cùng, nhấp vào add-ons> quản lý các tiện ích bổ sung. Tìm tiện ích bổ sung bạn muốn sử dụng và nhấp vào Tùy chọn> Sử dụng trong tài liệu này.

Sử dụng một chức năng tùy chỉnh

Khi bạn đã viết một chức năng tùy chỉnh hoặc cài đặt một chức năng từ thị trường không gian làm việc của Google, nó dễ sử dụng như một hàm tích hợp:

Nhấp vào ô nơi bạn muốn sử dụng chức năng.

• Nhập một dấu bằng (=) theo sau là tên hàm và bất kỳ giá trị đầu vào nào - ví dụ:

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  0 - và nhấn Enter.
• Tế bào sẽ hiển thị trong giây lát

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  1, sau đó trả về kết quả.
• Hướng dẫn cho các chức năng tùy chỉnh
• Trước khi viết chức năng tùy chỉnh của riêng bạn, có một vài hướng dẫn để biết.

Đặt tên

Ngoài các quy ước tiêu chuẩn để đặt tên cho các hàm JavaScript, hãy lưu ý về những điều sau đây:

• Tên của một hàm tùy chỉnh phải khác biệt với tên của các hàm tích hợp như

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  2.

• Tên của một hàm tùy chỉnh không thể kết thúc bằng dấu gạch dưới (

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  3), biểu thị một hàm riêng trong tập lệnh ứng dụng.
  
  

• Tên của một hàm tùy chỉnh phải được khai báo với cú pháp

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  4, không phải

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  5.

Vốn hóa không quan trọng, mặc dù tên của các chức năng bảng tính là truyền thống theo truyền thống.

Tranh luận

• Giống như hàm tích hợp, chức năng tùy chỉnh có thể lấy các đối số làm giá trị đầu vào:
• Nếu bạn gọi chức năng của mình có tham chiếu đến một ô như một đối số (như

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  0), đối số sẽ là giá trị của ô.
• Nếu bạn gọi chức năng của mình với một tham chiếu đến một loạt các ô như một đối số (như

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  7), đối số sẽ là một mảng hai chiều của các giá trị của các ô. Ví dụ, trong ảnh chụp màn hình bên dưới, các đối số trong

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  8 được giải thích bằng tập lệnh ứng dụng là

  /**
   * Multiplies the input value by 2.
   *
   * @param {number} input The value to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return input * 2;
  }
  

  9. Lưu ý rằng mã mẫu cho DOUBLE từ trên cao sẽ cần được sửa đổi để chấp nhận một mảng làm đầu vào.
• Một cuộc gọi chức năng tùy chỉnh phải trả về trong vòng 30 giây. Nếu không, ô sẽ hiển thị lỗi:

  /**
   * Multiplies the input value by 2.
   *
   * @param {number|Array>} input The value or range of cells
   *     to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return Array.isArray(input) ?
        input.map(row => row.map(cell => cell * 2)) :
        input * 2;
  }
  

  4

Loại dữ liệu

Google Sheets lưu trữ dữ liệu ở các định dạng khác nhau tùy thuộc vào bản chất của dữ liệu. Khi các giá trị này được sử dụng trong các hàm tùy chỉnh, tập lệnh ứng dụng sẽ coi chúng là loại dữ liệu phù hợp trong JavaScript. Đây là những lĩnh vực phổ biến nhất của sự nhầm lẫn:

• Thời gian và ngày trong tờ trở thành đối tượng ngày trong tập lệnh ứng dụng. Nếu bảng tính và tập lệnh sử dụng các múi giờ khác nhau (một vấn đề hiếm gặp), chức năng tùy chỉnh sẽ cần phải bù.
• Giá trị thời lượng trong các bảng cũng trở thành đối tượng

  /**
   * Multiplies the input value by 2.
   *
   * @param {number|Array>} input The value or range of cells
   *     to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return Array.isArray(input) ?
        input.map(row => row.map(cell => cell * 2)) :
        input * 2;
  }
  

  5, nhưng làm việc với chúng có thể phức tạp.
• Giá trị tỷ lệ phần trăm trong các bảng trở thành số thập phân trong tập lệnh ứng dụng. Ví dụ: một ô có giá trị

  /**
   * Multiplies the input value by 2.
   *
   * @param {number|Array>} input The value or range of cells
   *     to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return Array.isArray(input) ?
        input.map(row => row.map(cell => cell * 2)) :
        input * 2;
  }
  

  6 trở thành

  /**
   * Multiplies the input value by 2.
   *
   * @param {number|Array>} input The value or range of cells
   *     to multiply.
   * @return The input multiplied by 2.
   * @customfunction
   */
  function DOUBLE(input) {
    return Array.isArray(input) ?
        input.map(row => row.map(cell => cell * 2)) :
        input * 2;
  }
  

  7 trong tập lệnh ứng dụng.

Tự động hoàn thành

Google Sheets hỗ trợ AutoComplete cho các chức năng tùy chỉnh giống như các chức năng tích hợp. Khi bạn nhập tên hàm trong ô, bạn sẽ thấy một danh sách các chức năng tích hợp và tùy chỉnh phù hợp với những gì bạn nhập.

Các chức năng tùy chỉnh sẽ xuất hiện trong danh sách này nếu tập lệnh của chúng bao gồm thẻ JSDOC

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Nâng cao

Sử dụng các dịch vụ tập lệnh ứng dụng

Các chức năng tùy chỉnh có thể gọi một số dịch vụ tập lệnh ứng dụng để thực hiện các tác vụ phức tạp hơn. Ví dụ, một hàm tùy chỉnh có thể gọi dịch vụ ngôn ngữ để dịch một cụm từ tiếng Anh sang tiếng Tây Ban Nha.

Không giống như hầu hết các loại tập lệnh ứng dụng khác, các chức năng tùy chỉnh không bao giờ yêu cầu người dùng ủy quyền truy cập vào dữ liệu cá nhân. Do đó, họ chỉ có thể gọi các dịch vụ không có quyền truy cập vào dữ liệu cá nhân, cụ thể là sau:

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Bảng tính

Chỉ đọc (có thể sử dụng hầu hết các phương thức

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

URL tìm nạp

Tiện ích

• XMLExtensions > Apps Script to open the script editor, then copy the script text from the original spreadsheet and paste it into the script editor of another spreadsheet.
• Nếu chức năng tùy chỉnh của bạn ném thông báo lỗi

  /**
   * Show the title and date for the first page of posts on the
   * Developer blog.
   *
   * @return Two columns of data representing posts on the
   *     Developer blog.
   * @customfunction
   */
  function getBlogPosts() {
    var array = [];
    var url = 'https://gsuite-developers.googleblog.com/atom.xml';
    var xml = UrlFetchApp.fetch(url).getContentText();
    var document = XmlService.parse(xml);
    var root = document.getRootElement();
    var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
    var entries = document.getRootElement().getChildren('entry', atom);
    for (var i = 0; i < entries.length; i++) {
      var title = entries[i].getChild('title', atom).getText();
      var date = entries[i].getChild('published', atom).getValue();
      array.push([title, date]);
    }
    return array;
  }
  

  5, dịch vụ yêu cầu ủy quyền người dùng và do đó không thể được sử dụng trong một hàm tùy chỉnh.File > Make a copy. When a spreadsheet is copied, any scripts attached to it are copied as well. Anyone who has access to the spreadsheet can copy the script. (Collaborators who have only view access cannot open the script editor in the original spreadsheet. However, when they make a copy, they become the owner of the copy and can see the script.)
• Để sử dụng một dịch vụ khác với các dịch vụ được liệt kê ở trên, hãy tạo một menu tùy chỉnh chạy chức năng tập lệnh ứng dụng thay vì viết chức năng tùy chỉnh. Một chức năng được kích hoạt từ menu sẽ yêu cầu người dùng ủy quyền nếu cần thiết và do đó có thể sử dụng tất cả các dịch vụ tập lệnh ứng dụng.

Chia sẻ

Các chức năng tùy chỉnh bắt đầu bị ràng buộc với bảng tính mà chúng được tạo. Điều này có nghĩa là một hàm tùy chỉnh được viết trong một bảng tính không thể được sử dụng trong các bảng tính khác trừ khi bạn sử dụng một trong các phương thức sau:

Nhấp vào phần mở rộng> Tập lệnh ứng dụng để mở trình chỉnh sửa tập lệnh, sau đó sao chép văn bản tập lệnh từ bảng tính gốc và dán nó vào trình chỉnh sửa tập lệnh của bảng tính khác.

Tạo một bản sao của bảng tính chứa chức năng tùy chỉnh bằng cách nhấp vào Tệp> Tạo một bản sao. Khi một bảng tính được sao chép, bất kỳ tập lệnh nào được gắn vào nó cũng được sao chép. Bất cứ ai có quyền truy cập vào bảng tính đều có thể sao chép tập lệnh. .

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Xuất bản kịch bản dưới dạng tiện ích bổ sung của Google Sheets.

Tương tự, chức năng tùy chỉnh dưới đây tìm nạp hiệu quả nội dung trực tiếp từ Internet và sử dụng một mảng hai chiều để hiển thị hai cột kết quả chỉ với một cuộc gọi chức năng.Nếu mỗi ô yêu cầu gọi chức năng riêng của nó, thao tác sẽ mất nhiều thời gian hơn, vì máy chủ tập lệnh ứng dụng sẽ phải tải xuống và phân tích nguồn cấp XML mỗi lần.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Các kỹ thuật này có thể được áp dụng cho gần như bất kỳ chức năng tùy chỉnh nào được sử dụng nhiều lần trong suốt bảng tính, mặc dù các chi tiết triển khai sẽ thay đổi tùy thuộc vào hành vi của chức năng.