Apps Script - Một số lệnh thường dùng trong Google Sheets

[huynhbuutam]

Đầu tiên, chúng ta mở bảng tính cần thực hiện bằng Google Sheet, sau đó chọn lệnh Extensions > Apps Script
Một cửa số mới hiện ra, chúng ta xoá toàn bộ nội dung hiện có, và nhập toàn bộ mã lệnh dưới đây vào vùng soạn thảo mã lệnh trong tệp Code.gs
Cuối cùng nhấn nút Save. Như vậy chúng ta đã nạp xong phần mã lệnh vào bảng tính của mình.
Để các lệnh này hoạt động, chúng ta cần chọn hàm onOpen, sau nó nhấn lệnh Run. Việc tiếp theo là xác nhận các cấp phép cho mã lệnh vừa tạo.
Mọi người có thể điều chỉnh lại các lệnh sao cho phù hợp với yêu cầu của mình nhé

Mã: Chọn tất cả

function onOpen() {
	var menu = SpreadsheetApp.getUi().createMenu('My Menu');
	menu.addItem('Sub-Menu 1', 'MyFunction1').addToUi();
	menu.addItem('Sub-Menu 2', 'MyFunction2').addToUi();
	menu.addItem('Sub-Menu 3', 'MyFunction3').addToUi();
}

function MyFunction1() {
	/*
	var excel_app = SpreadsheetApp.create('My Spreadsheet');
	var excel_id = excel_app.getId();
	var excel = SpreadsheetApp.openById(excel_id);
	var s = excel.getActiveSheet();
	*/
	var excel = SpreadsheetApp.getActiveSpreadsheet();
	var s = excel.getActiveSheet();
	s.setName('My Sheet');
	s.deleteColumns(17, 8);
	s.deleteRows(100,900);
	s.setColumnWidth(1, 110);
	s.setColumnWidth(2, 400);
	s.setColumnWidths(3, 16, 110);
	s.setFrozenColumns(2);
	s.setFrozenRows(3);
	s.getRange('A4:A100').setDataValidation(SpreadsheetApp.newDataValidation()
		.setAllowInvalid(false)
		.requireValueInList(['ACCEPTANCE', 'CHECKBOX', 'CHECKGRID', 'CHOICE', 'DATE', 'GRID', 'IMAGE1', 'IMAGE2', 'LIST', 'PAGE', 'PARAGRAPH', 'SCALE', 'SECTION', 'TEXT', 'TIME', 'VIDEO'], true)
		.build());
	s.getRange('A1:A100').setFontWeight('bold').setHorizontalAlignment('center');
	s.getRange('A1').setValue('EXERCISE');
	s.getRange('A2').setValue('DESCRIBE');
	s.getRange('A3').setValue('TYPE');
	s.getRange('C1').setValue('FOLDER ID:').setFontWeight('bold').setHorizontalAlignment('right');
	s.getRange('E1').setValue('PUBLIC URL:').setFontWeight('bold').setHorizontalAlignment('right');
	s.getRange('G1').setValue('COPY FORM:').setFontWeight('bold').setHorizontalAlignment('right');
	s.getRange('H1').setDataValidation(SpreadsheetApp.newDataValidation()
		.setAllowInvalid(false)
		.requireValueInList(['YES'], true)
		.build());
	s.getRange('I1').setValue('LINK COPY:').setFontWeight('bold').setHorizontalAlignment('right');
	s.getRange('K1').setValue('SHUFFLE:').setFontWeight('bold').setHorizontalAlignment('right');
	s.getRange('L1').setDataValidation(SpreadsheetApp.newDataValidation()
		.setAllowInvalid(false)
		.requireValueInList(['YES'], true)
		.build());
	s.getRange('B3').setValue('QUESTION').setFontWeight('bold').setHorizontalAlignment('center');
	s.getRange('C3').setValue('INSTRUCTIONS').setFontWeight('bold').setBackground('#c4daea').setHorizontalAlignment('center');
	s.getRange('D3').setValue('POINTS').setFontWeight('bold').setBackground('#ffff66').setHorizontalAlignment('center');
	s.getRange('E3').setValue('TEXT TRUE').setFontWeight('bold').setBackground('#00ffff').setHorizontalAlignment('center');
	s.getRange('F3').setValue('TEXT FALSE').setFontWeight('bold').setBackground('#00ffff').setHorizontalAlignment('center');
	s.getRange('G3').setValue('LINK').setFontWeight('bold').setBackground('#00ffff').setHorizontalAlignment('center');
	s.getRange('H3').setValue('LINK TEXT').setFontWeight('bold').setBackground('#00ffff').setHorizontalAlignment('center');
	s.getRange('I3:R3').setValue('OPTION').setFontWeight('bold').setBackground('#d4dee5').setHorizontalAlignment('center');
	s.getRange('E4:H100').setBackground('#00ffff');
	s.getRange('I4:R100').setBackground('#d4dee5');
	s.getRange('C4:C100').setBackground('#c4daea');
	s.getRange('D4:D100').setBackground('#ffff66');
};

