App Script new Google Sheet creation issue

I am trying to write an App Script that takes string data from multiple different spreadsheets (completely separate documents) and puts them all in a new spreadsheet. When I run the logger, it shows me all the data I want. I want each piece of data to show up in Column A, but when I run my script, it only puts 1 data point in the spreadsheet instead of all of them. Can someone give me some guidance? Here is my code:

function pullTogether() {
  var files = DriveApp.getFolderById('Folder ID').searchFiles('title != "nothing"');
  const rangeName = 'Sheet1!B2:C';
  while(files.hasNext()){
    var xFile = files.next();
    var name = xFile.getId();
    
    const values = Sheets.Spreadsheets.Values.get(name, rangeName).values;
    for (const row in values) {
      var a1 = (values[row][0]);
      Logger.log(a1);
    
  var ss = SpreadsheetApp.openById("ID of new spreadsheet"); //I have the real ID in my code
  var cell = ss.getRange("A2");
  cell.setValue(a1);
     
}
}

}

Answer

I believe your goal is as follows.

• You want to retrieve the values from the column “B” of each Spreadsheet under the specific folder.
• You want to put the retrieved values to the column “A” of the destination sheet.

Modification points:

• About but when I run my script, it only puts 1 data point in the spreadsheet instead of all of them., when I saw your script, the retrieved value is always put to the cell “A2” of the destination sheet. I think that this might be the reason for your issue.

• In your script, I thought that when the following flow is used, the process cost will become low. By this flow, your issue can be also removed.

• In your situation, even when Sheets API is not used, the script might work using getValues().

When these points are reflected in your script, it becomes as follows.

Modified script:

Please set the folder ID and the destination Spreadsheet ID.

function pullTogether() {
  // Retrieve values from each Spreadsheet.
  var values = [];
  var files = DriveApp.getFolderById('Folder ID').searchFiles(`title != 'nothing' and mimeType='${MimeType.GOOGLE_SHEETS}'`);
  var sheetName = 'Sheet1'
  while (files.hasNext()) {
    var xFile = files.next();
    var sheet = SpreadsheetApp.open(xFile).getSheetByName(sheetName);
    if (sheet) {
      var v = sheet.getRange("B2:B" + sheet.getLastRow()).getValues();
      values = [...values, ...v];
    }
  }

  // Put values to the destination sheet.
  var ss = SpreadsheetApp.openById("ID of new spreadsheet"); //I have the real ID in my code
  var dstSheet = ss.getSheets()[0];
  dstSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

Note:

• Although I’m not sure about your actual situation, when the above script didn’t work by the large data, please modify as follows.

  • From

      dstSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
    

  • To

      Sheets.Spreadsheets.Values.update({ values }, ss.getId(), `'${dstSheet.getSheetName()}'!A2`, { valueInputOption: "USER_ENTERED" });
    

References:

• getValues()
• setValues(values)