Giúp mình thêm tính năng trên Apps Script của Google Sheet

Liên hệ QC
Tôi tuân thủ nội quy khi đăng bài
Có
Kientran871994

Kientran871994

Thành viên mới
Tham gia
23/9/23
Bài viết
9
Được thích
0
Hiện tại, mình đã viết xong 1 đoạn Code Apps Script nhờ ChatGPT có thuật toán như sau:
- Mình có 2 sheet cần quan tâm là: "Danhsach" và "Data" (như trong hình).
- Mỗi khi mình thêm dữ liệu vào cột C4 trong sheet "Danhsach" và chạy Script thì đoạn Code của mình sẽ sinh ra 1 số ngẫu nhiên trong khoảng từ 1 đến 11, từ đó nó sẽ đối chiếu với bảng phụ tại Sheet "Data" có tọa độ: C4:E14 như trong hình. Theo đó, mỗi số ngẫu nhiên sinh ra trong khoảng từ 1 đến 11 sẽ phải đối chiếu với dữ liệu tại cột Số thứ tự, sau đó gán giá trị trong cột "Thẩm phán" vào ô tương ứng tại cột B trong sheet "Danhsach".
********Ví dụ:
Bước 1: Mình nhập liệu vào cột C trên Sheet "Danhsach" và chạy Script.
Bước 2: Hệ thống tự random ra số ngẫu nhiên là 11.
Bước 3: Hệ thống đối chiếu với dữ liệu trong sheet "Data", số 11 ứng với "Nguyễn Văn L"
Bước 4: Hệ thống tự động gán vào cột B tương ứng trong sheet "Danhsach" như trong hình.
Screenshot 2023-09-23 at 00.59.33.png
Screenshot 2023-09-23 at 00.59.40.png

***** Tuy nhiên, bây giờ mình muốn thêm chức năng như sau: Mình đã tạo sẵn các checkbox, ví dụ nếu mình tích vào checkbox đầu tiên là ông Nguyễn Văn A, thì khi hệ thống random ra số 1 là ông Nguyễn Văn A thì hệ thống sẽ bỏ qua và không tự động điền dữ liệu vào Sheet "Danhsach" nữa, hệ thống sẽ tự động random ra 1 số khác không phải số 1 (do mình đã tick vào ô checkbox, mục đích tích là để loại người đó ra) và điền vào Sheet "Danhsach"........ Tức là: Nếu mình tích các ô checkbox từ 1 đến 10, thì hệ thống sẽ chỉ điền vào sheet "Danhsach" khi nó random ra số 11, ứng với Nguyễn Văn L.
Hiện tại, mình không thể viết được đoạn Script này, các bạn giúp mình với ! Xin cảm ơn. Dưới đây là đoạn Script mà mình sử dụng ChatGPT để viết lên do không biết gì về lập trình hết. Mình xin cảm ơn các bạn trong diễn đàn !
Script:
function RANDOMIZE_AND_DOICHIUE() {
// Lấy sheet chính (đang được mở) và sheet "Data" theo tên
var mainSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");

// Lấy dòng cuối cùng trong cột A của bảng chính
var lastRowMain = mainSheet.getLastRow();

// Lấy giá trị của cột A từ hàng 4 đến hàng cuối cùng trong bảng chính
var mainRange = mainSheet.getRange("A4:A" + lastRowMain).getValues();

// Lấy giá trị của cột C và D từ bảng "Data"
var dataRange = dataSheet.getRange("C4:D14").getValues();

// Biến kiểm tra có cần hiển thị thông báo
var displayAlert = false;

// Lặp qua từng số ngẫu nhiên trong cột A của bảng chính
for (var i = 0; i < mainRange.length; i++) {
// Kiểm tra xem ô trong cột B của bảng chính đã có dữ liệu chưa
var cellValue = mainSheet.getRange(4 + i, 2).getValue();

if (cellValue === "") {
// Sử dụng số ngẫu nhiên đã sinh để thực hiện đối chiếu
var soNgauNhien = Math.floor(Math.random() * 11) + 1;
var found = false;

// Lặp qua từng hàng trong cột C và D của bảng "Data"
for (var j = 0; j < dataRange.length; j++) {
if (dataRange[j][0] == soNgauNhien) {
// Nếu tìm thấy số ngẫu nhiên trong cột C của "Data" và ô trong cột B của bảng chính trống
// điền dữ liệu tham chiếu tương ứng vào cột B của bảng chính
mainSheet.getRange(4 + i, 2).setValue(dataRange[j][1]);
found = true;
break;
}
}

// Nếu không tìm thấy dữ liệu, thiết lập biến hiển thị thông báo
if (!found) {
displayAlert = true;
}
}
}

// Hiển thị thông báo nếu cần
if (displayAlert) {
SpreadsheetApp.getUi().alert('Yêu cầu nhập dữ liệu trước khi thực hiện script.');
}
}
Nhấp chuột vào đây để mở rộng...
 
