Failure of app script to retrieve data from an external spreadsheet

Question

Failure of app script to retrieve data from an external spreadsheet

In an attempt to consolidate data, this program aims to transfer information from one spreadsheet to another. The process involves retrieving all files within a designated folder (all of which are spreadsheets), extracting values from a specific range, and storing them in an array. Subsequently, the program switches to another spreadsheet where it attempts to populate the first row with the array values. Finally, once the data is transferred, the original file is moved to a different location.

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]

    var Cvals = copySheet.getRange("C1:C").getValues() 
    var lastToValue = Cvals.filter(String).length-2 

    var Csheet = pasteSheet.getRange("C1:C").getValues()
    var lastSheetToValue = Csheet.filter(String).length  

    var allRows = []
    for (i = 0;i = lastToValue;i++){
      allRows[i] = copySheet.getRange(`B${i+3}:P${i+3}`).getValues()
    }

    var rangeToUnify = pasteSheet.getRange(`B${lastSheetToValue+1}:P${lastSheetToValue + lastToValue + 1}`) 
    rangeToUnify.setValues(allRows)  

    file.moveto(moveToG)  
  }
}

Despite expectations, the execution of this program seems to remain incomplete. It fails to insert and move data without displaying any errors throughout the process.

Note: The program reads .xslx files, which are eventually converted to Google Spreadsheets upon upload to Google Drive. This conversion may be causing complications that are challenging to resolve.

javascript google-apps-script google-sheets

Answer 1

Answer №1

Here is a modified version that aligns more closely with your original intention

function getAndImportData(uploadFolder) {
  var uploadG = DriveApp.getFolderById('')
  var moveToG = DriveApp.getFolderById('')
  var filesInFolder = uploadFolder.getFiles()
  while (filesInFolder.hasNext()) {
    var file = filesInFolder.next()
    var fileId = file.getId()
    var copySheet = SpreadsheetApp.getActive().getSheets()[0];
    var columnValues = copySheet.getRange("C1:C" + copySheet.getLastRow()).getValues()
    var rowLength = columnValues.length;
    var destSheet = pasteSheet.getRange("C1:C" + pasteSheet.getLastRow()).getValues()
    var lastValue = destSheet.length;
    var allRows = []
    for (i = 0; i < rowLength; i++) {
      allRows.push(copySheet.getRange(`B${i + 3}:P${i + 3}`).getValues()[0]);
    } 
    pasteSheet.getRange(lastValue + 1,2,allRows.length,allRows[0].length).setValues(allRows);
    file.moveto(moveToG) // moves file
  }
}

Answer 2

Here is a modified version that aligns more closely with your original intention

function getAndImportData(uploadFolder) {
  var uploadG = DriveApp.getFolderById('')
  var moveToG = DriveApp.getFolderById('')
  var filesInFolder = uploadFolder.getFiles()
  while (filesInFolder.hasNext()) {
    var file = filesInFolder.next()
    var fileId = file.getId()
    var copySheet = SpreadsheetApp.getActive().getSheets()[0];
    var columnValues = copySheet.getRange("C1:C" + copySheet.getLastRow()).getValues()
    var rowLength = columnValues.length;
    var destSheet = pasteSheet.getRange("C1:C" + pasteSheet.getLastRow()).getValues()
    var lastValue = destSheet.length;
    var allRows = []
    for (i = 0; i < rowLength; i++) {
      allRows.push(copySheet.getRange(`B${i + 3}:P${i + 3}`).getValues()[0]);
    } 
    pasteSheet.getRange(lastValue + 1,2,allRows.length,allRows[0].length).setValues(allRows);
    file.moveto(moveToG) // moves file
  }
}

Answer 3

Answer №2

Areas for Improvement:

Upon reviewing your script, I noticed that in your for loop, you have used
```
for (i = 0; i = lastToValue; i++) {
```
. This will cause the loop to never finish.
- I suspect that this might be the reason why your program doesn't complete its execution as expected and does not throw any errors.
- To address this issue, it is necessary to update the loop to
```
for (i = 0; i < lastToValue; i++) {
```
  . However, using allRows[i] = copySheet.getRange(B${i + 3}:P${i + 3}).getValues() within a loop can increase the processing cost. Reference
