Ошибка не удалось найти диапазон или лист для импорта

Всем привет, меня зовут Артем Медведев, я представляю компанию Helpexcel.pro. Мы занимаемся созданием управленческих таблиц и умеем их интегрировать с другими приложениями.

На самом деле очень много задач, связанных с управленческими отчетами можно решить без программирования. Мы периодически публикуем материалы о том, как самостоятельно создать управленческий учет в Гугл таблицах. Делюсь тут ссылкой на соответствующий раздел сайта. Там пошаговая инструкция с разбором формул.

Кейс:
У каждого сотрудника своя таблица, где записываются учетные операции. Нужно сделать так, что бы в одной таблице собиралась информация из таблиц сотрудников в один сводный отчет.

Прикрепляю небольшой видеообзор итогового решения, что бы было понятней о чем речь)

Вот ссылка на папку с таблицами, которые будет описаны далее.

Рассмотрим ситуацию, когда с целью разграничения прав у каждого сотрудника своя таблица, где фиксируются учетные операции.

И есть таблица руководителя, содержащая форму отчета.

Усложним задачу тем, что представление данных в таблице руководителя задается взаимосвязанными выпадающими списками и диапазонами дат.

Шаг 1

Для начала нужно сделать так, что бы данные, которые водятся в таблицы сотрудников появлялись в таблице руководителя.

Для этого создаем в таблице руководителя ровно столько листов, сколько таблиц сотрудников. Данные будем подтягивать формулой IMPORTRANGE()

Посмотрите короткий обзор применения формулы в данной ситуации.

В таблице «Отчетность» на листе «Данные» поставьте курсор в ячейку D3 и пропишите там формулу по аналогии с видеозаписью.

Подробнее о формуле:

=IMPORTRANGE(«Ссылка на таблицу»; «Диапазон, который нужно отобразить из этой таблицы»)

Обязательно заключайте аргументы в кавычки!

Пример функции:

=importrange(«https://docs.google.com/spreadsheets/d/14GIMUMieJBNiUGFEcukx0q0VwhdjzzQ_6R_JSWXbzNs/edit #gid=0″;»Данные!b:g»)

Для самопроверки воспользуйтесь готовой формулой

Шаг 2

В шаге 1 мы сделали так, что бы при обновлении данных в таблицах сотрудников, в таблице руководителя отображались все изменения.

Теперь нужно сделать так, что бы отчет заполнялся актуальными данными из таблиц сотрудников.

Для этого хорошо подходить формула СУММЕСЛИМН()

Подробнее о формуле:

=СУММЕСЛИМН(диапазон суммирования;диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

И эту формулу нужно приписать относительно каждой таблицы.

Вид формулы в ячейке отчета:

= СУММЕСЛИМН1+СУММЕСЛИМН2+СУММЕСЛИМН3…СУММЕСЛИМНN

Про формулу СУММЕСЛИМН и про то как ее использовать для разных вычислений напишу статью немного позже.

А пока прикрепляю запись вебинара, где мы очень подробно разбираем IMPORTRANGE, СУММЕСЛИМН, ЕСЛИ, FILTER для создания модели отчета.

Если хотите получить записи курса по Гугл таблицам, то можно посмотреть информацию на этой страничке.

Google Spreadsheet.
I dynamically remove old sheet and I create (I use sheet.copyTo() function) a new sheet with the same name, but IMPORTRANGE formula can’t find (periodically) new Sheets.

=ImportRange("ssId", "Data!A1:B1")

This script works fine but other Spreadsheets which fetch data from this Spreadsheet by IMPORTRANGE formula periodically can’t find new (generated) sheets:

=QUERY(IFERROR(IMPORTRANGE("ssId", "Data!A:AU")),"SELECT * ", 0)

This formula doesn’t return anything!

function copySpreadSheet(sourceId, targetId) {
try {
var sourceSS = SpreadsheetApp.openById(sourceId);
SpreadsheetApp.setActiveSpreadsheet(sourceSS);
var sourceSheets = sourceSS.getSheets();

var targetSS = SpreadsheetApp.openById(targetId);
SpreadsheetApp.setActiveSpreadsheet(targetSS);
var targetSheets = targetSS.getSheets();

//you can't delete all sheets!
var timestamp = new Date().getTime();
var tempName = "TEMPORARY-FOR-DELETE-" + timestamp;
targetSS.insertSheet(tempName);    

for(var i = 0; i < targetSheets.length; i++) {
  targetSS.deleteSheet(targetSheets[i]);
}

var sourceCharts = [];
var newSheetCharts = [];
var newSheet = null;
var newSheetName = "";
for(var i = 0; i < sourceSheets.length; i++) {
  newSheet = sourceSheets[i].copyTo(targetSS)
  newSheetName = newSheet.getName().replace("Copy of ", "");
  newSheet.setName(newSheetName);

  /*
  newSheetCharts = newSheet.getCharts();

  sourceCharts = sourceSheets[i].getCharts();
  for(var j = 0; j < sourceCharts.length; j++) {

   /* Server Error:
   var chart = newSheetCharts[j].modify()
    .setChartType(sourceCharts[j].getType())
    .addRange(sourceCharts[j].getRange())
    .build();
   newSheet.updateChart(chart);
   */

    //insertImage(blob, column, row, offsetX, offsetY)

    /*
    newSheet.insertImage(
      sourceCharts[j].getBlob(),
      sourceCharts[j].getContainerInfo().getAnchorColumn(),
      sourceCharts[j].getContainerInfo().getAnchorRow(),
      sourceCharts[j].getContainerInfo().getOffsetX(),
      sourceCharts[j].getContainerInfo().getOffsetY()
    );
    */
 /* } */

}

targetSS.deleteSheet(targetSS.getSheetByName(tempName)); //remove a temporary sheet  

//Adding Date Stamp:
targetSS.insertSheet("Last time updated").hideSheet().getRange(1, 1).setValue(new Date());

SpreadsheetApp.flush();
Utilities.sleep(500); 

return targetSS;

} catch (err) {
  Logger.log(err.toString());
 }
}

I tried to «cheat» the Spreadsheet:

var querySheet = dataSourceSS.getSheetByName("Query");
querySheet.activate();
Logger.log(formula);
querySheet.getRange(2, 1).setFormula("=Minute(Now())"); //any FORMULA 
Utilities.sleep(1000);
querySheet.getRange(2, 1).setFormula(formula);

It still doesn’t work!

Google Spreadsheet.
I dynamically remove old sheet and I create (I use sheet.copyTo() function) a new sheet with the same name, but IMPORTRANGE formula can’t find (periodically) new Sheets.

=ImportRange("ssId", "Data!A1:B1")

This script works fine but other Spreadsheets which fetch data from this Spreadsheet by IMPORTRANGE formula periodically can’t find new (generated) sheets:

=QUERY(IFERROR(IMPORTRANGE("ssId", "Data!A:AU")),"SELECT * ", 0)

This formula doesn’t return anything!

function copySpreadSheet(sourceId, targetId) {
try {
var sourceSS = SpreadsheetApp.openById(sourceId);
SpreadsheetApp.setActiveSpreadsheet(sourceSS);
var sourceSheets = sourceSS.getSheets();

var targetSS = SpreadsheetApp.openById(targetId);
SpreadsheetApp.setActiveSpreadsheet(targetSS);
var targetSheets = targetSS.getSheets();

//you can't delete all sheets!
var timestamp = new Date().getTime();
var tempName = "TEMPORARY-FOR-DELETE-" + timestamp;
targetSS.insertSheet(tempName);    

for(var i = 0; i < targetSheets.length; i++) {
  targetSS.deleteSheet(targetSheets[i]);
}

var sourceCharts = [];
var newSheetCharts = [];
var newSheet = null;
var newSheetName = "";
for(var i = 0; i < sourceSheets.length; i++) {
  newSheet = sourceSheets[i].copyTo(targetSS)
  newSheetName = newSheet.getName().replace("Copy of ", "");
  newSheet.setName(newSheetName);

  /*
  newSheetCharts = newSheet.getCharts();

  sourceCharts = sourceSheets[i].getCharts();
  for(var j = 0; j < sourceCharts.length; j++) {

   /* Server Error:
   var chart = newSheetCharts[j].modify()
    .setChartType(sourceCharts[j].getType())
    .addRange(sourceCharts[j].getRange())
    .build();
   newSheet.updateChart(chart);
   */

    //insertImage(blob, column, row, offsetX, offsetY)

    /*
    newSheet.insertImage(
      sourceCharts[j].getBlob(),
      sourceCharts[j].getContainerInfo().getAnchorColumn(),
      sourceCharts[j].getContainerInfo().getAnchorRow(),
      sourceCharts[j].getContainerInfo().getOffsetX(),
      sourceCharts[j].getContainerInfo().getOffsetY()
    );
    */
 /* } */

}

targetSS.deleteSheet(targetSS.getSheetByName(tempName)); //remove a temporary sheet  

//Adding Date Stamp:
targetSS.insertSheet("Last time updated").hideSheet().getRange(1, 1).setValue(new Date());

SpreadsheetApp.flush();
Utilities.sleep(500); 

return targetSS;

} catch (err) {
  Logger.log(err.toString());
 }
}

I tried to «cheat» the Spreadsheet:

var querySheet = dataSourceSS.getSheetByName("Query");
querySheet.activate();
Logger.log(formula);
querySheet.getRange(2, 1).setFormula("=Minute(Now())"); //any FORMULA 
Utilities.sleep(1000);
querySheet.getRange(2, 1).setFormula(formula);

It still doesn’t work!