Kientran871994 đã viết:
docs.google.com

phancongthamphan

docs.google.com docs.google.com
Dạ link đây ạ. Em quên mất. Mọi người giúp e với
Nhấp chuột vào đây để mở rộng...
Bạn thử xem:
Mã: 
function onEdit(e) {
  var res = filterData();
  var len = res.length;
  sh = e.source.getActiveSheet();
  col = e.range.getColumn();
  row = e.range.getRow();
  // Browser.msgBox(sh.getName());
  if (sh.getName() === "Danhsach" && col === 3 && row >= 4){
    if (len === 0){
      Browser.msgBox("Không có dữ liệu để điền!");
      return;};
    var r = Math.floor(Math.random()*len);
    if (e.range.getValue() === ""){
      sh.getRange(row,col-1).setValue("")}
    else{
      sh.getRange(row,col-1).setValue(res[r][1])}
  }
}

function filterData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Data");
  var danhSachSheet = ss.getSheetByName("Danhsach");
  var data = dataSheet.getRange("C4:E14").getValues();
  var resultArray = data.filter(c=>{return c[2] === false;
    }).map(v=>{return [v[0],v[1]];});
  return resultArray
}
 
Nhattanktnn đã viết:
Bạn thử xem:
Mã: 
function onEdit(e) {
  var res = filterData();
  var len = res.length;
  sh = e.source.getActiveSheet();
  col = e.range.getColumn();
  row = e.range.getRow();
  // Browser.msgBox(sh.getName());
  if (sh.getName() === "Danhsach" && col === 3 && row >= 4){
    if (len === 0){
      Browser.msgBox("Không có dữ liệu để điền!");
      return;};
    var r = Math.floor(Math.random()*len);
    if (e.range.getValue() === ""){
      sh.getRange(row,col-1).setValue("")}
    else{
      sh.getRange(row,col-1).setValue(res[r][1])}
  }
}

function filterData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Data");
  var danhSachSheet = ss.getSheetByName("Danhsach");
  var data = dataSheet.getRange("C4:E14").getValues();
  var resultArray = data.filter(c=>{return c[2] === false;
    }).map(v=>{return [v[0],v[1]];});
  return resultArray
}
Nhấp chuột vào đây để mở rộng...
Cảm ơn bạn ! Mình tạo 1 script riêng để chạy nó đúng k bạn ?
 
Em làm được rồi ạ. Tuy nhiên, em còn 1 sheet "Form" để nhập liệu. Khi e nhập từ Form để script tự sao chép sang cột C của Sheet "Danhsach" thì có vẻ Code trên nó không tự thêm giá trị vào cột B trên Sheet "Danhsach" ạ. Nhưng khi em thêm tay vào cột B trên sheet "Danhsach" thì được.
Căn bản là sheet "Danhsach" thì e sẽ đặt mật khẩu để 1 vài người có quyền chỉnh sửa thôi ạ.
 
