Bài 3: Copy và Di Chuyển Dải Ô Sang Sheet khác

Đây là Bài 3 trong chuỗi serie Học Ứng dụng Google App Script vào Google Sheet. Trong bài này, mục tiêu chính của chúng ta là tìm hiểu cách Copy và Di chuyển dải ô từ sheet này sang sheet khác. Clip hướng dẫn:

Nội dung

  • Copy và Di chuyển dải ô trong 1 sheet
  • Copy và Di chuyển dải ô từ sheet này sang sheet khác
  • Xóa hàng, xóa cột
  • Tóm tắt các hàm lấy phân lớp Sheet

Tóm tắt

  • copyTo() copy dải ô đứng trước và paste vào vị trí dải ô trong ngoặc
  • moveTo() chuyển dải ô đước trước vào trị ví dải ô trong ngoặc
  • getSheetByName() đi đến sheet có tên trong ngoặc
  • deleteRow() xóa dòng có số thứ tự trong ngoặc
  • deleteColumn() xóa cột có số thứ tự trong ngoặc
  • deleteRows(rowPosition, howMany) xóa howMany dòng, bắt đầu từ dòng rowPosition
  • deleteColumns(columnPosition, howMany) xóa howMany cột, bắt đầu từ cột columnPosition

I. Copy và Di chuyển dải ô trong 1 sheet

Ở các bài trước thì mình đã giới thiệu các hàm getValue(), getValues(), setValue() và setValues(). Các hàm này được thiết lập nhằm mục đích chính là lấy giá trị và đặt giá trị. Chúng ta đã lợi dụng điều đó để copy và paste dải ô. Tuy nhiên thì các hàm trên không chuyên dùng để làm vậy. Thay vào đó chúng ta nên dùng hàm copyTo()moveTo().

Bài 2: Các hàm lấy giá trị và đặt giá trị dải ô,mình có đoạn code cuối cùng như sau:

Chức năng của nó là gán các giá trị của dải ô A1:D7 vào biến “giatri1“, sau đó đi đến dải ô F8:I14 để ghi các giá trị đó vào. Đây hoàn toàn giống với việc chúng ta đi copy và paste. App Script cung cấp 1 hàm chuyên dùng để làm điều này, đó là copyTo(). Bây giờ, mình không dùng đến các hàm getValues và setValues nữa, mình viết lại như sau:

Câu lệnh ” range.copyTo(dich); ” thực hiện nhiệm vụ:

  1. Đi đến dải ô trong biến “range” -> dải A1:D7
  2. Copy các ô trong dải
  3. Đi đến dải ô F8:I14 để Paste (Dán) vào

copyTo() là hàm copy dải ô đứng trước và paste vào vị trí dải ô trong ngoặc

Lưu ý rằng, biến “range” và biến “dich” phải có cùng kích thước (7 hàng, 4 cột). Tương tự dải ô mà các bạn muốn copy phải cùng kích thước hàng cột với dải ô mà cách bạn paste vào.

Cách sử dụng hàm moveTo cũng tương tự như copyTo

moveTo() là hàm di chuyển dải ô đước trước vào trị ví dải ô trong ngoặc

Tuy nhiên vì bản chất là “move” nên nó dữ liệu ở dải ô cũ cũng không còn. Xem hình:

II. Copy / Di chuyển dải ô từ sheet này sang sheet khác

Để copy hoặc di chuyển dải ô từ sheet này sang sheet khác thì bạn phải xác định sheet mà bạn muốn copy / move vào (sheet đích đến). Để phân biệt, mình sẽ đặt sheet0 là sheet mà chúng ta lấy dữ liệu, và sheet1 là sheet đích đến.

Mục tiêu: di chuyển dải ô A1:D7sheet0 và paste sang dải ô F8:I14sheet1

Để xác định sheet khác, chúng ta dùng hàm getSheetByName()

getSheetByName() là hàm đi đến sheet có tên trong ngoặc

Lưu ý tên phải để trong dấu ngoặc kép ” “

Ở trên, mình đã thay biến “sheet” cũ thành “sheet0” (để phân biệt với sheet1). Biến “sheet1” sẽ đi đến sheet có tên là sheet1. Đồng thời biến “dich” lúc này sẽ là dải ô F8:I14 trong sheet1.

