google-apps-scriptdo-whilearray-mergeexceed

Import multiple Google Sheets into 1 sheet / The JavaScript runtime exited unexpectedly/


the code is good no bug was identified, but the folder is very heavy (full of files) so the runtime (execution) is infinite and I have this as a comment

The JavaScript runtime exited unexpectedly.

My objective is to copy the range "D12: T64" of each file and merge it to the master file "Extract1"

function ExtractPPS1(){
  var folder = DriveApp.getFolderById("1tCw6VpQN7ccaiIDTEm7hsJ8ZKw50MDJ6");
  var filesIterator = folder.getFiles();
  var file; 
  var fileType; 
  var ssID;
  var combinedData= [];
  var data;

  while(filesIterator.hasNext()){
    file = filesIterator.next();
    fileType = file.getMimeType(); 
    if (fileType ==="application/vnd.google-apps.spreadsheet" ){
      ssID = file.getId();
      data = getDataFromAssessment1(ssID);
      combinedData = combinedData.concat(data);
    }
  }
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extract1");
  ws.getRange("D12:T64").clearContent(); 
  ws.getRange(2,1,combinedData.length, combinedData[0].length).setValues(combinedData);
  ws.getRange()
 
  
}

function getDataFromAssessment1(ssID){
  
  var ss = SpreadsheetApp.openById(ssID);
  var ws = ss.getSheetByName("Assessment Sheet ORCA Pilot STEP1");
  var data = ws.getRange("D12:T64"+ws.getLastRow()).getValues();
  return data;
  
};    

Solution

  • function ExtractPPS1() {
      var csh = SpreadsheetApp.getActive.getSheetByName("Extract1");
      csh.getRange("D12:T64").clearContent();
      var folder = DriveApp.getFolderById("ssid");
      var files = folder.getFiles();
      while (files.hasNext()) {
        let file = files.next();
        if (file.getMimeType() == "application/vnd.google-apps.spreadsheet") {
          var ss = SpreadsheetApp.openById(file.getId());
          var sh1 = ss.getSheetByName("Assessment Sheet ORCA Pilot STEP1");
          var vs1 = sh1.getRange("D12:T64").getValues();
          csh.getRange(csh.getLastRow() + 1, 1, vs1.length, vs1[0].length).setValues(vs1);
        }
      }
    }
    

    Adding file name to data set:

    function ExtractPPS1() {
      var csh = SpreadsheetApp.getActive.getSheetByName("Extract1");
      csh.getRange("D12:T64").clearContent();
      var folder = DriveApp.getFolderById("ssid");
      var files = folder.getFiles();
      while (files.hasNext()) {
        let file = files.next();
        if (file.getMimeType() == "application/vnd.google-apps.spreadsheet") {
          var ss = SpreadsheetApp.openById(file.getId());
          var sh1 = ss.getSheetByName("Assessment Sheet ORCA Pilot STEP1");
          var vs1 = sh1.getRange("D12:T64").getValues();
          vs1.unshift([...Array.from(new Array(vs1[0].length).keys(),x => (x == 0) ? `File Name: ${file.getName()}`:'')])
          csh.getRange(csh.getLastRow() + 1, 1, vs1.length, vs1[0].length).setValues(vs1);
        }
      }
    }