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

Forwarding to another page following an AJAX post request to a Django view

Enhancing jQuery functionality: Ensuring newly added elements are aware of existing functions

Issues are arising with Jquery Scripts when running through Selenium in IE 9, resulting in the error message SCRIPT5009: '$' is undefined. However, these scripts are functioning correctly when executed directly in the web browser

Utilizing the outcome of an AJAX call in JavaScript

Issue with splitting an array and eliminating commas - angular/ReactJS

How can I accurately show the server time and timezone in JS/jQuery regardless of the user's location?

JavaScript code that triggers a function when the mouse is moved over a description box on a

Vue 3 has a known issue where scoped styles do not get applied correctly within the content of a <slot> element

Angular 8 delivers an observable as a result following a series of asynchronous requests

Adjusting canvas height in Storybook - Component does not fit properly due to low canvas height

JavaScript - Issue arises when evaluating the sine of complex numbers of large magnitudes

cdkDropList does not function properly when used with ng-template in a dynamic component list

Div with sidebar that sticks

With vuejs, only one place can control the changing of v-model

JavaScript does not display checkbox values

Linking Two HTML Components in Angular 4 with Identical Values

Are there any options available for customizing the animation settings on the UI-bootstrap's carousel feature?

a guide on expanding a submenu in a shiny dashboard sidebar without using automated functions

What is the proper way to utilize "three.module.js"?

Invoke a CoffeeScript function within a jQuery function