Vì cách dùng của copyTo và moveTo là giống nhau nên mình chỉ minh họa moveTo thôi, các bạn có thể tự thử với copyTo.

III. Xóa hàng, xóa cột bằng App Script

Để xóa 1 hàng hoặc 1 cột trong 1 sheet, các bạn sử dụng hàm deleteRow() hoặc deleteColumn()

deleteRow() thực hiện việc xóa dòng có số thứ tự trong ngoặc

deleteColumn() thực hiện việc xóa cột có số thứ tự trong ngoặc

Ví dụ (không liên quan đến đoạn code trên), mình muốn xóa hàng 2 và cột 2 trong sheet đang thao tác (sheet0), mình viết như sau:

Trước khi chạy đoạn code trên
Sau khi chạy đoạn code thực hiện xóa dòng 2 và cột 2

Để xóa nhiều hàng / nhiều cột trong 1 sheet, chúng ta sử dụng hàm deleteRows() hoặc deleteColumns()

deleteRows(rowPosition, howMany) thực hiện việc xóa howMany dòng, bắt đầu từ dòng rowPosition

deleteColumns(columnPosition, howMany) thực hiện việc xóa howMany cột, bắt đầu từ cột columnPosition

Quay lại đoạn code chính của chúng ta. Mình muốn làm 1 việc như sau:

Sau khi đã di chuyển dải ô A1:D7 từ sheet0 sang F8:I14 ở sheet1, khu vực A1:D7 sẽ bị trống không. Bây giờ chúng ta sẽ xóa đi những hàng trống và cột trống đó. Có tất cả 7 dòng và 5 cột trống. Chúng ta được đoạn code hoàn chỉnh như sau:

Vậy là mình chia sẻ xong phần xóa dòng / cột. Tiếp đến mình sẽ tóm tắt các hàm lấy sheet.

IV. Tóm tắt các hàm lấy phân lớp Sheet

Chúng ta đã biết hàm getActiveSheet() sẽ lấy sheet mà chúng ta đang thao tác trên Google Sheet và getSheetByName() lấy sheet có tên ở trong ngoặc. Ngoài ra để lấy sheet, chúng ta còn 1 hàm nữa, đó là getSheets().

Bạn có thể đọc thêm về hàm getSheets() tại đây:

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets

Mình muốn chia sẻ 1 cách dùng của getSheets để lấy sheet có số thứ tự trong bảng tính. Để giải thích bằng lời thì hơi khó, nên mình sẽ lấy ví dụ cho dễ hiểu. Ở đoạn code chính, mình đã dùng getActiveSheet() cho sheet0 và getSheetByName() cho sheet1. Bây giờ mình sẽ thay chúng bằng getSheets()

Bằng việc bỏ số thứ tự của sheet trong bảng tính vào trong [ ], mình đã có kết quả tương tự. Lưu ý: sheet đầu tiên sẽ có số thứ tự là 0, sheet thứ 2 có số thứ tự là 1,… Đó là do trong Java Script, mảng bắt đầu đếm từ 0. Cho nên khi gọi sheet0 mình đã dùng getSheets()[0], còn gọi sheet1 thì dùng getSheets()[1]

Trước khi chạy code. Bạn hãy chạy thử code để xem kết quả nhé ^^

