I want to automatically accumulate all values in AAA from BBB to gg and many more subsheets.

1. It seems that importrange() cannot be used in the same sheet.
2. I think I have to use apps script, but it's difficult for me.

CodePudding user response：

Try this way

=query({bbb!A:F;ccc!A:F;ddd!A:F},"select * where Col1 is not null")

or by script

function accumulate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sh = ss.getSheetByName('AAA')
  sh.clearContents()
  var index = sh.getIndex()
  ss.getSheets().filter(s => s.getIndex() > index).forEach(function (sheet) {
    var data = sheet.getDataRange().getValues()
    sh.getRange(sh.getLastRow() 1,1,data.length,data[0].length).setValues(data)
    SpreadsheetApp.flush();
  })
}