- Similarly, using rangeToUnify.setValues(allRows) within a loop can also lead to high processing costs.
You are fetching values from the sheet using
```
copySheet.getRange("C1:C").getValues()
```
and
```
pasteSheet.getRange("C1:C").getValues()
```
. This approach retrieves all rows within the data range, resulting in increased processing costs.
Even after making the aforementioned modifications, there might be an error at rangeToUnify.setValues(allRows) due to a mismatch in the number of rows between the range and the values.
Lastly, the correct method should be file.moveTo(moveToG), instead of file.moveto(moveToG).

Consider implementing these changes into your script. Here's the modified version:

Modified Script:

Prior to running this script, ensure to set the variables for pasteSheet, uploadFolder, and moveToG.

function obtainAndImportData() {
  var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###"); // Provide the name of your "pasteSheet".
  var uploadFolder = DriveApp.getFolderById('###'); // Specify your folder ID.
  var moveToG = DriveApp.getFolderById('###'); // Define another folder ID.

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

Executing this script fetches Spreadsheets from the "uploadFolder". Values are retrieved from "B3:P" of the first tab in each Sheet, then appended starting from column "B" of "pasteSheet".

Note:

In this revised version, the last row from the "C" column is fetched per your original script. If you wish to utilize the last row of the data range, consider using getLastRow().

References:

Answer 4

Areas for Improvement:

Upon reviewing your script, I noticed that in your for loop, you have used
```
for (i = 0; i = lastToValue; i++) {
```
. This will cause the loop to never finish.
- I suspect that this might be the reason why your program doesn't complete its execution as expected and does not throw any errors.
- To address this issue, it is necessary to update the loop to
```
for (i = 0; i < lastToValue; i++) {
```
  . However, using allRows[i] = copySheet.getRange(B${i + 3}:P${i + 3}).getValues() within a loop can increase the processing cost. Reference
- Similarly, using rangeToUnify.setValues(allRows) within a loop can also lead to high processing costs.
You are fetching values from the sheet using
```
copySheet.getRange("C1:C").getValues()
```
and
```
pasteSheet.getRange("C1:C").getValues()
```
. This approach retrieves all rows within the data range, resulting in increased processing costs.
Even after making the aforementioned modifications, there might be an error at rangeToUnify.setValues(allRows) due to a mismatch in the number of rows between the range and the values.
Lastly, the correct method should be file.moveTo(moveToG), instead of file.moveto(moveToG).

Consider implementing these changes into your script. Here's the modified version:

Modified Script:

Prior to running this script, ensure to set the variables for pasteSheet, uploadFolder, and moveToG.

function obtainAndImportData() {
  var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###"); // Provide the name of your "pasteSheet".
  var uploadFolder = DriveApp.getFolderById('###'); // Specify your folder ID.
  var moveToG = DriveApp.getFolderById('###'); // Define another folder ID.

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

Executing this script fetches Spreadsheets from the "uploadFolder". Values are retrieved from "B3:P" of the first tab in each Sheet, then appended starting from column "B" of "pasteSheet".

Note:

In this revised version, the last row from the "C" column is fetched per your original script. If you wish to utilize the last row of the data range, consider using getLastRow().

Failure of app script to retrieve data from an external spreadsheet

Answer №1

Answer №2

Areas for Improvement:

Modified Script:

Note:

References:

Similar questions

My tests are not passing because I included a compare method in the Array prototype. What steps can I take to fix this issue in either the

The WHATWG URL API allows creation of a new URL using the new URL

Express.js automatically sets timeouts for requests to static files

Issue encountered during production build in Angular 6 due to NGRX

The date displayed in moment.js remains static even after submitting a new transaction, as it continues to hold onto the previous date until

JavaScript error - Property value is not valid

Extracting Parameters using JQuery's load Method

At what point should the term "function" be included in a ReactJS component?

Is It Possible to Create Flash Content Without Using a SWF File?

Issue with timestamp in the Instagram API call to retrieve media using AJAX (GET /media/search)

How can you use DOM manipulation to extract input data from an <input type="file"> element?

What steps can be taken to ensure express Node.JS replies to a request efficiently during periods of high workload

Utilize the href attribute on an image, whether it be through a class

How to activate a button only if the specified conditions are met using VueJS 3

React developers are struggling with parsing XML data with ReactDOM

bespoke theme background hue

creating a while loop in node.js

Decoding user input parameters within a vue module

Top recommendation for utilizing $scope variables in Angular applications

WordPress AJAX call results in a response code of zero