Google Scripts Поиск и возврат ячейки

Question

У меня возникли проблемы с моим первым Google Script, и я был бы признателен за помощь.

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

Я пытался использовать этот скрипт:

/**
* Finds a value within a given range. 
* @param value The value to find.
* @param range The range to search in.
* @return A range pointing to the first cell containing the value, 
*     or null if not found.
*/
function find(value, range) {
var data = range.getValues();
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
if (data[i][j] == value) {
return range.getCell(i + 1, j + 1);
}
}
}
return null;
}

Однако он возвращает ошибку "TypeError: Не удается найти функцию getValues ​​в объекте"

Мое значение будет определено как текстовая строка, например, "ABCD"

Мой диапазон будет диапазоном нескольких листов, например, sheet1!A1:C4,sheet2!A1:C4

Функция должна возвращать лист, столбец и строку значения поиска

В настоящее время я использую формулу цикла соответствия индекса iferror для поиска по одному столбцу за раз (я использую косвенный, так как мой диапазон является переменной), но хотел бы, чтобы скрипт сделал это проще и быстрее.

=IFERROR(index(INDIRECT(Sheet1!A:A),
iferror(MATCH(A2,INDIRECT(Sheet1!B:B),0),
iferror(MATCH(A2,INDIRECT(Sheet1!C:C),0),
...ETC

Спасибо заранее за любую помощь.

Answer 1

Попробуй это:

function find(value, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = range.split("!")[0];
  var A1Ref = range.split("!")[1];
  var activeRange = ss.getSheetByName(sheet).getRange(A1Ref);
  var data = activeRange.getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == value) {
        return (ss.getSheetByName(sheet).getRange(i + 1, j + 1));
      }
    }
  }
  return null;
}

Это вернет диапазон. Вы можете изменить эту строку, чтобы получить A1Notation:

return (ss.getSheetByName(sheet).getRange(i + 1, j + 1).getA1Notation());

Answer 2

Обновление ответа Акшина Джалилова - оригинал не вернул мне правильное местоположение ячейки. Я обновил его, чтобы включить в ответ расчет местоположения ячейки.

      // original: https://stackoverflow.com/questions/9905533/convert-excel-column-alphabet-e-g-aa-to-number-e-g-25
// updated: https://gist.github.com/mark05e/29bb2b7564460059da6d3e74f08ab31f
function find(value, range) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = range.split("!")[0];
    var A1Ref = range.split("!")[1];
    
    var [cellStart, cellEnd] = A1Ref.split(":")
    var rowStartNumber = Number(cellStart.replace(/[^0-9]/g,''))
    var rowEndNumber = Number(cellEnd.replace(/[^0-9]/g,''))
    var colStart = cellStart.replace(/[^A-Za-z]/g,'')
    var colStartNumber = Number(lettersToNumber(colStart))
    var colEnd = cellEnd.replace(/[^A-Za-z]/g,'')
    var colEndNumber = Number(lettersToNumber(colEnd))

    var searchRange = ss.getSheetByName(sheet).getRange(A1Ref);
    var searchData = searchRange.getValues();
    for (var i = 0; i < searchData.length; i++) {
        for (var j = 0; j < searchData[i].length; j++) {
            if (searchData[i][j] == value) {
                return (ss.getSheetByName(sheet)
                        .getRange(rowStartNumber + i, colStartNumber + j)
                        .getA1Notation());
            }
        }
    }
    return null;
}

// https://stackoverflow.com/questions/9905533/convert-excel-column-alphabet-e-g-aa-to-number-e-g-25
function lettersToNumber(letters){
    for(var p = 0, n = 0; p < letters.length; p++){
        n = letters[p].charCodeAt() - 64 + n * 26;
    }
    return n;
}

      // Test Function
function testFind(name) {
    let x = find("Region1","Sheet1!C3:C14")
    console.log(x)
}