I have a function that uploads the image to Google Drive through Google Sheets and creates its link in Google Sheets Cell. Here is the code snippet:
function upload(obj) {
var file = DriveApp.getFolderById("###").createFile(obj.upload);
var activeSheet = SpreadsheetApp.getActiveSheet();
var File_name = file.getName()
var value = 'hyperlink("' + file.getUrl() + '";"' + File_name + '")'
var activeSheet = SpreadsheetApp.getActiveSheet();
activeSheet.getRange("B2").setFormula(value)
return {
fileId: file.getId(),
mimeType: file.getMimeType(),
fileName: file.getName(),
};
}
function onChecked(e) {
if (e.range.getA1Notation() === 'I1' && e.value === 'TRUE') {
var html = HtmlService.createHtmlOutputFromFile('upload');
SpreadsheetApp.getUi().showModalDialog(html, 'Upload File');
}
}
I have set up onEdit trigger on onChecked(e) function that whenever checkbox in cell I1 is checked this function will be triggered to upload image in Google Drive. The purpose of setting this as on Edit trigger is that:
Users do not need to authorize the script/scopes as they are not technical. So I authorized the scopes of the script during trigger installation.
It works fine for me but when any other user except me checks this checkbox, it does not upload the image. It means upload function is not executed by any other user. Here is upload.html file:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<form> <!-- Modified -->
<div id="progress" ></div>
<input type="file" name="upload" id="file">
<input type="button" value="Submit" class="action" onclick="form_data(this.parentNode)" >
<input type="button" value="Close" onclick="google.script.host.close()" />
</form>
<script>
function form_data(obj){ // Modified
google.script.run.withSuccessHandler(closeIt).upload(obj);
};
function closeIt(e){ // Modified
console.log(e);
google.script.host.close();
};
</script>
</body>
</html>
To summarize the issue, this function works fine for me but when other users try to run it, it only executes onChecked function, but the upload() function does not run. Any help in this regard would be greatly appreciated.
I believe your goal is as follows.
onChecked has already been installed as the OnEdit trigger.google.script.run, the user is required to authorize the scopes of the script.https://www.googleapis.com/auth/drive.file and https://www.googleapis.com/auth/script.container.ui are required to be used.When these points are reflected in a sample script, how about the following sample script?
appsscript.jsonPlease update your appsscript.json as follows.
{
"timeZone": "###", <--- this is your timezone.
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "Drive",
"version": "v2",
"serviceId": "drive"
},
{
"userSymbol": "Sheets",
"version": "v4",
"serviceId": "sheets"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/script.container.ui"
]
}
Please copy and paste the following script to the script editor of Google Spreadsheet.
Please reinstall onChecked as the OnEdit trigger again. By this, 2 scopes are authorized.
function onChecked(e) {
if (e.range.getA1Notation() === 'I1' && e.value === 'TRUE') {
var html = HtmlService.createTemplateFromFile('upload');
html.token = ScriptApp.getOAuthToken();
html.spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
html.folderId = "###"; // Please set your folder ID.
SpreadsheetApp.getUi().showModalDialog(html.evaluate(), 'Upload File');
}
}
Please set your folder ID and Spreadsheet ID to folderId and spreadsheetId.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js/resumableupload_js.min.js"></script>
</head>
<body>
<form>
<input type="file" name="upload" id="file">
<input type="button" value="Submit" class="action" onclick="form_data(this.parentNode)" >
<input type="button" value="Close" onclick="google.script.host.close()" />
</form>
<div id="progress"></div>
<script>
function form_data(obj) {
const file = obj.upload.files[0];
if (file.name != "") {
let fr = new FileReader();
fr.fileName = file.name;
fr.fileSize = file.size;
fr.fileType = file.type;
fr.readAsArrayBuffer(file);
fr.onload = resumableUpload;
}
}
function resumableUpload(e) {
document.getElementById("progress").innerHTML = "Initializing.";
const f = e.target;
const resource = {
fileName: f.fileName,
fileSize: f.fileSize,
fileType: f.fileType,
fileBuffer: f.result,
accessToken: "<?!= token ?>",
folderId: "<?!= folderId ?>",
};
const ru = new ResumableUploadToGoogleDrive();
ru.Do(resource, function (res, err) {
if (err) {
console.log(err);
return;
}
console.log(res);
let msg = "";
if (res.status == "Uploading") {
msg = Math.round((res.progressNumber.current / res.progressNumber.end) * 100) + "%";
} else {
msg = res.status;
}
document.getElementById("progress").innerText = msg;
if (res.result) {
const fileUrl = "https://drive.google.com/file/d/" + res.result.id + "/view?usp=drivesdk";
const formula = `=hyperlink("${fileUrl}";"${f.fileName}")`;
const url = "https://sheets.googleapis.com/v4/spreadsheets/<?!= spreadsheetId ?>/values/B2?valueInputOption=USER_ENTERED";
fetch(url, {
method: "PUT",
headers: {
authorization: "Bearer " + resource.accessToken,
"Content-Type": "application/json"
},
body: JSON.stringify({values: [[formula]]})
})
.then(response => response.json())
.then(data => {
console.log(data);
google.script.host.close();
})
.catch(error => console.error(error));
}
});
}
</script>
</body>
</html>
When you check the checkbox of "I1", the dialog is opened. And, please upload a file. By this, the file is put into the folder to folderId, and the file URL is put into "B2".
If you don't want to use your access token, I think that the service account can be also used. But, at that time, please modify the above script.
When I tested this script, I confirmed that the user who is not the owner of Spreadsheet uploads a file, the file could be correctly uploaded and the file URL is put into "B2".
In this case, the user cannot open the uploaded file because the owner of the file is not the user. But, if the folder of folderId is shared with the user, the user can open the file.