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

Question

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

Welcome to [so]. Besides adding code, add enough details so other might reproduce the problem, show what you have tried fix it and add a brief description of your search efforts as is suggested in [ask]. P.S. There is a line that should throw an error --> `ws.getRange()` (SpreadsheetApp.Sheet.getRange should have parameters) — Rubén, Oct 09 '22 at 20:26
Related: https://stackoverflow.com/q/68894315/1595451, https://stackoverflow.com/q/63600001/1595451 — Rubén, Oct 09 '22 at 23:54

Cooper · Accepted Answer · 2022-10-10T11:47:42.237

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

Thank you so much it works, but it still exceeds the maximum runtime. I have another question I would like to add a column for the files name ''' vs1 = vs1.map(function (a){ return a.concat([file.getName()]);}) ''' But it returns only 2 names for all — hind, Oct 10 '22 at 08:24

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

1 Answers1