Блог

Я хочу создать скрипт Google, который позволит мне перечислить следующее в отдельной электронной таблице (в столбцах):

• столбец A, получите имя всех электронных таблиц в данной папке
• столбец B, получите URL-адрес указанных электронных таблиц
• В столбце C найдите последнюю ячейку первого листа в каждой электронной таблице
• В столбце D создайте формулу importrange с приведенной выше информацией

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

Я выполнил столбцы A и B. Однако я понятия не имею, как получить последнюю из каждой электронной таблицы. Я предполагаю, что столбец D будет легче записать, поскольку единственной переменной в формуле для importrange является lastBelow — это то, что у меня есть:

 function gatheredInfo(){
  var result = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var folder = DriveApp.getFolderById('FOLDERID');
  var list = [];
  list.push(['Name','URL']);
  var files = folder.getFiles();
  while (files.hasNext()){
    file = files.next();
    var row = []
    row.push(file.getName(),file.getUrl())
    list.push(row);
  }
   result.getRange(1,1,list.length,list[0].length).setValues(list);
}
 

Любая помощь / рекомендации искренне приветствуются. Я застрял на этом некоторое время.

Чтобы перейти к «последней ячейке», вам нужно указать скрипту открывать каждую электронную таблицу, пока вы находитесь в цикле «while», и для этого вы можете использовать lastrow и lastcolumn .

Редактировать примечание* В зависимости от того, что вы на самом деле подразумеваете под «последней ячейкой» и как структурированы ваши данные, вам может потребоваться настроить поиск ячейки и обозначения.. но это приведет вас к правильной части вашего кода и концепции*

Это раздел, который я добавил для этого в ваш код…

 //Checks if the file type is a Google Sheet
    if(DriveApp.getFileById(fileID).getMimeType() == MimeType.GOOGLE_SHEETS){
      //Gets the current spreadsheet and first sheet to open to look for the last column and row
    var CurrSheet = SpreadsheetApp.openById(fileID).getSheets()[0];
      
      //find last row, column, and put that into A1 notation
    var lastrow = CurrSheet.getLastRow();
    var lastColumn= CurrSheet.getLastColumn();
      //if you don't need A1 notation then this will spit out the row number and the column number.. this next line is what gives you A1 most easily.
    var lastcell = CurrSheet.getRange(lastrow,lastColumn).getA1Notation();
    
    var row = []
    row.push(file.getName(),file.getUrl(),lastcell);
    list.push(row);
    }

 

Сначала я добавил проверку, чтобы убедиться, что файл в папке является Google Sheet (это mime-тип). Это поможет вам предотвратить ошибки, если что-то, что не является таблицей Google, попадет в эту папку.

Затем мы открываем лист с помощью fileID, который вы уже установили. Я по умолчанию установил это для первого листа в каждой электронной таблице (это this part .getSheets()[0] ). Вы сказали, что у вас есть только один лист, но просто вставьте.

Затем используйте это, чтобы получить последнюю строку и последний столбец, и я объединил их в поддельный диапазон, чтобы быстрее перевести его в формат A1. Затем это просто помещается в последний столбец вашего уже установленного массива.

Вот весь код. Я тестировал с папкой, которая у меня есть, со смешанными типами файлов, и она работает правильно.

 function gatheredInfo(){
  var result = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var folder = DriveApp.getFolderById('FOLDERID');
  var list = [];
  list.push(['Name','URL','Cell']);
  var files = folder.getFiles();
  while (files.hasNext()){
    file = files.next();
    
    var fileID = file.getId();
    
    //Checks if the file type is a Google Sheet
    if(DriveApp.getFileById(fileID).getMimeType() == MimeType.GOOGLE_SHEETS){
      //Gets the current spreadsheet and first sheet to open to look for the last column and row
    var CurrSheet = SpreadsheetApp.openById(fileID).getSheets()[0];
      
      //find last row, column, and put that into A1 notation
    var lastrow = CurrSheet.getLastRow();
    var lastColumn= CurrSheet.getLastColumn();
      //if you don't need A1 notation then this will spit out the row number and the column number.. this next line is what gives you A1 most easily.
    var lastcell = CurrSheet.getRange(lastrow,lastColumn).getA1Notation();
    
    var row = []
    row.push(file.getName(),file.getUrl(),lastcell);
    list.push(row);
    }
  }
   result.getRange(1,1,list.length,list[0].length).setValues(list);
}
 

Что касается создания формулы importrange, вы можете сделать это в самом листе с помощью формулы или в скрипте приложения, просто введя текстовую строку, которая объединяет все эти переменные.

Однако я не включил это, потому что у меня нет всей информации для составления успешной формулы importrange(). Вам нужен URL-адрес, имя листа и диапазон, и я не знаю, какие у вас имена листов или совпадают ли они. Если вам это тоже нужно, вы можете добавить это прямо под переменной CurrSheet (var SheetName=CurrSheet.getSheetName();). Это даст вам имя первого листа в вашей электронной таблице, из которого вы извлекаете информацию о ячейке. Вы можете просто проанализировать это в своем скрипте приложения, чтобы либо вывести предложенную формулу importrange(), либо поместить ее в качестве собственного столбца, чтобы использовать формулу на листе.

Если вам нужна более подробная информация об этом, дайте мне знать. Я просто не хотел слишком много предполагать о вашей ситуации.