function MyFunction2() {
	var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	AnotherFunction(s);   
}

function MyFunction3() {
	var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets();
	for (var k = 0; k < sh.length; k++) {  
		var s = sh[k];
		AnotherFunction(s);
	}
} 

function AnotherFunction(s) {
	var r = s.getDataRange();
	var nr = r.getNumRows();
	var nc = r.getNumColumns();
	var lr = s.getLastRow();
	var lc = s.getLastColumn();
	var d = r.getValues();
	
	for (var x = 0; x < nr; x++) {
		var i = d[x][0];
		var cr = 1 + x;
		var ro = s.getRange(cr, 8, 1, 10);
		var op = ro.getValues();
		
		if (i == '') {
			continue;
		} else if (i == 'CHOICE') {
			var arr = [];
						
			for (var ccc = 8; ccc < nc; ccc++) {
				var cu = 1 + ccc;
				var bgcolor = s.getRange(cr, cu, 1, 1).getBackground();
				
				if (s.getRange(cr, cu, 1, 1).getValue() !== '' && bgcolor === "#00ff00") {
					var q1 = d[x][ccc] + " (Correct)";
					arr.push(q1);
				} else if (s.getRange(cr, cu, 1, 1).getValue() !== '' && bgcolor !== "#00ff00") {
					var q1 = d[x][ccc];
					arr.push(q1);
				}
			}
			
			Browser.msgBox(arr);
		}
	}
}

Giải thích từng lệnh
- Tạo bảng chọn chính có tiêu đề là "My Menu" (Lưu ý: Việc tạo bảng chọn chỉ áp dụng trong trường hợp mã lệnh được đính kèm bên trong bảng tính, điều này đồng nghĩa với việc chúng ta cần mở bảng tính bằng Google Sheets mới có thể chạy được các lệnh liên quan đến việc tạo các bảng chọn)

Mã: Chọn tất cả

var menu = SpreadsheetApp.getUi().createMenu('My Menu');

- Thêm lệnh con có tiêu đề là "Sub-Menu 1", khi chọn lệnh này sẽ thực thi hàm "MyFunction1"

Mã: Chọn tất cả

menu.addItem('Sub-Menu 1', 'MyFunction1').addToUi();

- Tạo bảng tính mới với tên là "My Spreadsheet"

Mã: Chọn tất cả

var excel_app = SpreadsheetApp.create('My Spreadsheet');

- Lấy mã bảng tính

Mã: Chọn tất cả

var excel_id = excel_app.getId();

- Lấy bảng tính có mã là giá trị của biến excel_id để xử lí

Mã: Chọn tất cả

var excel = SpreadsheetApp.openById(excel_id);

- Lấy bảng tính hiện đang mở để xử lí

Mã: Chọn tất cả

var excel = SpreadsheetApp.getActiveSpreadsheet();

- Lấy trang tính hiện đang mở để xử lí

Mã: Chọn tất cả

var s = excel.getActiveSheet();

- Đặt tên cho trang tính là "My Sheet"

Mã: Chọn tất cả

s.setName('My Sheet');

- Xóa 8 cột, bắt đầu từ cột thứ 17

Mã: Chọn tất cả

s.deleteColumns(17, 8);

- Xóa 900 hàng, bắt đầu từ hàng thứ 100