Đây là Video e quay lại thao tác ạ:
Script Form của em:
function nhaplieu() {
var form = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form");
var danhsach = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Danhsach");
// Lấy dữ liệu từ sheet "Form"
var dataToCopy = [
form.getRange("C4").getValue(),
form.getRange("C5").getValue(),
form.getRange("C6").getValue(),
form.getRange("C7").getValue(),
form.getRange("C8").getValue()
];
// Kiểm tra xem có dữ liệu để sao chép không (vd: nếu dòng đầu tiên trong "Form" có giá trị)
var hasDataToCopy = dataToCopy.some(function(value) { return value !== "" });
if (hasDataToCopy) {
// Tìm dòng đầu tiên trong "Danhsach" có dữ liệu (bắt đầu từ dòng 4)
var firstEmptyRow = 4;
while (danhsach.getRange(firstEmptyRow, 3).getValue() !== "") {
firstEmptyRow++;
}
// Sao chép dữ liệu vào "Danhsach" tại dòng tìm được
danhsach.getRange(firstEmptyRow, 3, 1, 5).setValues([dataToCopy]);
// Xóa nội dung của các ô C4:C8 trên "Form"
form.getRange("C4:C8").clearContent();
} else {
// Hiển thị thông báo nếu không có dữ liệu để sao chép
Browser.msgBox("Không có dữ liệu để sao chép!");
}
}
Nhấp chuột vào đây để mở rộng...
 
