I have two functions in Apps Script, both write values to my Google Sheet. I wanted to have them on the same row. I tried to use
sheet.getLastRow().setValues(sheet.appendRow([obj.name, obj.mimeType, obj.id, Utilities.formatDate(new Date(), "GMT 8:00", "yyyy-MM-dd'T'HH:mm:ss'Z'")])
But it didn't work.
Here's my codes, which I've tried to adapt from my previous question (Multiple bulk import entries using Google Apps Script): Code.gs
function doGet() {
return HtmlService.createTemplateFromFile('index').evaluate();
}
function getAuth() {
// DriveApp.createFile() // This is used for adding the scope of "https://www.googleapis.com/auth/drive".
return ScriptApp.getOAuthToken();
}
function update(formObject) {
SpreadsheetApp.getActiveSpreadsheet()
.getSheets()[0]
.appendRow([formObject.myName]);
}
function putFileInf(obj) {
SpreadsheetApp.getActiveSpreadsheet()
.getSheets()[0]
.appendRow([obj.name, obj.mimeType, obj.id, Utilities.formatDate(new Date(), "GMT 8:00", "yyyy-MM-dd'T'HH:mm:ss'Z'")]);
}
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="fr">
<input type="text" name="myName">
<input type="file" id="file1" />
<input type="file" id="file2" />
<input type="button" onclick="run()" value="Upload" />
</form>
<div id="progress"></div>
<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
<script>
function run() {
google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
}
function ResumableUploadForGoogleDrive(accessToken) {
const f1 = document.getElementById("file1").files[0];
const f2 = document.getElementById("file2").files[0];
const fObj = document.getElementById("fr");
[f1, f2].forEach((file, i) => {
if (!file) return;
let fr = new FileReader();
fr.fileName = file.name;
fr.fileSize = file.size;
fr.fileType = file.type;
fr.readAsArrayBuffer(file);
fr.onload = e => {
var id = "p" i;
var div = document.createElement("div");
div.id = id;
document.getElementById("progress").appendChild(div);
document.getElementById(id).innerHTML = "Initializing.";
const f = e.target;
const resource = { fileName: f.fileName, fileSize: f.fileSize, fileType: f.fileType, fileBuffer: f.result, accessToken: accessToken, folderId: "1NbwjLmYXyXjkIV1SjQL3ThnO97kssrcc" };
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) "% (" f.fileName ")";
} else {
msg = res.status " (" f.fileName ")";
}
if (res.status == "Done") {
google.script.run.update(fObj);
google.script.run.putFileInf(res.result);
}
document.getElementById(id).innerText = msg;
});
};
});
}
</script>
</body>
</html>
An example of what I have in mind: Say user enters name (myName) as Linda, then upload a photo with filename of 'pic.jpg'. I want to print out 'Linda pic.jpg' in the Google Sheet. But what I have right now is:
Linda
pic.jpg
If you have any advice, I'll be very grateful! Have a good day :))
CodePudding user response:
In your script, how about the following modification? In this modification, your HTML & javascript is modified.
Modified script:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="fr">
<input type="text" id="name" name="myName"> <!-- Modified -->
<input type="file" id="file1" />
<input type="file" id="file2" />
<input type="button" onclick="run()" value="Upload" />
</form>
<div id="progress"></div>
<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
<script>
function run() {
google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
}
function ResumableUploadForGoogleDrive(accessToken) {
const f1 = document.getElementById("file1").files[0];
const f2 = document.getElementById("file2").files[0];
const name = document.getElementById("name").value; // Modified
[f1, f2].forEach((file, i) => {
if (!file) return;
let fr = new FileReader();
fr.fileName = file.name;
fr.fileSize = file.size;
fr.fileType = file.type;
fr.readAsArrayBuffer(file);
fr.onload = e => {
var id = "p" i;
var div = document.createElement("div");
div.id = id;
document.getElementById("progress").appendChild(div);
document.getElementById(id).innerHTML = "Initializing.";
const f = e.target;
const resource = { fileName: f.fileName, fileSize: f.fileSize, fileType: f.fileType, fileBuffer: f.result, accessToken: accessToken, folderId: "root" };
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) "% (" f.fileName ")";
} else {
msg = res.status " (" f.fileName ")";
}
if (res.status == "Done") {
res.result.name = name; // Added
google.script.run.putFileInf(res.result);
}
document.getElementById(id).innerText = msg;
});
};
});
}
</script>
</body>
</html>
In this modification, the value is retrieved from the text input tag. And, include it in
res.result
. By this,obj.name
of.appendRow([obj.name, obj.mimeType, obj.id, Utilities.formatDate(new Date(), "GMT 8:00", "yyyy-MM-dd'T'HH:mm:ss'Z'")])
has the value ofname
.In this case, your
update
of Google Apps Script side is not used.