Mã: Chọn tất cả

s.deleteRows(100,900);

- Đặt cho cột thứ 1 với độ rộng là 110px

Mã: Chọn tất cả

s.setColumnWidth(1, 110);

- Đặt cho cột thứ 3 đến cột thứ 16 với độ rộng là 110px

Mã: Chọn tất cả

s.setColumnWidths(3, 16, 110);

- Đóng băng 2 cột đầu tiên

Mã: Chọn tất cả

s.setFrozenColumns(2);

- Đóng băng 3 hàng đầu tiên

Mã: Chọn tất cả

s.setFrozenRows(3);

- Lấy khối A4:A100, sau đó đặt kiểm tra dữ liệu với tùy chọn như sau:
+ Không cho phép nhập sai
+ Yêu cầu nhập dữ liệu theo các giá trị sau với tùy chọn hiển thị bảng chọn: ACCEPTANCE, CHECKBOX, CHECKGRID, CHOICE, DATE, GRID, IMAGE1, IMAGE2, LIST, PAGE, PARAGRAPH, SCALE, SECTION, TEXT, TIME, VIDEO
+ Áp dụng các thiết lập

Mã: Chọn tất cả

s.getRange('A4:A100').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInList(['ACCEPTANCE', 'CHECKBOX', 'CHECKGRID', 'CHOICE', 'DATE', 'GRID', 'IMAGE1', 'IMAGE2', 'LIST', 'PAGE', 'PARAGRAPH', 'SCALE', 'SECTION', 'TEXT', 'TIME', 'VIDEO'], true)
  .build());

- Lấy khối khối A1:A100, sau đó định dạng như sau:
+ Đặt kiểu chữ in đậm
+ Căn giữa nội dung

Mã: Chọn tất cả

s.getRange('A1:A100').setFontWeight('bold').setHorizontalAlignment('center');

- Lấy ô A1, sau đó đặt dữ liệu là EXERCISE

Mã: Chọn tất cả

s.getRange('A1').setValue('EXERCISE');

- Lấy khối E4:H100, sau đó đặt màu nền là #00ffff

Mã: Chọn tất cả

s.getRange('E4:H100').setBackground('#00ffff');

- Lấy tất cả các trang tính để xử lí

Mã: Chọn tất cả

var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets();

- Lấy địa chỉ khối có chứa toàn bộ dữ liệu trong trang tính

Mã: Chọn tất cả

var r = s.getDataRange();

- Lấy số lượng hàng trong khối

Mã: Chọn tất cả

var nr = r.getNumRows();

- Lấy số lượng cột trong khối

Mã: Chọn tất cả

var nc = r.getNumColumns();

- Lấy vị trí hàng cuối cùng

Mã: Chọn tất cả

var lr = s.getLastRow();

- Lấy vị trí cột cuối cùng

Mã: Chọn tất cả

var lc = s.getLastColumn();

- Lấy mảng dữ liệu từ khối

Mã: Chọn tất cả

var d = r.getValues();

- Lấy khối bắt đầu từ ô tại tọa độ (giá trị của biến cr, 8), lấy đến 1 hàng và 10 cột tiếp theo

Mã: Chọn tất cả

var ro = s.getRange(cr, 8, 1, 10);

- Lấy màu nền của ô đầu tiên, trong khối bắt đầu từ ô tại tọa độ (giá trị của biến cr, giá trị của biến cu), lấy đến 1 hàng và 1 cột tiếp theo

Mã: Chọn tất cả

var bgcolor = s.getRange(cr, cu, 1, 1).getBackground();

Chi tiết tất cả các lệnh xem tại:
+ Các lệnh liên quan đến giao diện người dùng: https://developers.google.com/apps-scri ... ce/base/ui
+ Các lệnh chung liên quan đến bảng tính: https://developers.google.com/apps-scri ... dsheet-app
+ Các lệnh liên quan đến trang tính: https://developers.google.com/apps-scri ... heet/sheet
+ Các lệnh liên quan đến vùng chọn: https://developers.google.com/apps-scri ... heet/range
+ Các lệnh liên quan đến cài đặt kiểm tra dữ liệu: https://developers.google.com/apps-scri ... on-builder