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

P
Nhờ giúp đỡ hàm lọc dữ liệu theo tỉnh
Trả lời
2
Đọc
10
Hoàng Tuấn 868
H
20050911
File Excel lỗi ''The file could not be accessed. Try one of the following:.."
Trả lời
3
Đọc
52
20050911
20050911
ongke0711
  • Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
11 12 13
Trả lời
249
Đọc
13K
ongke0711
ongke0711
B
Định dạng condition formatting, đếm hỗn hợp dữ liệu merge và không merge
Trả lời
17
Đọc
183
batista911
B
P
Tổng hợp dữ liệu từ các sheet theo chọn lọc
Trả lời
16
Đọc
298
Hoàng Tuấn 868
H
Tuấn Bụng Bự
  • Question
Tìm Bộ 8 số xuất hiện nhiều nhất trong random( 1 - 80)
2
Trả lời
30
Đọc
668
huuthang_bd
huuthang_bd
H
Tính và sắp xếp theo thư tự tăng dần có điều kiệu (có dữ liệu trùng nhau)
Trả lời
1
Đọc
26
bebo021999
B
V
  • Question
Lấy dữ liệu lãi suất từ website Sbv.gov.vn bằng Power Query
Trả lời
1
Đọc
487
Minhhanhsgu
M
Nguyễn Duy Tuân
  • Question
Chia sẻ ControlForOffice - Bộ công cụ điều khiển Office/Excel
    • Thích
6 7 8
Trả lời
149
Đọc
95K
Nguyễn Duy Tuân
Nguyễn Duy Tuân
B
Lọc dữ liệu trùng, đếm số lần, tự tô màu theo điều kiện.
Trả lời
5
Đọc
136
batista911
B
N
Vẽ biểu đồ kết hợp nhiều yếu tố trong excel
Trả lời
2
Đọc
95
Ny Ny 2024
N
C
Chia sẻ: Kết quả xổ số Keno
    • Cười
    • Thích
Trả lời
9
Đọc
271
ptm0412
ptm0412
T
Tối ưu hóa hàm lấy dữ liệu từ bảng Tổng sang bảng chi tiết
Trả lời
7
Đọc
234
Hoàng Tuấn 868
H
nguyenphuochien166
Cách Vlookup hoặc cách lấy thông tin dữ liệu có dấu mũ lũy thừa?
    • Cười
Trả lời
6
Đọc
116
tigertiger
tigertiger
T
Không đếm dữ liệu rỗng trong PivotTable
Trả lời
8
Đọc
1K
hoangtuaotrang_hp_vn
H
C
Công thức tự nhập liệu số lượng nhập khi bị âm
Trả lời
1
Đọc
98
hoangtuaotrang_hp_vn
H
kimtanmap
  • Question
Nhờ viết code VBA Macro lập lại.
Trả lời
5
Đọc
171
kimtanmap
kimtanmap
dung09031989
Xin giúp công thức tô màu các ô phía sau khi kế hoạch đặt hàng thiếu so với tồn kho.?
Trả lời
1
Đọc
86
hoangtuaotrang_hp_vn
H
huynhnhu1234
Tìm giá vé máy bay một chiều và khứ hồi
Trả lời
0
Đọc
60
huynhnhu1234
huynhnhu1234
V
[Nhờ giúp đỡ] Cách tìm thời gian bị trùng trong excel ở nhiều thời gian khác nhau
Trả lời
7
Đọc
136
tranvansau278
T
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