Google App Script를 사용해 Google Spreadsheet에서 사용자 정의 함수를 통해 배열 처리하기!

 

Google Spreadsheet에서는 Google Apps Script로 생성된 사용자 정의 기능을 사용할 수 있다. 

사용자 정의 기능을 사용해 스프레드시트에 내장된 기능을 확장하는 기능을 만들 수 있는데, 어느 날 배열을 사용해 사용자 정의 함수를 실행해야 하는 상황이 발생한다면 어떻게 해야 할까?

상황1

상황을 가정하자면 아래와 같다.

• "A1:A5" 셀에는 각각 "a1", "a2", "a3", "a4" 및 "a5" 값이 있습니다.
• 셀 "B1:B5"에는 각각 "b1", "b2", "b3", "b4" 및 "b5"의 값이 있습니다.

custom script를 보면,

function SAMPLE(argument1, argument2) {
  return JSON.stringify({ argument1, argument2 });
}

 

사용자 정의 함수 =SAMPLE(A1,B1)을 셀 "C1"에 넣으면 다음 결과가 얻어집니다.

 

 

완전 간단히 function을 만들고 그 function을 바로 사용할 수 있습니다!

 

배열을 사용하여 사용자 정의 함수

사용자 정의 함수 =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5))를 셀 "C1"에 넣으면 다음 결과가 얻어집니다.

 

이 경우 "A1:A5" 및 "B1:B5"의 값은 ["a1", "a2", "a3", "a4", "a5"] 및 ["b1", "b2", 각각 "b3", "b4", "b5"]입니다. 이에 따라 스크립트의 인수1, 인수2의 인수는 ["a1", "a2", "a3", "a4", "a5"] 및 ["b1", "b2", "b3", "b4입니다. ", "b5"]와 같은 결과가 반환됩니다. 이런 결과를 원하시면 커스텀 함수와 스크립트를 수정하지 않고도 사용하실 수 있습니다.

 

그러나 예상 결과가 {"argument1":"a1","argument2":"b1"}, {"argument1":"a2","argument2":"b2"}, {"argument1":"a3인 경우 ","argument2":"b3"},,, “C1:C5”에서 각각 사용자 정의 함수 또는 스크립트를 수정해야 합니다. 

 

사용자 정의 기능 수정

이 패턴에서는 사용자 정의 함수를 수정하여 예상한 결과를 얻습니다. 이 경우 스크립트를 수정할 필요가 없습니다.

수정된 Custom 함수는 다음과 같습니다.

 

=MAP(A1:A5,B1:B5,LAMBDA(value1,value2,SAMPLE(value1,value2)))

 

이 공식을 사용하면 다음과 같은 결과가 나온다.

 

 

 

이 이미지에서 MAP 함수를 이용하여 예상한 결과를 얻을 수 있음을 알 수 있다.

 

사용자 정의 함수의 스크립트 수정

이 패턴에서는 사용자 정의 함수의 원본 스크립트를 수정하여 최종 결과를 얻습니다. 이 경우 =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5))에서 사용자 정의 함수를 수정할 필요가 없습니다.

수정된 스크립트는 다음과 같습니다.

function SAMPLE(argument1, argument2) {
  return argument1.map((e, i) =>
    JSON.stringify({ argument1: e[0], argument2: argument2[i][0] })
  );
}

 

=ARRAYFORMULA(SAMPLE(A1:A5,B1:B5))의 사용자 정의 함수를 셀 “C1”에 넣으면 다음 결과가 얻어집니다.

 

 

하지만 이 수정된 스크립트에서 =SAMPLE(A1,B1)을 사용하면 TypeError: Argument1.map is not a function과 같은 오류가 발생합니다. "A1"과 "B1"의 값은 배열이 아니기 때문입니다. =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5)) 및 =SAMPLE(A1,B1)을 모두 사용하려는 경우 다음 수정 사항을 사용할 수 있습니다.

 

function SAMPLE(argument1, argument2) {
  if ([argument1, argument2].every((e) => Array.isArray(e))) {
    return argument1.map((e, i) =>
      JSON.stringify({ argument1: e[0], argument2: argument2[i][0] })
    );
  }
  return JSON.stringify({ argument1, argument2 });
}

 

이 경우 다음 스크립트를 사용할 수도 있습니다.

function SAMPLE(argument1, argument2) {
  if ([argument1, argument2].every((e) => Array.isArray(e))) {
    return argument1.map((e, i) => SAMPLE(e[0], argument2[i][0]));
  }
  return JSON.stringify({ argument1, argument2 });
}

 

추가정보

추가적으로 안내드리자면 현 단계에서는 현재 스프레드시트 사양으로 인해 셀 개수가 많은 경우 커스텀 기능을 사용할 수 없습니다. Custom 함수 사용 시 셀 개수 관련 오류가 발생하는 경우, 다음과 같이 Custom 함수 대신 Google Apps Script를 직접 사용해 보시기 바랍니다.

const sheetName = "Sheet1"; // Please set your sheet name.

function onEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart > 2) return;
  myFunction(sheet);
}

function myFunction(sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)) {
  const range = sheet.getRange("A1:B" + sheet.getLastRow());
  const values = range.getValues().map(([argument1, argument2]) =>
    [[argument1, argument2].includes("") ? [null] : JSON.stringify({ argument1, argument2 })]
  );
  range.offset(0, 2, values.length, 1).setValues(values);
}

 

이 스크립트를 테스트할 때 시트 이름을 sheetName으로 설정하고 myFunction을 실행하세요. 이에 따라 결과 값이 "C" 열에 입력됩니다. 또한 "A1:A" 및 "B1:B" 셀을 편집하면 단순 트리거의 onEdit 함수에 의해 "C" 열의 값이 업데이트됩니다.