How to get data only from all the sheets containing specific text in the name?

55 Views Asked by At

I have been trying to use ChatGPT to write and troubleshoot this Script with no luck. I have tried so many things and it always just returns data from the first sheet that matches the condition, and doesn't seem to loop and return data from any other iterations or sheets that also match the condition.

Here are some examples of sheet names:

  • "5 - Past Send Assets - 2022 Q2"
  • "1 - Dates and Assets"
  • "5 - Past Send Assets Q4 & Q1"

Here is the Script

try {
// Get the URL from cell A1
var spreadsheetURL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RunHistory").getRange("D2").getValue();

    // Open the other spreadsheet using its URL
    var otherSpreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
    
    // Initialize an empty array to store the results
    var results = [];
    
    // Get all the sheets in the other spreadsheet

var sheets = otherSpreadsheet.getSheets();

    // Loop through each sheet
    for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();
    
      // Check if the sheet name contains "5 -" or "1 -"
      if (sheetName.indexOf("5 -") !== -1 || sheetName.indexOf("1 -") !== -1) {
        // Get data from F3:F and G3:G in the current sheet
        var data = sheet.getRange("F3:F").getValues();
        var data2 = sheet.getRange("G3:G").getValues();
    
        // Append the data to the results array
        for (var j = 0; j < data.length; j++) {
          results.push([data[j][0], data2[j][0]]);
        }
      }
    }
    
    // Get the active spreadsheet and sheet
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = activeSpreadsheet.getActiveSheet();
    
    // Clear any existing data in the active sheet (starting from A2)
    activeSheet.getRange("A2:B").clearContent();
    
    // Write the results to the active sheet starting from A2
    activeSheet.getRange(2, 1, results.length, 2).setValues(results);
    
    // Display a success message
    SpreadsheetApp.getUi().alert("Data retrieved and populated successfully.");

} catch (error) {
// Display an error message if any issues occur
SpreadsheetApp.getUi().alert("An error occurred: " + error.message);
}
}\`\`\`

\*\*I tried adding various `continue;` and naming conditions such as "5 " and "5 - ", \*\*

Also I tried substituting this section

```// Append the data to the results array
        for (var j = 0; j < data.length; j++) {
          results.push([data[j][0], data2[j][0]]);```

with this
```// Create a new array for the current sheet's data
        var sheetData = [];

        // Append the data to the sheetData array
        for (var j = 0; j < data.length; j++) {
          sheetData.push([data[j][0], data2[j][0]]);
        }

        // Concatenate the sheetData array with the results array
        results = results.concat(sheetData);```
0

There are 0 best solutions below