Kientran871994 đã viết:
Ai giúp mính với ạ :(
Nhấp chuột vào đây để mở rộng...
Bạn bỏ hết toàn bộ code. Thay thế bằng code này:

JavaScript: 
var ss = SpreadsheetApp.getActive();
var ds = ss.getSheetByName("Danhsach");
var form_range = ss.getSheetByName("Form").getRange("C4:C8");
var data_range = ss.getSheetByName("Data").getRange("C4:E14");
var thamphan_col = 2;
var firstrow_ds = 4;

function onEdit(e) {
  sh = e.source.getActiveSheet();
  c = e.range.getColumn();
  r = e.range.getRow();
  if (sh.getName() === ds.getName() && c===thamphan_col+1 && r>= firstrow_ds){
    var t = thamphan();
    if (sh.getRange(r,c).getValue() === ""){
      sh.getRange(r,thamphan_col).setValue("")}
    else{
      sh.getRange(r,thamphan_col).setValue(t)}
  }
}

function thamphan(){
  var res = filterData();
  var len = res.length;
  if (len !== 0){
    var rws = Math.floor(Math.random()*len);
    return res[rws][1];
  }
}

function filterData() {
  var d = data_range.getValues();
  var resultArray = d.filter(c=>{return c[2] === false;
    }).map(v=>{return [v[0],v[1]];});
  return resultArray
}

function transpose(a){
  return Object.keys(a[0]).map(c=>{ return a.map(r=>{ return r[c]; }); });
}

function nhaplieu() {
  var r = ds.getLastRow();
  var a = transpose(form_range.getValues());
  var b = new Array();
  b = a[0];
  b.unshift(null,thamphan());
  ds.appendRow(b);
  form_range.offset(1,0,form_range.getNumRows()-1).clearContent();
}
 
Nhattanktnn đã viết:
Bạn bỏ hết toàn bộ code. Thay thế bằng code này:

JavaScript: 
var ss = SpreadsheetApp.getActive();
var ds = ss.getSheetByName("Danhsach");
var form_range = ss.getSheetByName("Form").getRange("C4:C8");
var data_range = ss.getSheetByName("Data").getRange("C4:E14");
var thamphan_col = 2;
var firstrow_ds = 4;

function onEdit(e) {
  sh = e.source.getActiveSheet();
  c = e.range.getColumn();
  r = e.range.getRow();
  if (sh.getName() === ds.getName() && c===thamphan_col+1 && r>= firstrow_ds){
    var t = thamphan();
    if (sh.getRange(r,c).getValue() === ""){
      sh.getRange(r,thamphan_col).setValue("")}
    else{
      sh.getRange(r,thamphan_col).setValue(t)}
  }
}

function thamphan(){
  var res = filterData();
  var len = res.length;
  if (len !== 0){
    var rws = Math.floor(Math.random()*len);
    return res[rws][1];
  }
}

function filterData() {
  var d = data_range.getValues();
  var resultArray = d.filter(c=>{return c[2] === false;
    }).map(v=>{return [v[0],v[1]];});
  return resultArray
}

function transpose(a){
  return Object.keys(a[0]).map(c=>{ return a.map(r=>{ return r[c]; }); });
}

function nhaplieu() {
  var r = ds.getLastRow();
  var a = transpose(form_range.getValues());
  var b = new Array();
  b = a[0];
  b.unshift(null,thamphan());
  ds.appendRow(b);
  form_range.offset(1,0,form_range.getNumRows()-1).clearContent();
}
Nhấp chuột vào đây để mở rộng...
Đỉnh vãi. E ngồi mò mò, sửa sửa mấy ngày nay không ra :(( Em cảm ơn bác rất nhiều !
 
Các bạn giúp mình với!
Hiện tại mình đang thiết kế 1 file đánh giá giờ dạy trong trường học muốn dùng Script trên GoogleSheet. Cụ thể gồm 2 Sheet:
1. Sheet Form nhập liệu:
1721629987900.png1721629987900.png
2. Sheet Data:
1721630023694.png1721630023694.png
Ý tưởng của mình là:
1. Gõ mã tiết vào ô C4 để tìm kiếm sau đó người dùng sẽ bổ sung Môn học, Tiết theo PPCT, HS vắng, Tên bài, Nhận xét, Đánh giá, Họ tên sau đó sẽ cập nhật. Dữ liệu sẽ chuyển sang Sheet Data.
2. Không cho sửa các ô C6,C8,E6,E8 trên FORM
3. Chỉ cập nhật được khi kết quả Tìm kiếm là có
4. Chỉ cập nhật được trước 12h00 của ngày đánh giá ( Vì giáo viên dạy xong phải đánh giá giờ dạy luôn).
Đây là file và đoạn code mình làm nhưng không chạy như ý.
Cảm ơn các bạn nhiều!
Code:

//Search data========================

var SPREADSHEET_NAME = "Data";
var SEARCH_COL_IDX = 0;
var RETURN_COL_IDX = 0;

function searchStr() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form"); //Form Sheet

var str = formSS.getRange("C4").getValue();
var values = ss.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;
if (row[SEARCH_COL_IDX] == str) {

formSS.getRange("C4").setValue(row[0]) ;
formSS.getRange("C6").setValue(row[1]);
formSS.getRange("E6").setValue(row[2]);
formSS.getRange("C8").setValue(row[3]);
formSS.getRange("E8").setValue(row[4]);
formSS.getRange("G8").setValue(row[5]);
formSS.getRange("C10").setValue(row[6]);
formSS.getRange("C12").setValue(row[7]);
formSS.getRange("C14").setValue(row[8]);
formSS.getRange("C16").setValue(row[9]);
formSS.getRange("C18").setValue(row[10]);
formSS.getRange("C20").setValue(row[11]);

return row[RETURN_COL_IDX];

}
}
}

//Update====================

function updateData() {

var SPREADSHEET_NAME = "Data";
var SEARCH_COL_IDX = 0;
var RETURN_COL_IDX = 0;

var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form"); //Form Sheet
var datasheet = ss.getSheetByName("Data"); //Data Sheet


var str = formSS.getRange("C4").getValue();
var values = ss.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;
if (row[SEARCH_COL_IDX] == str) {
var INT_R = i+1

var values1 = [[
formSS.getRange("G8").getValue(),
formSS.getRange("C10").getValue(),
formSS.getRange("C12").getValue(),
formSS.getRange("C14").getValue(),
formSS.getRange("C16").getValue(),
formSS.getRange("C18").getValue(),
formSS.getRange("C20").getValue()
]];

datasheet.getRange(INT_R, 1, 1, 8).setValues(values1);
SpreadsheetApp.getUi().alert(' "Bạn đã hoàn thành đánh giá giờ học "');
return row[RETURN_COL_IDX];
}
}
}
 

