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