Google Trang tính thành

Google Trang tính cung cấp hàng trăm chức năng tích hợp sẵn 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;
}

1,

/**
 * 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 và

/**
 * 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. Khi những thứ này không đủ cho nhu cầu của bạn, bạn có thể sử dụng Google Apps Script để viết các hàm tùy chỉnh — chẳng hạn như chuyển đổi mét thành dặm hoặc — sau đó sử dụng chúng trong Google Trang tính giống như một hàm tích hợp sẵn

Bắt đầu

Các chức năng tùy chỉnh được tạo bằ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. (Ghi chú. khóa học này không được phát triển bởi và không được liên kết với Google. )

Đây là một hàm tùy chỉnh đơn giản, có tên 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;
}

4, hàm này nhân một giá trị đầu vào với 2

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
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 xem liệu người khác đã xây dựng chức năng tùy chỉnh mà bạn cần hay chưa

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ở bảng tính trong Google Trang tính
2. Chọn mục menu Tiện ích mở rộng > Apps Script.
3. Xóa bất kỳ mã nào trong trình chỉnh sửa tập lệnh. Đối với chức năng

   /**
    * 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 ở 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. Ở trên cùng, hãy nhấp vào Lưu lưu .

Bây giờ bạn có thể

Nhận chức năng tùy chỉnh từ Google Workspace Marketplace

Google Workspace Marketplace 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 Trang tính. Để sử dụng hoặc khám phá các tiện ích bổ sung này

1. Tạo hoặc mở bảng tính trong Google Trang tính
2. Ở trên cùng, hãy nhấp vào Tiện ích bổ sung > Tải tiện ích bổ sung
3. Sau khi Google Workspace Marketplace mở ra, hãy nhấp vào hộp tìm kiếm ở góc trên cùng bên phải
4. Nhập "chức năng tùy chỉnh" và nhấn Enter
5. 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ó
6. Một hộp thoại có thể cho bạn biết rằng tiện ích bổ sung cần có quyền. Nếu vậy, hãy đọc kỹ thông báo, sau đó nhấp vào Cho phép
7. Tiện ích bổ sung 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, hãy nhấp vào Tiện ích bổ sung > Quản lý 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 more_vert > Sử dụng trong tài liệu này.

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

Sau khi bạn đã viết hoặc cài đặt một chức năng tùy chỉnh từ Google Workspace Marketplace, bạn có thể dễ dàng sử dụng chức năng này như một chức năng tích hợp sẵn

1. Nhấp vào ô mà bạn muốn sử dụng chức năng
2. Nhập dấu bằng (

   /**
    * 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;
   }
   

   6) 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 — rồi nhấn Enter
3. Ô 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ả

Nguyên tắc 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 số nguyên tắc cần biết

đặt tên

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

• Tên của hàm tùy chỉnh phải khác 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 chức năng tùy chỉnh không được 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 chức năng riêng tư trong Apps Script
• Tên của một chức năng 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
• Viết hoa không thành vấn đề, mặc dù tên của các hàm bảng tính theo truyền thống là chữ hoa

Tranh luận

Giống như hàm dựng sẵn, hàm tùy chỉnh có thể lấy đối số làm giá trị đầu vào

• Nếu bạn gọi hàm của mình với tham chiếu đến một ô duy nhất làm đối số (chẳng hạn 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), thì đối số sẽ là giá trị của ô

• Nếu bạn gọi hàm của mình với tham chiếu đến một dải ô làm đối số (chẳng hạn 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), thì đối số sẽ là một mảng hai chiều gồm các giá trị của ô. 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 Apps Script hiểu 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

  /**
   * 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 sẽ cần phải là
  
  

• Đối số chức năng tùy chỉnh phải xác định. Nghĩa là, các hàm bảng tính tích hợp trả về một kết quả khác nhau mỗi khi chúng tính toán — chẳng hạn 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;
  }
  

  11 hoặc

  /**
   * 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;
  }
  

  12 — không được phép làm đối số cho hàm tùy chỉnh. Nếu một hàm tùy chỉnh cố gắng trả về một giá trị dựa trên một trong các hàm tích hợp dễ thay đổi này, thì hàm đó sẽ hiển thị

  /**
   * 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 vô thời hạn

Giá trị trả về

Mọi hàm tùy chỉnh phải trả về một giá trị để hiển thị, sao cho

• Nếu một hàm tùy chỉnh trả về một giá trị, thì giá trị đó sẽ hiển thị trong ô mà hàm được gọi từ đó
• Nếu một hàm tùy chỉnh trả về một mảng giá trị hai chiều, thì các giá trị đó sẽ tràn vào các ô liền kề miễn là các ô đó trống. Nếu điều này khiến mảng ghi đè lên nội dung ô hiện có, thì hàm tùy chỉnh sẽ đưa ra lỗi thay thế. Ví dụ, xem phần trên
• Hàm tùy chỉnh không thể ảnh hưởng đến các ô khác với các ô mà nó trả về giá trị cho. Nói cách khác, một hàm tùy chỉnh không thể chỉnh sửa các ô tùy ý, chỉ các ô được gọi từ đó và các ô liền kề của chúng. Để chỉnh sửa các ô tùy ý, hãy sử dụng menu tùy chỉnh để chạy một hàm thay thế
• Lệnh gọi hàm tùy chỉnh phải quay lại trong vòng 30 giây. Nếu không, ô sẽ hiển thị lỗ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;
  }
  

  14

Loại dữ liệu

Google Trang tính 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, Apps Script coi chúng là loại dữ liệu thích hợp trong JavaScript. Đây là những lĩnh vực phổ biến nhất của sự nhầm lẫn

• Ngày và giờ trong Trang tính trở thành đối tượng Ngày trong Apps Script. Nếu bảng tính và tập lệnh sử dụng các múi giờ khác nhau (sự cố 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 Trang tính cũng trở thành đối tượng

  /**
   * 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;
  }
  

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

  /**
   * 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;
  }
  

  16 trở thà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;
  }
  

  17 trong Apps Script

tự động hoàn thành

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

Các hàm 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} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

18, như trong 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;
}

19 bên 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;
}

Nâng cao

Sử dụng dịch vụ Apps Script

Các chức năng tùy chỉnh có thể gọi một số dịch vụ Apps Script nhất định để thực hiện các tác vụ phức tạp hơn. Ví dụ: một chức năng 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 cấp 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ể như sau

Các dịch vụ được hỗ trợNotesCacheWorks, nhưng không đặc biệt hữu ích trong các chức năng tùy chỉnhHTMLCó thể tạo HTML, nhưng không thể hiển thị nó (hiếm khi hữu ích)JDBCLanguageLockWorks, nhưng không đặc biệt hữu ích trong các chức năng tùy chỉnhBản đồCó thể tính toán chỉ đường nhưng không hiển thị bản đồProperties

/**
 * 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;
}

10 chỉ nhận các thuộc tính của chủ sở hữu bảng tính. Trình chỉnh sửa bảng tính không thể đặt thuộc tính người dùng trong chức năng tùy chỉnh. Chỉ đọc bảng tính (có thể sử dụng hầu hết các phương 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;
}

11, nhưng không phải phương 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;
}

12).
Không thể mở các bảng tính khác (______213 hoặc

/**
 * 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;
}

14). Tìm nạp URLXML

Nếu chức năng tùy chỉnh của bạn đưa ra thông báo lỗ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;
}

15, thì dịch vụ cần có sự cho phép của người dùng và do đó không thể sử dụng được trong chức năng tùy chỉnh

Để sử dụng một dịch vụ khác ngoài những dịch vụ được liệt kê ở trên, hãy tạo menu tùy chỉnh chạy chức năng Apps Script 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 và do đó có thể sử dụng tất cả các dịch vụ Apps Script

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 trong. Điều này có nghĩa là không thể sử dụng hàm tùy chỉnh được viết trong một bảng tính trong các bảng tính khác trừ khi bạn sử dụng một trong các phương pháp sau

• Nhấp vào Tiện ích mở rộng > Apps Script để 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 vào trình chỉnh sửa tập lệnh .
• Tạo một bản sao của bảng tính có chứa chức năng tùy chỉnh bằng cách nhấp vào Tệp > Tạo bản sao. Khi một bảng tính được sao chép, mọi tập lệnh được đính kèm với nó cũng được sao chép. Bất kỳ ai có quyền truy cập vào bảng tính đều có thể sao chép tập lệnh. (Cộng tác viên chỉ có quyền xem không thể mở trình chỉnh sửa tập lệnh trong bảng tính gốc. Tuy nhiên, khi họ tạo một bản sao, họ trở thành chủ sở hữu của bản sao và có thể xem tập lệnh. )
• Xuất bản tập lệnh dưới dạng tiện ích bổ sung Google Trang tính

Điều này có nghĩa là bất kỳ ai có quyền chỉnh sửa bảng tính đều có thể chỉnh sửa bất kỳ mã Apps Script nào được đính kèm với bảng tính đó

Tối ưu hóa

Mỗi khi một chức năng tùy chỉnh được sử dụng trong bảng tính, Google Trang tính sẽ thực hiện một cuộc gọi riêng đến máy chủ Apps Script. Nếu bảng tính của bạn chứa hàng chục (hoặc hàng trăm hoặc hàng nghìn). ) của lệnh gọi hàm tùy chỉnh, quá trình này có thể khá chậm

Do đó, nếu bạn dự định sử dụng một hàm tùy chỉnh nhiều lần trên một phạm vi dữ liệu lớn, hãy xem xét sửa đổi hàm để nó chấp nhận một phạm vi làm đầu vào ở dạng mảng hai chiều, sau đó trả về một mảng hai chiều có thể

Ví dụ: hàm

/**
 * 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;
}

19 hiển thị ở trên có thể được viết lại để chấp nhận một ô hoặc một dải ô như sau

/**
 * 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

Cách tiếp cận trên sử dụng phương thức bản đồ của đối tượng

/**
 * 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;
}

17 của JavaScript để gọi đệ quy

/**
 * 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 trên mọi giá trị trong mảng ô hai chiều. Nó trả về một mảng hai chiều chứa kết quả. Bằng cách này, bạn có thể gọ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;
}

4 chỉ một lần nhưng nó sẽ tính toán cho một số lượng lớn ô cùng một lúc, như minh họa trong ảnh chụp màn hình bên dưới. (Bạn có thể thực hiện điều tương tự với các câu lệ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;
}

10 lồng nhau thay vì lệnh gọ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;
}

11. )

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

/**
 * 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

Các kỹ thuật này có thể được áp dụng cho hầu hết mọi chức năng tùy chỉnh được sử dụng nhiều lần trong toàn bộ bảng tính, mặc dù chi tiết triển khai sẽ khác nhau tùy thuộc vào hoạt động của chức năng

Ctrl F trong Google Trang tính là gì?

Google Trang tính có tốt như Excel không?

Làm cách nào để luôn sắp xếp từ A đến Z trong Google Trang tính?

Google Trang tính chỉ chuyển đến Z phải không?