1. สร้างชีต
2. สร้างโฟลเดอร์ตามจำนวนผู้ใช้ ใส่ไอดีโฟลเดอร์ของผู้ใช้ลงในชีต
3. แถบเมนู > ส่วนขยาย > App Script
4. สร้างไฟล์
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1>Upload file</h1>
<form id="myform" onsubmit="formSubmit(this)">
<label style="font-size: 20px" >File</label><br>
<input type="file" name="file" accept=".pdf,application/pdf" /><br><br>
<label style="font-size: 20px" >User Name</label><br>
<select name="user" id="user" style="font-size: 20px" >
<?= getMenuUser().forEach(function(item) { ?>
<option value="<?= item ?>" ><?= item ?></option>
<? }) ?>
</select><br><br>
<input type="submit" name="submitButton" value="Submit" />
<span style="font-size: 20px" ></span>
</form>
<script>
function formSubmit(obj){
event.preventDefault();
google.script.run.withSuccessHandler((output)=>{
alert(output)
document.getElementById('myform').reset()
}).uploadFile(obj);
}
</script>
</body>
</html>
code.gs
function doGet(e) {
const htmlOutput = HtmlService.createTemplateFromFile('index')
return htmlOutput.evaluate();
}
function getMenuUser() {
const data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data").getRange("A2:A").getDisplayValues().filter(r => r[0])
return data
}
function uploadFile(obj) {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data")
const data = sheet.getDataRange().getDisplayValues().slice(1)
const item = data.findIndex(r => {return r[0] === obj.user})
const folder = DriveApp.getFolderById(data[item][1]);
const datax = data[item].filter(r => { return (r)})
const lastCol = datax.length +1
const rowRecord = sheet.getRange(item+2,lastCol,1,1)
//Upload file if exists and update the file url
if (obj.file.length > 0) {
const blob = obj.file;
const file = folder.createFile(blob);
file.setDescription("Uploaded by " + obj.user);
const fileUrl = file.getUrl();
const fileName = file.getName();
rowRecord.setFormula(`=HYPERLINK("${fileUrl}", "${fileName}")`)
return "Saved successfully";
} else{
return "Record saved without a file";
}
} catch (error) {
return error.toString();
}
}