23 COMMENTS

  1. Chào bạn ,bạn có thể giúp mình viết một đoạn code copy => paste dữ liệu với từ dọc sang ngang ,hai điều kiện ko?
    Ví dụ: mình có hai Sheet là Sheet “Nguon” ô A1:A20 và “Dich” ô A7:T
    1, đ/k thứ nhất là kiểm tra và không thực thi lệnh copy => paste ,nếu để trống dữ liệu trong Sheet nguồn ,ô A1:A5,A7,A15.
    2, Kiểm tra số thứ tự tại cột A7:A ,Nếu trùng với số thứ tự tại A1 của Sheet “Nguon” thì sẽ Paste trồng lên dữ liệu cũ ;Nếu số thứ tự ko trùng thì sẽ Paste vào ô trống tiếp theo.

    • Chào bạn, mình hiểu ý bạn là muốn copy dải ô Nguon!A1:A20 sang Dich!A7:T7, và chỉ copy paste những ô có dữ liệu. Mình hiểu vậy có đúng không? Nếu không thì bạn giải thích thêm nhé, vì thực sự mình thấy hơi khó hiểu hì hì.
      Cảm ơn bạn đã đến với website ^^

    • function hangcot() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var nguon = ss.getSheetByName(“Nguon”);
      var dich = ss.getSheetByName(“Dich”);
      var nguonRange = nguon.getRange(“A1:A20”);

      var nguonValue = nguonRange.getValues();

      var j = -1;
      var dichR = [];
      for (var i = 0; i < nguonValue.length; i++) { if (nguonValue[i] != "") { j++; dichR[j] = nguonValue[i]; } } var dichRange = dich.getRange(7,1,1,dichR.length); dichRange.setValues([dichR]); }Gửi bạn code Chuyển dải ô từ cột sang hàng đồng thời loại bỏ các ô trống. Ý tưởng là gom các ô có dữ liệu trong dải ô Nguon!A1:A20 vào mảng dichR. Sau đó dùng setValues để ghi mảng dichR vào dải ô Dich!A7:T Code này sử dụng kiến thức về mảng trong Java, mình hiện tại chưa kịp làm đến phần đó nên hi vọng sẽ sớm ra clip hoặc bài viết ^^ Thân

  2. Cảm ơn Admin đã giúp ,Thực ra mình đang muốn làm một cái Form nhập dữ liệu trực tiếp trên bảng tính của GSheet ,có hai Sheet như mình đã hỏi ở trên ,Ý của mình là:
    1, Nhập dữ liệu mới từ Sheet”Nguon” sang Sheet”Dich”
    2, Lấy dữ liệu có sẵn trong Sheet”Dich” để sửa và lưu vào dữ liệu cũ hoặc có thể lưu thành dữ liệu mới.
    3, Mình có tạo một trang tính ,Admin vào xem và giúp mình nhé….Thank.
    Link: https://docs.google.com/spreadsheets/d/1uWyjyb_x4d9sA93jKdZg_1JzKcSSvJq_fki_GAt3Fug/edit?usp=sharing

    • Chào bạn, xin lỗi bạn vì trả lời lâu nhé, qua giờ lo ăn Tết haha
      Đây, mình có làm 1 cái sheet cho bạn nè, bên trong đã có sẵn hướng dẫn sử dụng. Mà vì code dài quá nên mình không giải thích ở đây được, có gì thắc mắc thì bạn cứ hỏi mình. Tuy nhiên thì mình chỉ giúp bạn 90% thôi, có 1 phần mình bỏ để hi vọng bạn có thể tự tìm hiểu hì hì (xem Hướng dẫn ở trong link). Tất nhiên là bạn hoàn toàn có thể hỏi lại nếu có khó khăn.
      À vì file này mình Public, nên bạn có thể xem code trong App Script luôn nhé. Mà bạn vui lòng copy code mình viết và paste sang file của bạn rồi hãy chỉnh sửa nhé!

      Ôi 4 tiếng của mình, hi vọng sẽ có ích cho bạn!

      https://docs.google.com/spreadsheets/d/1OGq_zz0cMz7bmVpRN6dZHqIAW1BBX0t_v4TwFJ8UYck/edit?usp=sharing

  3. Cảm ơn bạn ,Giống như một món quà tuyệt vời đầu năm mới 2019 mình nhận được từ bạn ,Các chức năng chạy rất tốt và đúng theo những gì mình đang cần ,Hiện tại như thế này là ổn rồi…Tuy nhiên trong quá trình dùng thực tế có thể xảy ra những tình huống khác ,Vậy rất mong được sự giúp đỡ của bạn ,Nhân dịp năm mới 2019 Xin chúc bạn gáp nhiền may mắn trong cuộc sống.

  4. Chào bạn ,Bạn giúp mình hai vấn đề nữa nhé:
    1, Mình muốn chức năng “kiểm tra bỏ trống dữ liệu” được tích hợp vào chức năng “Lưu mới” và “Lưu cũ” ,Tức là khi chọn lệnh “Lưu” mà có ô để trống dữ liệu thì sẽ không lưu được và có thông báo lỗi.
    2, Bạn thêm cho mình 1 chức năng sửa dữ liệu cũ trong 4 cột ,trên một Sheet mới ,Bạn xem File và giúp mình nhé….Thank.

    • Hi bạn, viết vội nên chưa phủ hết các điều kiện nên chắc có thể xảy ra lỗi. Bạn tạo bản sao (duplicate) file dưới đây về Drive của bạn để chỉnh sửa thêm nhé (Tệp -> Tạo bản sao)
      https://docs.google.com/spreadsheets/d/1tiZqu3WyTI5I7Oj3ZEsT6eyJRF_esJEKFwpvzE_1qsQ/edit#gid=358753101

      À mà ở sheet SuaCotTuyChon, mình có thể làm như format của bạn nhưng mà mình thấy để vậy thì hay hơn, vì có thể thêm bớt cột tùy ý, chứ không phải nhất thiết lúc nào cũng 4 cột 10 -> 13
      Với cả mình không làm như lần trước là cho các chức năng vào 1 ô, mà lần này mình để trong macro, vì mình thấy bạn cũng biết tạo macro nên mình đổi thế.

      Bạn thấy sao?

  5. Chào bạn ,Mình có lang thang trên mạng ,Thấy có đoạn mã dưới đây:

    function Login(){
    var objSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘F’);
    var range3 = objSheet.getRange(‘A6’);
    var objUi = SpreadsheetApp.getUi();
    var input = objUi.prompt(‘TÊN ĐĂNG NHẬP’, Browser.Buttons.OK_CANCEL).getResponseText();

    var range1 = objSheet.getRange(‘A5’);
    var range2 = objSheet.getRange(‘B5’);

    range1.setValue(input);
    var answer = range2.getValue();

    while (input != answer){
    input = objUi.prompt(‘MẬT KHẨU’, Browser.Buttons.OK_CANCEL).getResponseText();
    range1.copyTo(range3);
    range1.setValue(input);
    }
    objUi.alert(‘LOGIN THÀNH CÔNG !’)
    }

    Thấy rất hay và có thể áp dụng vào nhiều điều kiện người dùng ,Mình định sử dụng vào việc cho người dùng chung bảng tính phải đăng nhập để lấy thông tin người dùng.
    Nhưng mình gặp phải trường hợp rất khó chịu là:
    + Khi chạy mã lệnh này lên rồi ,mà không tiếp tục đăng nhập ,hay ko có mật khẩu thì ko thể nào thoát ra được….Chỉ còn cách tắt bảng tính.
    + Mà ko hiểu sao ,cái nút CANCEL có mà nó ko hoạt động gì cả…….Bạn xem và giúp mình để cho chức năng CANCEL hoạt động nhé…Thank
    Link: https://docs.google.com/spreadsheets/d/1W8Rp3-2EJm9Xw22W3eofQdYtKmOkfjn-9NeD0dCZK6c/edit?usp=sharing

    • Mình hơi không hiểu ý bạn là gì. Cái code này có mục đích là:
      1. Phải đăng nhập
      2. Nếu mật khẩu không đúng thì nhập lại. Nhập đến khi nào đúng thì thôi.

      Nếu bạn muốn nút Cancel hoạt động, thì cái việc đăng nhập đâu có ý nghĩa gì nữa. Vì nếu ta Cancel được, thì ta có thể xem toàn bộ bảng tính mà không cần đăng nhập.
      Còn điều thứ 2, thật sự mình không muốn vô duyên đâu nhưng mà sẽ chẳng ai viết code với mục đích như thế cả. Vì người ta nếu đã có được link truy cập vào bảng tính thì đều có thể đi đến cái ô chứa password để xem password mà không cần đăng kí với bạn trước đó. Bạn hiểu ý mình không? Nghĩa là ví dụ như bạn lưu password của tất cả user vào dải ô E77:F81 trong link đi. Thì dù bạn có dùng onOpen->Login để bắt đăng nhập, nhưng mình hoàn toàn có thể vào link này để xem luôn password của tất cả user:
      https://docs.google.com/spreadsheets/d/1W8Rp3-2EJm9Xw22W3eofQdYtKmOkfjn-9NeD0dCZK6c/edit#gid=0&range=E77:F81

      Hoặc nếu bạn chọn lưu password trong file App Script thì khi 1 người có quyền edit file GSheet, thì người ta cũng có thể xem và edit nội dung của file App Script.
      Nói tóm lại, dù bạn lưu password ở đâu, thì người dùng vẫn có thể xem được. Còn nếu bạn sử dụng những hàm cao cấp hơn như là liên kết mysql hay database gì đó thì may ra làm được. Cơ mà mình không chuyên về mấy cái đó nên không giúp bạn được haha.

      Tuy nhiên không phải vì thế mà mình không giúp đâu. Cái code trên nếu bạn cần thì mình vẫn sẽ xem nhé. Chỉ là hơi thắc mắc về mục đích của bạn thôi.

  6. Chào bạn…Mình vừa sửa lại cái mã lệnh LOGIN ,và mình cho dữ liệu + Pass người dùng vào trong một bảng tính khác ,Ko biết có khả thi ko ? Phiền bạn giúp mình cái lệnh CANCEL nhé ,Vì mình sẽ áp dụng vào việc khác…
    Bạn cho hỏi là: Khi thoát bảng tính mà câu lệnh chưa hoàn thành ,Thì có ảnh hướng gì ko ? chẳng hạn như bị lỗi bảng tính chẳng hạn…Thank

    • Đây, nếu bạn muốn Cancel hoạt động thì phải dùng thêm 1 Điều kiện cho vòng lặp While nhé
      function Login(){
      var objSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘F’);
      var range3 = objSheet.getRange(‘A6’);
      var objUi = SpreadsheetApp.getUi();
      var input = objUi.prompt(‘TÊN ĐĂNG NHẬP’, Browser.Buttons.OK_CANCEL).getResponseText();

      var range1 = objSheet.getRange(‘A5’);
      var range2 = objSheet.getRange(‘B5’);

      range1.setValue(input);
      var answer = range2.getValue();
      var passButton = objUi.prompt(‘MẬT KHẨU’, Browser.Buttons.OK_CANCEL);

      while (passButton.getSelectedButton() == objUi.Button.OK && input != answer){
      passButton = objUi.prompt(‘MẬT KHẨU’, Browser.Buttons.OK_CANCEL); //cập nhật lại giá trị cho passButton
      input = passButton.getResponseText();
      range1.copyTo(range3);
      range1.setValue(input);
      }
      objUi.alert(‘LOGIN THÀNH CÔNG !’)
      }

      Còn các câu hỏi của bạn thì mình trả lời như sau:
      1. Dù bạn có để password ở đâu thì người ta cũng tìm ra được thôi (trừ khi bạn dấu nó kĩ ơi là kĩ haha. Kiểu như dấu ở cái dải ô T1000:X1004 và để chữ là màu trắng, thì xa quá người ta không biết chứ còn để ở mấy chỗ gần gần thì dễ tìm ra thôi. Mà nói chung để password ở ngay trong bảng tính như thế thì không nên đâu, vì thiếu an toàn)
      2. Về vụ thoát bảng tính, thì tùy vào câu lệnh mà bạn đang nói tới là gì. Nếu là sử dụng vòng lặp For để viết vô các ô, ví dụ như cần viết từ ô 1->200, mà nó đang viết dở tới ô 150 thì bạn tắt file, thì chương trình vẫn sẽ tiếp tục chạy khi bạn đóng file nhé. Còn nếu là các câu lệnh bình thường như kiểu cái Login lần trước bạn viết ấy, thì nó chỉ đơn giản là dừng code lại thôi. Chứ không có lỗi bảng tính nào cả.

      Mấy câu hỏi của bạn thực sự rất thú vị, nhờ đó mà mình có vài ý tưởng mới cho các chương trình mà mình viết. Cảm ơn bạn vì đã hỏi!

    • Không phải lỗi code đâu, mà do khi bạn copy từ web qua thì cái dấu nháy ‘ nó bị lỗi ấy mà. Sau khi copy qua thì bạn đổi hết các dấu ‘ thành “ nhé. Còn file thì bạn đag set quyền nên mình không vào được app script.

  7. Chào bạn ,Mình lại có vấn đề nhờ bạn giúp về lỗi thực hiện lệnh khi thanh Menu trang tính bị ẩn.
    Mình sử dụng đoạn mã lệnh sau:

    function onEdit(){
    var F = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“F”);
    var Ff3 = F.getRange(“F3”).getValue();
    if (Ff3 == “UserMail”) {
    UserMail();
    F.getRange(“F3”).clearContent();
    }
    }

    function UserMail(){
    var F = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘F’);
    var Fr1 = F.getRange(‘H3’);
    var userEmail=Session.getActiveUser().getEmail();
    Fr1.setValue(userEmail)
    Browser.msgBox(“Your email address is “+userEmail);
    }

    Khi mình mở Link bình thường thì nó chạy Ok….Link: https://docs.google.com/spreadsheets/d/1W8Rp3-2EJm9Xw22W3eofQdYtKmOkfjn-9NeD0dCZK6c/edit?usp=sharing

    Nhưng khi mình chạy Link ẩn Menu thì không thể chạy được lệnh…Bạn xem Link: https://docs.google.com/spreadsheets/d/1W8Rp3-2EJm9Xw22W3eofQdYtKmOkfjn-9NeD0dCZK6c/edit?hl=en&hl=en&ui=2&chrome=false&rm=demo#gid=0

    * Các lệnh khác vẫn chạy bình thường.

    Nhờ bạn giúp mình nhé ,Thank

  8. Chào Bác, bài hướng dẫn của Bác thật tuyệt.. đúng cái E đang muốn học!

    Em xin hỏi thêm về copy và paste nhưng thêm điều kiện tìm dữ liệu và dán vào đúng dòng dữ liệu thì không biết có khó không? Nếu Bác biết xin Bác chỉ cho 1 script để chạy với… E cảm ơn Bác trước!!!

    Link và mong muốn copy, dán ở trong file này ạ:

    https://docs.google.com/spreadsheets/d/1lNpjevrCe6Mz-GOfIHyZZfPjgIR6qTUU8tU1oNvzIf0/edit?usp=sharing

    • Chào bạn, vui vì nó có ích cho bạn
      Link của bạn mình đã xem qua. Bạn thử suy nghĩ làm theo cách này xem sao nhé. Nếu vẫn không được thì mình sẽ giúp tiếp
      1. Khai báo Biến 1 – lấy giá trị của ô C3 sheet CapnhatCS
      2. Khai báo mảng A – lấy giá trị dải ô B5:B10 sheet Dulieu
      3. Chạy vòng lặp For trong mảng A. Từ i=0 đến i< mảng A.lenth 4. Dùng câu lệnh điều kiện If để kiểm tra. Nếu Biến 1 == A[i], thì tiến hành ghi dữ liệu 5. Ghi dữ liệu: ghi Ngày thanh toán và Số tiền thanh toán từ sheet CapnhatCS sang sheet Dulieu, trong bài hướng dẫn trên có chỉ cách làm đó ạ Thân

  9. Nhờ bạn hướng dẫn cách tạo nút để bấm vào đó chạy lệnh function myFunction() để copyto từ sheet này sang sheet khác.

  10. Chào bạn, hiện nay mình đang có vấn đề cần được giúp đỡ, do mình đang chạy báo cáo dữ liệu cho team.
    Mình có 1 bảng dữ liệu, các hàng ở trên cùng sẽ là thông tin mới nhất, Vậy bạn có thể giúp mình có 1 đoạn macro để khi các bạn điền hết dòng sát với dòng công thức trên cùng sẽ auto nhảy ra dòng mới và có coppy công thức sẵn.
    VD: A1:H1 là dòng công thức
    các dòng bên dưới từ A2:A100 là dòng để điền thông tin.
    Khi mình đã viết tới dòng A2 thì sẽ auto nhảy trả lại 1 dòng trắng và coppy công thức ở các ô từ A1:H1

Leave a Reply