File đính kèm

  • TKB 2024-2025(22.7).xlsx
    891.4 KB · Đọc: 1
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

MinhKhai
  • Question
Nhờ sửa lỗi gửi email bằng CDO
Trả lời
6
Đọc
243
nguoiconxunui
N
changchang2525
bài tập excel 2024
Trả lời
10
Đọc
376
nttrung_proneu
nttrung_proneu
nttrung_proneu
Xin ví dụ về hàm TEXT
    • Thích
Trả lời
18
Đọc
132
ptm0412
ptm0412
GPE-Trợ Lý
  • Dán lên cao
[KHẨN] HƯỚNG VỀ ĐỒNG BÀO CHỊU ẢNH HƯỞNG CỦA BÃO YAGI - BẢO YÊN, LÀO CAI
    • Thích
    • Cảm ơn
    • Yêu thích
3 4 5
Trả lời
89
Đọc
4K
PhanTuHuong
PhanTuHuong
T
Lọc dữ liệu mã trùng và lấy dữ liệu có số tuổi cao nhất
    • Thích
Trả lời
6
Đọc
116
hongminh_0306
H
befaint
[ Python và ứng dụng ] Tải hàng loạt tờ khai Thuế từ website thuedientu
    • Thích
    • Yêu thích
    • Cảm ơn
Trả lời
10
Đọc
433
ptm0412
ptm0412
Maika8008
  • Đã giải quyết
[Chia sẻ] Dùng VBA trộn dữ liệu Excel sang file Word mẫu (tương tự chức năng Mail Merge)
    • Thích
    • Yêu thích
    • Cảm ơn
24 25 26
Trả lời
506
Đọc
44K
HeSanbi
HeSanbi
T
Excel 365 bị treo mỗi khi copy và dán
Trả lời
5
Đọc
58
HeSanbi
HeSanbi
H
  • Question
Nhờ giúp sửa code tách ngày theo tuần trong tháng.
    • Buồn
Trả lời
7
Đọc
127
HUONGHCKT
H
J
Hướng dẫn Ranking xếp hạng mà data ở các vùng khác nhau?
Trả lời
8
Đọc
313
bebo021999
B
ongke0711
  • Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
4 5 6
Trả lời
105
Đọc
5K
ongke0711
ongke0711
Trojan
File excel bị lỗi không mở name manager được
Trả lời
12
Đọc
288
HeSanbi
HeSanbi
T
Tìm kiếm những sản phẩm Đạt trong ngày
Trả lời
15
Đọc
415
LuongKPhuong
L
N
Tạo, sử dụng COM DLL viết bằng Visual C# .NET trong VBA
    • Thích
    • Yêu thích
Trả lời
1
Đọc
209
phuongnam366377
phuongnam366377
T
File hỗ trợ lập Tờ khai thuế và các mẫu biểu hồ sơ địa chính xã/phường/thị trấn
    • Thích
Trả lời
1
Đọc
80
Maika8008
Maika8008
TrinhThiHai_Kt
Xóa bỏ dấu cách xuống dòng (Alt + Enter) trong 1 ô excel?
Trả lời
15
Đọc
51K
thedang266
thedang266
H
Xin hỏi cách in 1 trang ngang xen kẻ các trang dọc trong 1 sheet!
Trả lời
5
Đọc
147
hieu998
H
H
Bị lỗi Hàm IRR lỗi #NUM mình không biết làm thế nào
Trả lời
2
Đọc
68
hienxh_1979
H
D
Bị lỗi mất hình ảnh khi gửi file excell có chèn link ảnh cho người khác
    • Cười
Trả lời
1
Đọc
110
snow25
snow25
P
lặp lại có điều kiện trong excel
Trả lời
16
Đọc
470
CHAOQUAY
C
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom