Is there a way to import cells in a certain range for a large number of sheets-CodePudding

I have 25 tabs in a Google Spreadsheet, but this number slowly grows as more data is added. The importrange function that we are provided by default requires the specification of which sheets to import data from using something like what was outlined here: Combining multiple spreadsheets in one using IMPORTRANGE. However, is there a way (using a default function or the Apps Script) to do this for all tabs in the Google Spreadsheet, without specifying each one individually?

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the values from all sheets in a Google Spreadsheet and want to put the values to a sheet of the destination Spreadsheet. In this case, you want to set a range you want to retrieve.
You want to achieve this using Google Apps Script.

Sample script:

Please copy and paste the following script to the script editor of Google Apps Script, and set the variables. And, run the function.

function myFunction() {
  const range = "A1:E3"; // Please set the range you want to retrieve.
  const srcSpreadsheetId = "###"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "###"; // Please set destination Spreadsheet ID.
  const dstSheetName = "Sheet1"; // Please set destination sheet name.

  const values = SpreadsheetApp.openById(srcSpreadsheetId).getSheets().flatMap(s => s.getRange(range).getValues());
  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstSheetName);
  dstSheet.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

When this script is run, the values are retrieved from the specific range of all sheets from the source Spreadsheet, and the values are put to the destination sheet.

In the above script, the retrieved values to "A1" of the destination sheet. If you want to append the values, please modify as follows.

From

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

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

Unfortunately, I'm not sure whether I could correctly understand your question. So, for example, if you want to retrieve the values from all sheets except for multiple sheets, and you want to put the retrieved values to the specification sheet in the same Spreadsheet, how about the following sample script? In this case, you can use this script as a custom function. So, when you use this script, please put a custom function of =SAMPLE("A1:E3", "Sheet2,Sheet3") to a cell. In this sample arguments, the values are retrieved from the range of "A1:E3" of all sheets except for "Sheet2" and "Sheet3".
```
  function SAMPLE(range, exclude) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const excludeSheets = [ss.getActiveSheet().getSheetName(), ...exclude.split(",").map(e => e.trim())];
    return ss.getSheets().flatMap(s => {
      if (!excludeSheets.includes(s.getSheetName())) {
        return s.getRange(range).getValues();
      }
      return [];
    });
  }
```

As another pattern, if you want to retrieve the values from all sheets in a source Spreadsheet to a sheet of destination Spreadsheet, how about the following sample script? In this sample, please copy and paste the following script to the script editor of the destination Spreadsheet. And, please set the variables of source Spreadsheet ID and range you want. When this script is run, a formula is put to the cell "A1" of the destination sheet. The formula is from this thread. By this, you can see the values from all sheets in the source Spreadsheet at the destination sheet.

  function myFunction2() {
    const range = "A1:E3"; // Please set the range you want to retrieve.
    const srcSpreadsheetId = "###"; // Please set source Spreadsheet ID.

    const srcSs = SpreadsheetApp.openById(srcSpreadsheetId);
    const url = srcSs.getUrl();
    const values = srcSs.getSheets().map(s => `IMPORTRANGE("${url}","'${s.getSheetName()}'!${range}")`);
    const formula = `={${values.join(";")}}`;
    const dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the destination sheet name.
    dstSheet.clearContents().getRange(1, 1).setFormula(formula);
  }

References:

CodePudding user response：

Outclass Topic .. An A good Info ! In the event that you are hoping to move to an open to housing project in rural areas of Bahria Orchard Lahore ought to be your best option. You will constantly have magnificent diversion and local area administrations at a careful distance.