Csv file upload to google drive ( as xlsx ) help please

Saheb_Kole · August 17, 2024, 11:31am

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

TIMAI2 · August 17, 2024, 1:27pm

You could take this approach:

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"); 

}

BLOCKS

SHEET

The spreadsheet remains a google sheet, for future appending of data, etc.

Saheb_Kole · August 17, 2024, 4:11pm

Wow great, Yes it's work perfectly but is it possible to store as a xlsx data directly in google drive folder also.

This method work is my next alternate to store same xlsx sheets but if seperate solution please guide me.

TIMAI2 · August 17, 2024, 5:04pm

Yep

Your google drive folder which you need to add in the function saveAsExcel needs to be "Anyone with Link Can View" permission.

You may want to trash the google sheet,
ss.setTrashed(true);

or at the very least clear its contents:
sh.clearContent()

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]);
}

var xlsxId = saveAsExcel(ssId,"myxlsx");

return ContentService.createTextOutput("https://drive.google.com/uc?export=download&id=" + xlsxId ); 

}

function saveAsExcel(ssId,ssName) {
  //var sheetId = ssId;
  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;
}

BLOCKS

takes a little while so i put in a progress dialog

Saheb_Kole · August 19, 2024, 5:00am

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 ??

TIMAI2 · August 19, 2024, 5:42am

Yes. You will need to send more parameters from your app to the script.

See here for an example of how this is done:

Saheb_Kole · August 19, 2024, 6:02am

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

block image I tryed

TIMAI2 · August 19, 2024, 7:54am

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 ); 

}

BLOCKS

Saheb_Kole · August 20, 2024, 1:32pm

error code 403 came

TIMAI2 · August 20, 2024, 2:04pm

Check your work

Saheb_Kole · August 20, 2024, 2:13pm

Code

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 ); 

}

TIMAI2 · August 20, 2024, 2:15pm

Blocks, as previously advised, should be like this:

why do you keep adding the unnecessary JsonObjectEncode block ?

and you still have this function in your code.gs ?

function saveAsExcel(ssId,ssName) {
  //var sheetId = ssId;
  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;
}

Saheb_Kole · August 20, 2024, 2:24pm

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 ); 

}

My full code

TIMAI2 · August 20, 2024, 3:19pm

You will need the saveAsExcel function to save your spreadsheet as an excel file...

Saheb_Kole · August 21, 2024, 7:11am

is it possible to show the uploading status in my apps between 1 to 100% ?

TIMAI2 · August 21, 2024, 8:18am

It is possible, but not very practical...

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%.

I might make one, if I find the time...

TIMAI2 · August 21, 2024, 10:08am

Perhaps something like this:

everDecreasingProgressBar.aia (4.0 KB)

The Finish Upload button simulates the web component receiving responseContent from the script.

Saheb_Kole · August 21, 2024, 10:16am

how to know the estimated upload finished time to completed uploading task ?

if the data size big like 200 new row post at a time then how to calculated the uploading task completed time ??

TIMAI2 · August 21, 2024, 10:21am

With my example you don't. It will just sit at 99% until the script completes...

Saheb_Kole · August 21, 2024, 10:44am

OK .. I got the point