App script failing to bring data from another spreadsheet-CodePudding

This program tries to bring all the data in one spreadsheet and paste it into another spreadsheet. Firstly, it gets all files inside a folder (all of which will be spreadsheets), then it gets the values of a certain range and stores them into an array; after that, it goes to another spreadsheet and tries to set the values of the array into the first row with no data; finally, it moves the file where the data was stored in the first place.

var uploadG = DriveApp.getFolderById('')
var moveToG = DriveApp.getFolderById('')

function obtainAndImportData(uploadFolder){
  var internalFiles = uploadFolder.getFiles()
  while (internalFiles.hasNext()){
    var file = internalFiles.next()
    var fileID = file.getId()
    var copySheet = SpreadsheetApp.openById(fileID).getSheets()[0] // reads file by file and gets their first spreadsheet

    var Cvals = copySheet.getRange("C1:C").getValues() 
    var lastToValue = Cvals.filter(String).length-2 // gets last row with data

    var Csheet = pasteSheet.getRange("C1:C").getValues()
    var lastSheetToValue = Csheet.filter(String).length  // gets last row with data

    var allRows = []
    for (i = 0;i = lastToValue;i  ){
      allRows[i] = copySheet.getRange(`B${i 3}:P${i 3}`).getValues()
    } // gets row and then stores it into an array

    var rangeToUnify = pasteSheet.getRange(`B${lastSheetToValue 1}:P${lastSheetToValue   lastToValue   1}`) 
    rangeToUnify.setValues(allRows)  //inserts data from old spreadsheet into this new one

    file.moveto(moveToG) // moves file
  }
}

I think it should work fine, but the program just never completes its execution. Never inserts the data, and never moves files, but it doesn't throw any error either.

Note: The files this program is reading start as .xslx files, which are saved as Google Spreadsheets once they're uploaded to Google Drive. (maybe that is the problem, but I don't really know how to solve it)

CodePudding user response：

I believe this is a little closer to what you were shooting for

function obtainAndImportData(uploadFolder) {
  var uploadG = DriveApp.getFolderById('')
  var moveToG = DriveApp.getFolderById('')
  var internalFiles = uploadFolder.getFiles()
  while (internalFiles.hasNext()) {
    var file = internalFiles.next()
    var fileID = file.getId()
    var copySheet = SpreadsheetApp.getActive().getSheets()[0];
    var Cvals = copySheet.getRange("C1:C"   copySheet.getLastRow()).getValues()
    var ldr = Cvals.length;
    var Csheet = pasteSheet.getRange("C1:C"   pasteSheet.getLastRow()).getValues()
    var lstv = Csheet.length;
    var allRows = []
    for (i = 0; i = ldr; i  ) {
      allRows.push(copySheet.getRange(`B${i   3}:P${i   3}`).getValues()[0]);
    } 
    pasteSheet.getRange(lstv   1,2,allRows.length,allRows[0].length).setValues(allRows);
    file.moveto(moveToG) // moves file
  }
}

CodePudding user response：

Modification points:

When I saw your script, in your for loop, you are using for (i = 0; i = lastToValue; i ) {. In this case, the loop is not finished.
- I thought that this might be the reason for your issue of but the program just never completes its execution. Never inserts the data, and never moves files, but it doesn't throw any error either. is due to this.
- In this case, it is required to be modified to for (i = 0; i < lastToValue; i ) {. But, when allRows[i] = copySheet.getRange(B${i 3}:P${i 3}).getValues() is used in a loop, the process cost will become high. Ref
- Also, when rangeToUnify.setValues(allRows) is used in a loop. the process cost becomes high.
In order to retrieve the values from the sheet, you are using copySheet.getRange("C1:C").getValues() and pasteSheet.getRange("C1:C").getValues(). In this case, all rows over the data range are retrieved. In this case, the process cost becomes high.
And, even when the above script was modified, I think that an error occurs at rangeToUnify.setValues(allRows). Because the number of rows are different between the range and the values.
And, file.moveto(moveToG) should be file.moveTo(moveToG).

When these points are reflected in your script, how about the following modification?

Modified script:

Before you use this script, please set the variables of pasteSheet, uploadFolder and moveToG.

function obtainAndImportData() {
  var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###"); // Please set your "pasteSheet".
  var uploadFolder = DriveApp.getFolderById('###'); // Please set your folder.
  var moveToG = DriveApp.getFolderById('###'); // Please set your folder.

  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var internalFiles = uploadFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  var values = [];
  while (internalFiles.hasNext()) {
    var file = internalFiles.next();
    var copySheet = SpreadsheetApp.open(file).getSheets()[0];
    var srcValues = copySheet.getRange("B3:P"   copySheet.get1stNonEmptyRowFromBottom(3)).getValues();
    values = [...values, ...srcValues];
    file.moveTo(moveToG);
  }
  pasteSheet.getRange(pasteSheet.get1stNonEmptyRowFromBottom(3)   1, 2, values.length, values[0].length).setValues(values);
}

When this script is run, the Spreadsheet is retrieved from "uploadFolder" folder. And, the values are retrieved from "B3:P" of the 1st tab for each Spreadsheet, and those values are appended from the column "B" of "pasteSheet" sheet.

Note:

In this modification, from your showing script, the last row is retrieved from the column "C". But, if you want to use the last row of the data range, I think that you can also use getLastRow().

Modification points:

Modified script:

Note:

References: