How can i read a specified value of cell and convert a file from excel into spreadsheet?

Question

In here I am trying to make a checking where if the "STATUS" column is 'NEW FILE', then i would like to perform a file conversation from excel to spreadsheet.

For the "STATUS" column i created an IF-ELSE statement,

if (row[0] === "last week file") {
  newValues.push(['OLD FILE'])
}
else{
  newValues.push(['NEW FILE'])
  ConvertFiles()
  return
}

Therefore, I am making a check through the "STATUS", if the status column is empty it will be written as 'NEW FILE', and then it will perform an file conversion from excel to spreadsheet since i already called the method inside it.

Here is the EDITED version code of the file conversion:

function ConvertFiles() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange(2, 1, sheet.getLastRow()-1, 5); // get A2:E6 range
  var data = range.getValues(); // get A2:E6 data

for(var i = 0; i < data.length; i++){ 
  if(data[i][2] == " "){

  for( var r= 2;r < sheet.getLastRow()+1; r++){
  var fileId = sheet.getRange(r,1).getValue(); 
  var folderID = sheet.getRange(r,2).getValue(); //for destination folder

  var files = DriveApp.getFileById(fileId);
  var name = files.getName().split('.')[0]; 
  var blob = files.getBlob();
  
  var newFile = {
  title: name.replace('_converted','') + '_converted', 
  parents: [{id: folderID}] }; 

  var destinationFolderId = DriveApp.getFolderById(folderID); 
  var existingFiles = destinationFolderId.getFilesByName(newFile.title);

while(existingFiles.hasNext()) {
  var oldConvertedFileWithSameNameID = existingFiles.next().getId();
  Drive.Files.remove(oldConvertedFileWithSameNameID,{supportsAllDrives: true});
} 

  var newFileID = Drive.Files.insert(newFile, blob, { convert: true,supportsAllDrives: true }).id; 
  Logger.log(newFileID);

  var Url = "https://drive.google.com/open?id=" + newFileID;
   //sheet.getRange(r,4).setValue(newFileID);
   //sheet.getRange(r,5).setValue(Url); 
   }
   sheet.getRange(i+2,4).setValue(newFileID); //set value in column D
   sheet.getRange(i+2,5).setValue(Url); //set value in column E  
}
 }
 }

The error that i am facing is, when i call the method ConvertFiles() inside the if statement, the conversion happens from row 2 until 6 CONTINOUSLY without stopping as shown in sample in red circle.

I only wanted to make conversion on the "NEW FILES" only which will be on row 5 and 6.

How can i make a conversion on the selected/specified row?

Reading a value of a cell is simple. `Sheet.getRange().getValue()` — Cooper, Dec 21 '21 at 19:20
Converting the excel files: https://stackoverflow.com/a/55645062/7215091 — Cooper, Dec 21 '21 at 19:30

Nikko J. · Answer 1 · 2021-12-22T15:38:56.977

It would be more efficient if you obtain all the values in your Sheet, loop the 2D array the getValues() method will return and add an if statement that will only process new files.

Example:

Here in my example below I created a script that will only process rows that have a blank value for the status column.

Code:

function ConvertFiles() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange(2, 1, sheet.getLastRow()-1, 5); // get A2:E6 range
  var data = range.getValues(); // get A2:E6 data
  
  /*the content of data is 2D array, 
  each sub array represent rows in your table*/
  for(var i = 0; i < data.length; i++){ 
    if(data[i][2] == ""){ //the 2 in [i][2] represent the value of C column in sheet
      
      //Add your file conversion code here

       sheet.getRange(i+2,4).setValue("Test only"); //set value in column D
       sheet.getRange(i+2,5).setValue("Test only"); //set value in column E
    }
  }
}

Data:

Output:

References:

EDIT:

The value of data variable in the code below is a 2D array containing all the data in the range provided. In your example, it is the data of A2:E6.

Example output:

[
 [fileId1,folderId1,Status1,,],
 [fileId2,folderId2,Status2,,],
 [fileId3,folderId3,Status3,,],
 [fileId4,folderId4,,,],
 [fileId5,folderId5,,,],
]

The for loop will access each sub array per iteration and since we already knew the position of our target data (fileID and folderID) we don't need to create another for loop to access it, instead we just specify the index on which the data is located. data[i][0] for file id and data[i][1] for folder id. The if(data[i][2] == "") is added to check if the column C of each row is empty and ignore the one with data.

Code:

function ConvertFiles() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getRange(2, 1, sheet.getLastRow()-1, 5);
  var data = range.getValues();
  for(var i = 0; i < data.length; i++){
    if(data[i][2] == ""){
      var fileId = data[i][0]; 
      var folderID = data[i][1];
      var files = DriveApp.getFileById(fileId);
      var name = files.getName().split('.')[0]; 
      var blob = files.getBlob();
      var newFile = {
      title: name.replace('_converted','') + '_converted', 
      parents: [{id: folderID}] };
      var destinationFolderId = DriveApp.getFolderById(folderID); 
      var existingFiles = destinationFolderId.getFilesByName(newFile.title); 
      while(existingFiles.hasNext()) {
        var oldConvertedFileWithSameNameID = existingFiles.next().getId();
        Drive.Files.remove(oldConvertedFileWithSameNameID,{supportsAllDrives: true});
      }
      var newFileID = Drive.Files.insert(newFile, blob, { convert: true,supportsAllDrives: true }).id; 
      var Url = "https://drive.google.com/open?id=" + newFileID;
      sheet.getRange(i+2,4).setValue(newFileID); 
      sheet.getRange(i+2,5).setValue(Url); 
    }
  }
}

Thanks for replying. I did implement your code inside my code as i shared again in the question section. I keep `for( var r= 2;r < sheet.getLastRow()+1; r++){` because i guess i need to use it to read the file id and folder id for the destination later on. I tried running the code by calling the method in the if else statement, but nothing happened. However, i tried to run the `ConvertFiles() ` method only, but still nothing executed inside the sheet (no conversion happened) — Vishnu Kumaran, Dec 22 '21 at 01:40
I am trying to make it in sequence, when there is _a new file been upload_ >> _the if-else statement will detect and fill up the empty cell value with "NEW FILE"_ >> _after filling up the value, it will make the file conversion for the new file that has been newly uploaded_ (which i did by calling the convert file method in the if-else statement). — Vishnu Kumaran, Dec 22 '21 at 01:47
@VishnuKumaran - You can obtain the fileID and folderID by just using `data[i][0]` and `data[i][1]`. I added another code snippet and included the conversion process in your code. Let me know if it works. — Nikko J., Dec 22 '21 at 15:21
I would also prefer you print the value of `data` variable and check this tutorial on [how to read and write on 2D array](https://www.javascripttutorial.net/javascript-multidimensional-array/). — Nikko J., Dec 22 '21 at 15:25

How can i read a specified value of cell and convert a file from excel into spreadsheet?

1 Answers1

Example:

References: