0

In a google sheet, I managed to filter file name and file type and list all the particular files needed from a google drive folder based on the conditions into a spreadsheet. I am having an issue to convert the excel files into spreadsheet because I'm opening the selected files using "SpreadsheetApp.openById(id)" in another function() to get the data range from the specific files.

Based on the requirement set, I was been asked to convert the excel files into spreadsheet before getting the range data and export those data into another sheet.

Below are the codes that i created to list the file name and file type, so i believe that the file conversion method might need to be implemented here as well because it involves the file filtering process and these are the files need to be converted later on. Please correct me if I am wrong.

function getChildFiles(parentName, parent, sheet) {

var fileIter = parent.searchFiles("title contains 'completed'and title contains 'Verification 
Visit Direct Suppliers'and mimeType='application/vnd.openxmlformats- 
officedocument.spreadsheetml.sheet'"); 

var output = [];

var now = new Date();
var date = new Date();
var copy = new Date(date);

while (fileIter.hasNext()) { 
var file = fileIter.next();

var fileName = file.getName();

var fileType = file.getMimeType();

var filecreatedate = file.getDateCreated();

var fileUpdate = file.getLastUpdated()

var path = parentName + ' |--> ' + fileName;

var fileID = file.getId();

var Url = 'https://drive.google.com/open?id=' + fileID;

output.push([fileID, fileName, path, Url,filecreatedate,fileUpdate,copy]);
Logger.log(fileType);}

if (output.length) {

var last_row = sheet.getLastRow();

sheet.getRange(last_row + 1, 1, output.length, 7).setValues(output);

SpreadsheetApp.flush();}

var childFolders = parent.getFolders();

while (childFolders.hasNext()) { 

var childFolder = childFolders.next();

var childFolderName = childFolder.getName();

getChildFiles( parentName + ' |--> ' + childFolderName, childFolder, sheet);}}

So I'm trying to implement the file conversion method from excel file to spreadsheet, so that i can extract data from the excel file after the file successfully converted to spreadsheet.

new name
  • 15,861
  • 19
  • 68
  • 114
  • please let me know if you don't understand any of the statement... – Vishnu Kumaran Nov 01 '21 at 17:26
  • I have to apologize for my poor English skill. I cannot understand the situation of `convert the excel files into spreadsheet files without changing the existing excel files type`. Can I ask you about the detail of your goal? – Tanaike Nov 02 '21 at 00:38
  • @Tanaike I mean for example, if i have file **A.xlsx** in my google drive folder and when i convert it, there will be a new file called **A.spreadsheet**. So basically if can i don't want the converted spreadsheet file to be populated in the google drive folder together with excel file. If can i want to store it in a variable so that i can push that variable into an array an it will populated a list of converted file(spreadsheet) into a google sheet. That is what im trying to do...but im not really sure whether it is possible or not. – Vishnu Kumaran Nov 02 '21 at 02:33
  • Thank you for replying. You want to convert XLSX to Spreadsheet. Is my understanding correct? But, I cannot understand `So basically if can i don't want the converted spreadsheet file to be populated in the google drive folder together with excel file. If can i want to store it in a variable so that i can push that variable into an array an it will populated a list of converted file(spreadsheet) into a google sheet.`. By this, I cannot imagine your actual goal. I apologize for my poor English skill. – Tanaike Nov 02 '21 at 02:37
  • @Tanaike or how about if i try to get the `fileID` and call it into another function for example `ConvertFile` function and use the `fileID` (which is in excel type) to convert into spreadsheet type ? If this method is possible can i know how can i do it? – Vishnu Kumaran Nov 02 '21 at 02:41
  • Thank you for replying. When you want to convert the XLSX file to Google Spreadsheet using the file ID, for example, is these threads useful? https://stackoverflow.com/q/56063156/7108653 , https://stackoverflow.com/q/64578454/7108653 – Tanaike Nov 02 '21 at 02:48
  • @Tanaike Yes your understanding is correct. I want to convert the filtered XLSX files from the code above into spreadsheet form and list back those files into a google sheet. This is my main goal. Sorry for confusing you with that statement. – Vishnu Kumaran Nov 02 '21 at 02:49
  • @Tanaike Actually i did refer to the (stackoverflow.com/q/56063156/7108653 , stackoverflow.com/q/64578454/7108653) but I'm not sure how can i use the `fileID` (which already contains the filtered files) directly into those code and convert it. – Vishnu Kumaran Nov 02 '21 at 02:58
  • Thank you for replying. For example, can you provide the sample values of `the fileID (which already contains the filtered files)`? By this, I thought that I might advice to resolve your issue of `how can i use the fileID (which already contains the filtered files) directly into those code and convert it.`. – Tanaike Nov 02 '21 at 03:05
  • @Tanaike so here is the sample values that the `fileID` store files with excel file type and contains the word of ''Verification Visit Direct Suppliers' and 'completed' [https://drive.google.com/file/d/1qUbeElLB2mfFEWZfpvE4nkhavFeaT6Ax/view?usp=sharing] so this is the list i have already populate in google sheet...so im trying to convert those files by using the id. i hope im providing you the correct sample that you are looking for.. – Vishnu Kumaran Nov 02 '21 at 04:04
  • Thank you for replying. From your replying, I proposed a sample script as an answer. Could you please confirm it? If that was not useful, I apologize. – Tanaike Nov 02 '21 at 04:33
  • Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Nov 07 '21 at 07:54
  • @Tanaike Not really but i tried implementing your concept of 'getSheetByName' and then i tried referring back to the thread you shared to me. So i manage to solve it in another way. Thank you i really appreciate your idea. – Vishnu Kumaran Nov 08 '21 at 18:27
  • @Tanaike by the way i had posted another question related to the same coding issue [https://stackoverflow.com/questions/69888122/trying-to-save-the-converted-excel-files-into-its-specific-folder-in-drive-using]. Maybe you can try to have a look in it. Thank you. – Vishnu Kumaran Nov 08 '21 at 18:31
  • Thank you for replying. I apologize for the inconvenience. From `Not really but i tried implementing your concept of 'getSheetByName' and then i tried referring back to the thread you shared to me. So i manage to solve it in another way. Thank you i really appreciate your idea.`, I understood that my answer was not useful. So I have to delete my answer. This is due to my poor skill. I deeply apologize for this. I would be grateful if you can forgive my poor skill. – Tanaike Nov 08 '21 at 23:25

0 Answers0