Trying to make a script to upload a file and paste the downloaded file's link into the active cell of a google spreadsheet.
After clicking "Upload" in the modal window, the file is not written to Google Drive and, accordingly, the link is not written to the cell
Code.gs
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('File')
.addItem('Attach...', 'showForm')
.addToUi();
}
function showForm() {
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi().showModalDialog(html, 'Upload File');
}
function uploadFile(e) {
var newFileName = e.fileName;
var blob = e.file;
var upFile = DriveApp.getFolderById('*FolderID*').createFile(blob).setName(newFileName);
Logger.log(upFile);
var fileUrl = upFile.getUrl();
var formula = '=HYPERLINK("' fileUrl '","' newFileName '")';
SpreadsheetApp.getActiveRange().setFormula( formula );
return "Uploaded!";
}
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_center">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<form id="myForm" >
Select File: <input type="file" name="file" accept="*" /><br>
File name: <input type="text" name="fileName" /><br><br>
<input type="button" value="Upload" onclick="upload(this.parentNode);" />
</form>
<script>
window.onload=func1;
function func1() {
document.getElementById('myForm').addEventListener('submit', function(event) {
event.preventDefault();
});
}
function upload(obj){
google.script.run.withSuccessHandler(close).withFailureHandler(close).uploadFile(obj);
}
function close(e) {
console.log(e);
google.script.host.close();
}
</script>
</body>
</html>
CodePudding user response：
Issue and workaround:
On December 9, 2021, the file object got to be able to be parsed from the Javascript side to the Google Apps Script side with V8 runtime. But, in this case, this can be used for only Web Apps. In the current stage, the sidebar and dialog cannot parse the file object on the Javascript side. Ref I think that this is the reason of your issue. So, in the current stage, it is required to send the file object as the string and the byte array for the sidebar and the dialog.
In this case, in order to achieve your goal using the current workaround, I would like to propose the following 2 patterns.
By the way, I think that in your Google Apps Script, an error occurs at SpreadsheetApp.getActiveCell().setFormula( formula );. Because SpreadsheetApp has no method of getActiveCell(). In this case, I think that getActiveRange() might be suitable.
Modified script:
In this modification, the file object is converted to the byte array, and the data is sent to Google Apps Script side.
Google Apps Script side:
function showForm() {
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi().showModalDialog(html, 'Upload File');
}
function uploadFile(e) {
var blob = Utilities.newBlob(...e);
var upFile = DriveApp.getFolderById('*FolderID*').createFile(blob).setName(e[2]);
Logger.log(upFile);
var fileUrl = upFile.getUrl();
var formula = '=HYPERLINK("' fileUrl '","' e[2] '")';
SpreadsheetApp.getActiveRange().setFormula(formula);
return "Uploaded!";
}
HTML & Javascript side:
<!DOCTYPE html>
<html>
<head>
<base target="_center">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<form id="myForm">
Select File: <input type="file" name="file" accept="*" /><br>
File name: <input type="text" name="fileName" /><br><br>
<input type="button" value="Upload" onclick="upload(this.parentNode);" />
</form>
<script>
window.onload = func1;
function func1() {
document.getElementById('myForm').addEventListener('submit', function(event) {
event.preventDefault();
});
}
function upload(obj) {
const file = obj.file.files[0];
// --- For your additional request, I modified below script.
const extension = function(e) {
const temp = e.split(".");
return temp.length == 1 ? "" : temp.pop();
}(file.name);
const filename = `${obj.fileName.value}.${extension}`;
// ---
const fr = new FileReader();
fr.onload = e => {
const obj = [[...new Int8Array(e.target.result)], file.type, filename];
google.script.run.withSuccessHandler(close).withFailureHandler(close).uploadFile(obj);
};
fr.readAsArrayBuffer(file);
}
function close(e) {
console.log(e);
google.script.host.close();
}
</script>
</body>
</html>
Note:
- In your script, I thought that the modified script might be a bit complicated. So, I posted the modified script as an answer.