This is the follow-up to my previous question for which @Tanaike proposed a very good solution. Here is a bit explanation of workflow.
- Script runs using onEdit() trigger in Google sheets
- A dialog box pop-up in sheet where the user uploads an image from a computer/laptop
- This image will go to Google drive and the image link will be imported into a Google sheet cell.
Here is the image of the dialog box which pops up in google sheets on script run to upload images:
Here is the code in Code.gs
function addImage() {
var filename = 'Row';
var htmlTemp = HtmlService.createTemplateFromFile('Index');
htmlTemp.fName = filename;
htmlTemp.position = 2;
var html = htmlTemp.evaluate().setHeight(96).setWidth(415);
var ui = SpreadsheetApp.getUi();
ui.showModalDialog(html, 'Upload');
}
function upload(obj, rowNum) {
var newFileName = obj[2];
var blob = Utilities.newBlob(...obj);
var upFile = DriveApp.getFolderById('[folderid]').createFile(blob).setName(newFileName);
var fileUrl = upFile.getUrl();
var urlCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(rowNum, 5);
urlCell.setValue('=HYPERLINK("' + fileUrl + '","View image")');
return "Done.";
}
Here is the code in HTML & Javascript side: Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_center">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
</head>
<body>
<form id="myForm">
Please upload image below.<br /><br />
<input type="hidden" name="fname" id="fname" value="<?= fName ?>"/>
<input type="hidden" name="position" id="position" value="<?= position ?>"/>
<input type="file" name="file" id="file" accept="image/jpeg,.pdf" />
<input type="button" value="Submit" class="action" onclick="formData(this.parentNode)" />
<input type="button" value="Close" onclick="google.script.host.close()" />
</form>
<script>
//Disable the default submit action using “func1”
window.onload=func1;
function func1() {
document.getElementById('myForm').addEventListener('submit', function(event) {
event.preventDefault();
});
}
function formData(obj) {
const file = obj.file.files[0];
const fr = new FileReader();
fr.readAsArrayBuffer(file);
fr.onload = f =>
google.script.run.withSuccessHandler(closeIt).upload([[...new Int8Array(f.target.result)], file.type, obj.fname.value], obj.position.value);
}
function closeIt(e){
console.log(e);
google.script.host.close();
};
</script>
</body>
</html>
This script works perfectly when google sheets is opened in the web browser(chrome), but I want to run this in android based google sheet app, in the google sheet app, this script is fired using the onEdit() trigger but a dialog box does not show up where the user can upload the image from mobile as well.
is there any workaround to upload the image from the Google sheet app as we do with the usual google sheets in the chrome browser? I hope I was clear in explaining my issue. Thank you
