Google sheet Apps Script Tạo Popup Upload file lên Onedrive và Google sheet
Trong project này, mình chia sẻ đến các bạn cách sử dụng Google sheet Apps Script Tạo Popup Upload file lên Onedrive và Google sheet. Các file tải lên được chứa trong một folder trong Onedrive. Các thông tin của file như: Ghi chú, tên file, dung lượng file và url của file được đưa vào Google sheet. Dưới đây là code của các file trong Apps script. Bạn có thể xem thêm video hướng dẫn bên dưới để hiểu.
Code của file “Code.gs”
function onOpen(){
SpreadsheetApp.getUi()
.createMenu('📤 UPLOAD')
.addItem("📄 Upload File", "createDialog")
.addToUi();
}
function createDialog(){
var template = HtmlService.createTemplateFromFile("popup")
.evaluate().setTitle("Tool Upload File").addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setHeight(450).setWidth(400);
const ui = SpreadsheetApp.getUi();
ui.showDialog(template);
}
function saveData(obj) {
var folderId = "1YMl8FqUAUODfQTIYtSosHg8SCq-IHHhf";
console.log(obj)
var data = Utilities.base64Decode(obj.data);
var blob = Utilities.newBlob(data, obj.type, obj.name);
var destination = DriveApp.getFolderById(folderId);
var createFile = destination.createFile(blob);
var linkFile = createFile.getUrl();
var sizeFileTemp = createFile.getSize();
var sizeFile = (sizeFileTemp/1000).toString() + " KB";
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data')
.appendRow([obj.input1,obj.name,sizeFile,linkFile]);
return true;
};
Code file “popup.html”
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<title>Giaoan.link chia sẻ tài liệu miễn phí</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"
integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
<style>
body {
background: #ffffff;
}
.frame{
//box-shadow: 10px 10px 15px rgb(193,182,182), -6px 6px 15px #eee;
padding: 20px;
max-width: 480px;
margin: 0px auto;
margin-top: 10px;
border-radius: 7px;
background: white;
}
button {
text-transform: uppercase;
transition: transform 80ms ease-in;
border-radius: 7px;
}
button:active{
transform: scale(0.95);
color: white
}
.btn.focus, .btn:focus {
outline: 0;
box-shadow: none!important;
color: white;
}
.btn-info: hover {
background-color: gold;
color: white;
border-color: gold;
}
.btn-info {color: white;}
.boxTitle {
border: 1px solid rgba(0,0,0,0.2);
border-radius: 40px 0px 40px 0px;
padding: 1.5rem;
margin-left: 10px;
margin-right: 10px;
border-radius: 13px;
margin: auto;
width: 100%;
text-align: center;
}
.box {
border: 1px solid rgba(0,0,0,0.2);
padding: 1.5rem;
margin-left:10px;
margin-right:10px;
border-radius: 13px;
margin: auto;
width: 100%;
}
.inp {
border:none;
border-bottom: 1px solid rgba(0,0,0,0.2);
border-radius: 0px;
padding: 5px 10px;
outline: none;
}
[placeholder]:focus::-webkit-input-placeholder {
transition: text-indent 0.4s 0.4s ease;
text-indent: -100%;
opacity: 1;
}
.inp.focus, .inp:focus {
outline: 0;
box-shadow: none!important;
/* color: green; */
border-bottom: 1px solid rgba(0,0,0,0.2);
}
</style>
</head>
<body>
<div class="container frame">
<div class="row">
<!-- create form element here -->
<div class="form-group mb-2 box">
<input type="text" class="form-control inp mb-3" id="input1" placeholder="Viết ghi chú!" autocomplete="off">
</div>
<div class="form-group mb-2 box">
<div class="mb-3" id="myForm">
<label for="myFile" class="form-label">Tải File của bạn.</label>
<input class="form-control-file" type="file" id="myFile" name="myFile" onchange="LoadFile(event)">
</div>
<center>
<div>
<p>
<img class="form-control-file mt-4" style="max-width: 100px; margin: 0px auto; display: none; border: 0px solid #555555;" src="" id="img" alt="from phone">
</p>
</div>
</center>
</div>
<!-- create form element until here -->
<div class="col text-center">
<button type="button" id="submitButton" class="btn btn-info">TẢI LÊN</button>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.1/dist/umd/popper.min.js" integrity="sha384-SR1sx49pcuLnqZUnnPwx6FCym0wLsk5JZuNx2bPPENzswTNFaQU1RDvt3wT4gWFG" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/js/bootstrap.min.js" integrity="sha384-j0CNLUeiqtyaRmlzUHCPZ+Gy5fQu0dQ6eZ/xAww941Ai1SxSY+0EQqNXNE6DZiVc" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@10"></script>
<script>
let data;
let name;
let type;
function LoadFile(event)
{
var file = event.target.files[0];
var reader = new FileReader();
reader.onload = function(e) {
console.log(e.target.result);
var fileData = e.target.result.substr(e.target.result.indexOf(",")+1);
var mimeTypeStart = e.target.result.indexOf("data:") + 5;
var mimeTypeEnd = e.target.result.indexOf(";");
var mimeType = e.target.result.substr(mimeTypeStart, mimeTypeEnd - mimeTypeStart);
var fileName = file.name;
data = fileData;
name = fileName;
type = mimeType;
};
reader.readAsDataURL(file);
};
document.addEventListener('DOMContentLoaded', (ev) =>{
showFileIcon('myFile','img')
});
function showFileIcon(fileID, imgID){
let input = document.getElementById(fileID);
input.addEventListener('change', (ev) =>{
console.dir( input.files[0] );
let img = document.getElementById(imgID);
img.style.display = "block";
img.src= input.files[0].type.indexOf("image/") > -1? window.URL.createObjectURL(input.files[0]):
input.files[0].type.indexOf("/pdf") > -1? "https://giaoan.link/images/pdf.jpg":
input.files[0].type.indexOf(".sheet") > -1? "https://giaoan.link/images/excel.jpg":
input.files[0].type.indexOf("document") > -1? "https://giaoan.link/images/word.jpg":
"https://giaoan.link/images/document.png";
})
};
document.querySelector(".btn").addEventListener("click", submitData);
function submitData(){
var input1 = document.getElementById("input1");
let upload1 = document.getElementById('myFile');
let img = document.getElementById('img');
let button = document.getElementById('submitButton');
let obj = {
input1: input1.value,
data: data,
name: name,
type: type
}
if(input1.value == "" || upload1.value == ""){
Swal.fire({title:"", text:'Vui lòng điền đầy đủ các trường!', icon: 'warning'})
}else{
button.innerHTML = "Loading...";
google.script.run
.withSuccessHandler(() => {
input1.value = "";
document.getElementsByName('myFile').forEach(function(val){ val.value = "" })
img.src = "";
img.style.display = "none";
button.innerHTML = "SUBMIT";
uploadFile = {};
Swal.fire({title:"", text:'File đã được gửi đi!', icon:'success', timer: 2500, showConfirmButton:false});
})
.saveData(obj);
}
};
</script>
</body>
</html>