How to export a table with addresses of houses from several sheets to a Google map?

Question

Google Maps

Everyone is good!
It is required to display (highlight with a comment) the houses of clients on the city map.
There is a Google table with addresses of clients' houses, there are several sheets in it - each sheet is a street.
How to export the whole table on the map to a layer (or each sheet to a separate layer)?

And then when exporting the table to the map, only the first sheet is pulled up
Thank you in advance

Answer 1

Try to simply collect all the data on one sheet.
If there are so many sheets, you can try to create a formula automatically.

1. Create a new sheet in the Table and move it to the top
   
2. Create a script project
   
3. Add code to this project
   
4. Add the following formula to a cell on the worksheet=GETFORMULA("A:D")
   
5. Copy the result of this formula into cell A1 of the first sheet of the spreadsheet
   

The code

function GETFORMULA(nota) {
  const sheet = SpreadsheetApp.getActiveSheet().getName();
  const list = SpreadsheetApp.getActive()
    .getSheets()
    .map((sheet) => sheet.getName())
    .filter((name) => name !== sheet)
    .map((name) => `'${name}'!${nota}`)
    .join('; ');
  return `=QUERY({${list}};"where Col1<>''";)`;
}

Example https://docs.google.com/spreadsheets/d/1CKEyvbDRsr...
Create and copy the text obtained from the formula

Result