hello there I want to upload my csv data to my google drive (xlsx) format and get download link on it by using google apps script unfortunately I couldn't get success
Please help me in regards it
function doPost(e) {
try {
var csvData = e.postData.contents;
var filename = e.parameter.filename || 'converted_file.xlsx';
// Create a new Google Spreadsheet
var spreadsheet = SpreadsheetApp.create('Temporary Spreadsheet');
var sheet = spreadsheet.getActiveSheet();
// Parse the CSV data and add it to the sheet
var csvRows = Utilities.parseCsv(csvData);
sheet.getRange(1, 1, csvRows.length, csvRows[0].length).setValues(csvRows);
// Convert the spreadsheet to Excel (.xlsx)
var xlsxBlob = spreadsheet.getAs('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
xlsxBlob.setName(filename);
// Delete the temporary spreadsheet
DriveApp.getFileById(spreadsheet.getId()).setTrashed(true);
// Save the Excel file to the specified folder
var folderId = 'YOUR_FOLDER_ID'; // Replace with your Google Drive Folder ID
var folder = DriveApp.getFolderById(folderId);
var file = folder.createFile(xlsxBlob);
var fileUrl = file.getUrl(); // Get the URL of the uploaded file
return ContentService.createTextOutput(fileUrl);
} catch (error) {
return ContentService.createTextOutput('Error: ' + error.toString());
}
}
1st I want to upload on google drive as xlsx format and get directly download link if required further download then with the download link I can download
Create spreadsheet (if not exists, if exists then use current)
Append the csv data
Return a url that will download an xlsx file
SCRIPT
function doPost(e) {
var ss,ssId;
var fx = DriveApp.getFilesByName("myXlsx");
if(!fx.hasNext()){
ss = SpreadsheetApp.create("myXlsx");
ssId = ss.getId();
var driveDoc = DriveApp.getFileById(ssId);
driveDoc.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
} else {
var file = fx.next();
ss = SpreadsheetApp.openById(file.getId());
}
sh = ss.getSheets()[0];
ssId = ss.getId();
var data = JSON.parse(e.postData.contents);
for (var i=0;i<data.length;i++) {
sh.appendRow(data[i]);
}
return ContentService.createTextOutput("https://docs.google.com/spreadsheets/d/" + ssId + "/export?format=xlsx");
}
yes got the way to complete the task but 1 small problems faced all time the xlsx file name is saved as myxlsx can i customize the xlsx file name throu my apps using text box text name ??
ok ok I got the solution but problem is I don't want to use the Base64Convertor extension now to upload only csv file now is there any other way to do that ?
I search on chat GTP
Yes, you can customize the name of the stored Excel file using a textbox input in MIT App Inventor. Here's how you can do it:
Steps:
In MIT App Inventor:
Create a TextBox component where the user can input the desired filename.
Use a Button to trigger the process of sending the data to the Google Apps Script.
Modify the doPost Function:
Add a parameter to the doPost function to accept the filename.
Pass the filename from MIT App Inventor to the Google Apps Script.
Example:
MIT App Inventor Blocks:
TextBox Component:
TextBox1: For the user to input the filename.
Button Component:
Button1: To trigger the HTTP POST request.
Web Component:
Use the Web component to send the POST request to your Google Apps Script URL.
Blocks:
When Button1 is clicked:
Set the Web1.Url to the Google Apps Script URL.
Create a dictionary with the data you want to send, including the filename from TextBox1.Text.
Use Web1.PostText to send the data to the script.
Google Apps Script:
Modify the doPost function to accept the filename:
javascript
Copy code
function doPost(e) {
var ss, ssId;
var data = JSON.parse(e.postData.contents);
var fileName = data.fileName; // Get the filename from the data
var fx = DriveApp.getFilesByName("myXlsx");
if (!fx.hasNext()) {
ss = SpreadsheetApp.create("myXlsx");
ssId = ss.getId();
var driveDoc = DriveApp.getFileById(ssId);
driveDoc.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
} else {
var file = fx.next();
ss = SpreadsheetApp.openById(file.getId());
}
var sh = ss.getSheets()[0];
ssId = ss.getId();
for (var i = 0; i < data.data.length; i++) {
sh.appendRow(data.data[i]);
}
var xlsxId = saveAsExcel(ssId, fileName); // Use the filename from the data
return ContentService.createTextOutput("https://drive.google.com/uc?export=download&id=" + xlsxId);
}
function saveAsExcel(ssId, ssName) {
var url = "https://docs.google.com/spreadsheets/d/" + ssId + "/export?format=xlsx";
var params = {
method: "get",
headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
muteHttpExceptions: true
};
var blob = UrlFetchApp.fetch(url, params).getBlob();
blob.setName(ssName + ".xlsx");
var xlid = DriveApp.getFolderById('<FOLDER ID HERE>').createFile(blob).getId();
return xlid;
}
Explanation:
The filename is passed as part of the POST data from MIT App Inventor.
In the Google Apps Script, the filename is used to save the Excel file with the custom name provided by the user.
This setup allows you to dynamically change the filename from your MIT App Inventor app based on user input.
But unworthily I don't get set the block right now
Close, but no cigar ! (That is ChatGPT for you...)
SCRIPT:
function doPost(e) {
var ss,ssId;
var contents = JSON.parse(e.postData.contents.replace(/\n/g, ""));
var filename = contents.filename;
var fx = DriveApp.getFilesByName("myXlsx");
if(!fx.hasNext()){
ss = SpreadsheetApp.create("myXlsx");
ssId = ss.getId();
var driveDoc = DriveApp.getFileById(ssId);
driveDoc.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
} else {
var file = fx.next();
ss = SpreadsheetApp.openById(file.getId());
}
sh = ss.getSheets()[0];
ssId = ss.getId();
var data = contents.data;
for (var i=0;i<data.length;i++) {
sh.appendRow(data[i]);
}
var xlsxId = saveAsExcel(ssId,filename);
return ContentService.createTextOutput("https://drive.google.com/uc?export=download&id=" + xlsxId );
}
function doPost(e) {
var ss,ssId;
var contents = JSON.parse(e.postData.contents.replace(/\n/g, ""));
var filename = contents.filename;
var fx = DriveApp.getFilesByName("myXlsx");
if(!fx.hasNext()){
ss = SpreadsheetApp.create("myXlsx");
ssId = ss.getId();
var driveDoc = DriveApp.getFileById(ssId);
driveDoc.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
} else {
var file = fx.next();
ss = SpreadsheetApp.openById(file.getId());
}
sh = ss.getSheets()[0];
ssId = ss.getId();
var data = contents.data;
for (var i=0;i<data.length;i++) {
sh.appendRow(data[i]);
}
var xlsxId = saveAsExcel(ssId,filename);
return ContentService.createTextOutput("https://drive.google.com/uc?export=download&id=" + xlsxId );
}
function doPost(e) {
var ss,ssId;
var contents = JSON.parse(e.postData.contents.replace(/\n/g, ""));
var filename = contents.filename;
var fx = DriveApp.getFilesByName("myXlsx");
if(!fx.hasNext()){
ss = SpreadsheetApp.create("myXlsx");
ssId = ss.getId();
var driveDoc = DriveApp.getFileById(ssId);
driveDoc.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
} else {
var file = fx.next();
ss = SpreadsheetApp.openById(file.getId());
}
sh = ss.getSheets()[0];
ssId = ss.getId();
var data = contents.data;
for (var i=0;i<data.length;i++) {
sh.appendRow(data[i]);
}
var xlsxId = saveAsExcel(ssId,filename);
return ContentService.createTextOutput("https://drive.google.com/uc?export=download&id=" + xlsxId );
}
If you must have a progress bar, instead of the progress dialog spinning circle, you could create a timer loop that builds up the progress in decreasing values (so that it never really gets to 100%), then when the script returns that it has finished, set to 100%.
