Google Sheet에서 셀에 반복 단어 추출하는 방법

Google 시트에서 셀에 반복되는 단어를 다른 셀에 추출하고 싶다면, Google Apps Script를 사용하여 이를 처리할 수 있습니다. Google Apps Script는 Google 시트의 기능을 확장하고 사용자 정의 기능을 추가할 수 있는 JavaScript 기반의 플랫폼입니다.

 

다음은 셀에 있는 반복되는 단어를 추출하여 다른 셀에 '#단어' 형식으로 나열하는 간단한 스크립트 예시입니다:

 

 

function extractHashtags() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A1'); // 예를 들어 A1 셀에서 단어를 추출
  var text = range.getValue();
  var words = text.split(/\s+/); // 공백으로 단어 분리
  var wordCount = {};
  var hashtags = [];

  // 단어의 빈도 수 계산
  for (var i = 0; i < words.length; i++) {
    var word = words[i];
    if (wordCount[word]) {
      wordCount[word]++;
    } else {
      wordCount[word] = 1;
    }
  }

  // 반복되는 단어 추출 및 해시태그 형식으로 변환
  for (var word in wordCount) {
    if (wordCount[word] > 1) { // 단어가 두 번 이상 반복되는 경우
      hashtags.push('#' + word);
    }
  }

  // 결과를 다른 셀(예: B1)에 저장
  sheet.getRange('B1').setValue(hashtags.join(' '));
}

 

이 스크립트는 주어진 셀(예: 'A1')의 텍스트를 가져와서 공백을 기준으로 단어를 분리합니다. 각 단어의 빈도 수를 계산한 후, 두 번 이상 나타난 단어에 대해 '#단어' 형식으로 변환하여 다른 셀(예: 'B1')에 저장합니다.

 

Google Apps Script를 사용하려면 Google 시트에서 '도구' > '스크립트 편집기'를 선택하여 위 스크립트를 붙여넣고 저장한 후, 스크립트 함수(extractHashtags)를 실행하면 됩니다.

 

행별로 특정 열에서 열로(D열을 읽고 J열에 생성)

행별로 D열의 내용을 분석하여 J열에 결과를 생성하고자 한다면, Google Apps Script에서 스크립트를 약간 수정해야 합니다. 이 수정은 스크립트가 D열의 각 행을 순회하면서 반복되는 단어를 찾아 J열에 해당 행에 결과를 저장하도록 할 것입니다.

 

다음은 수정된 스크립트입니다:

 

function extractHashtagsToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // 시트의 모든 데이터 범위 가져오기
  var values = dataRange.getValues(); // 데이터 값 가져오기

  for (var i = 0; i < values.length; i++) {
    var text = values[i][3]; // D열의 내용 (인덱스는 0부터 시작하므로 3은 D열)
    var words = text.split(/\s+/); // 공백으로 단어 분리
    var wordCount = {};
    var hashtags = [];

    // 단어의 빈도 수 계산
    for (var j = 0; j < words.length; j++) {
      var word = words[j];
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    // 반복되는 단어 추출 및 해시태그 형식으로 변환
    for (var word in wordCount) {
      if (wordCount[word] > 1) { // 단어가 두 번 이상 반복되는 경우
        hashtags.push('#' + word);
      }
    }

    // 결과를 J열에 저장
    sheet.getRange(i + 1, 10).setValue(hashtags.join(' ')); // J열은 10번째 열
  }
}

 

이 스크립트는 전체 시트의 데이터 범위를 가져와서 각 행에 대해 D열의 내용을 분석합니다. 각 행에서 반복되는 단어를 찾아 '#단어' 형식으로 변환한 후, 해당 행의 J열에 결과를 저장합니다.

 

위 스크립트를 Google 시트의 '도구' > '스크립트 편집기'에 붙여넣은 후 저장하고, extractHashtagsToColumn 함수를 실행하면 D열의 각 행에서 반복되는 단어가 해시태그 형식으로 J열에 저장됩니다.

 

단어 빈도수 변경

단어의 빈도 수를 5번 이상으로 제한하려면, 스크립트 내에서 단어의 빈도 수를 확인하는 조건을 수정해야 합니다. 현재 스크립트에서는 단어가 두 번 이상 반복될 때 해시태그를 생성하도록 설정되어 있습니다. 이를 5번 이상으로 변경하기 위해 해당 조건을 wordCount[word] > 1 에서 wordCount[word] >= 5 로 변경하면 됩니다.

 

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

 

function extractHashtagsToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // 시트의 모든 데이터 범위 가져오기
  var values = dataRange.getValues(); // 데이터 값 가져오기

  for (var i = 0; i < values.length; i++) {
    var text = values[i][3]; // D열의 내용 (인덱스는 0부터 시작하므로 3은 D열)
    var words = text.split(/\s+/); // 공백으로 단어 분리
    var wordCount = {};
    var hashtags = [];

    // 단어의 빈도 수 계산
    for (var j = 0; j < words.length; j++) {
      var word = words[j];
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    // 반복되는 단어 추출 및 해시태그 형식으로 변환
    for (var word in wordCount) {
      if (wordCount[word] >= 5) { // 단어가 다섯 번 이상 반복되는 경우
        hashtags.push('#' + word);
      }
    }

    // 결과를 J열에 저장
    sheet.getRange(i + 1, 10).setValue(hashtags.join(' ')); // J열은 10번째 열
  }
}

 

이 수정된 스크립트는 D열의 각 행에 있는 텍스트에서 단어의 빈도 수를 계산하고, 그 빈도 수가 5 이상인 단어에 대해 '#단어' 형식으로 변환하여 J열에 저장합니다.

 

Google 시트의 '도구' > '스크립트 편집기'에서 이 스크립트를 실행하면, 원하는 기능을 수행할 수 있습니다.

 

특수문자 ., {, ,[, 쉼표, 마침표, ', ", 등은 포함 안되게 하려면

특수 문자를 포함하지 않고 순수한 단어만 빈도수를 계산하려면, 텍스트를 단어로 분리할 때 정규 표현식을 사용하여 특수 문자를 제외하고 단어만 추출하도록 스크립트를 수정해야 합니다. 이를 위해 단어를 분리하는 부분에서 정규 표현식을 사용하여 알파벳과 숫자만 포함하는 단어를 매칭하도록 하겠습니다.

 

아래는 특수 문자를 제외하고 단어를 추출하는 수정된 스크립트입니다:

 

function extractHashtagsToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // 시트의 모든 데이터 범위 가져오기
  var values = dataRange.getValues(); // 데이터 값 가져오기

  for (var i = 0; i < values.length; i++) {
    var text = values[i][3]; // D열의 내용 (인덱스는 0부터 시작하므로 3은 D열)
    // 알파벳과 숫자만 포함하는 단어를 매칭하는 정규 표현식 사용
    var words = text.match(/\b[a-zA-Z0-9]+\b/g);
    if (!words) continue; // 매칭되는 단어가 없는 경우 다음 행으로 넘어감
    var wordCount = {};
    var hashtags = [];

    // 단어의 빈도 수 계산
    for (var j = 0; j < words.length; j++) {
      var word = words[j].toLowerCase(); // 대소문자 구분 없이 처리
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    // 반복되는 단어 추출 및 해시태그 형식으로 변환
    for (var word in wordCount) {
      if (wordCount[word] >= 5) { // 단어가 다섯 번 이상 반복되는 경우
        hashtags.push('#' + word);
      }
    }

    // 결과를 J열에 저장
    sheet.getRange(i + 1, 10).setValue(hashtags.join(' ')); // J열은 10번째 열
  }
}

 

 

이 스크립트는 \b[a-zA-Z0-9]+\b 정규 표현식을 사용하여 텍스트에서 알파벳과 숫자만 포함하는 단어를 추출합니다. \b는 단어 경계를 나타내고, [a-zA-Z0-9]+는 하나 이상의 알파벳 또는 숫자를 매칭합니다. 이렇게 추출된 단어들에 대해 빈도수를 계산하고, 그 빈도수가 5 이상인 경우에만 해시태그를 생성하여 J열에 저장합니다.

 

한글 분리하기

한글 단어를 포함하여 처리하려면 정규 표현식을 수정하여 한글 문자 범위도 포함시켜야 합니다. 한글의 경우 유니코드 범위가 가(U+AC00)부터 힣(U+D7A3)까지입니다. 이 범위를 정규 표현식에 추가하여 한글 단어도 추출할 수 있도록 스크립트를 수정하겠습니다.

 

한글의 경우 단어 경계(\b)가 영어나 숫자와는 다르게 작동할 수 있기 때문에 이를 고려해야 합니다. 또한, 대소문자 구분이 없는 한글 처리를 위해 toLowerCase() 함수의 적용을 영어에만 한정해야 할 필요가 있습니다.

 

다음은 한글 단어 추출을 위해 수정된 스크립트입니다:

 

function extractHashtagsToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // 시트의 모든 데이터 범위 가져오기
  var values = dataRange.getValues(); // 데이터 값 가져오기

  for (var i = 0; i < values.length; i++) {
    var text = values[i][3]; // D열의 내용 (인덱스는 0부터 시작하므로 3은 D열)
    // 알파벳, 숫자, 한글 단어를 매칭하는 정규 표현식 사용
    var words = text.match(/[a-zA-Z0-9가-힣]+/g);
    if (!words) continue; // 매칭되는 단어가 없는 경우 다음 행으로 넘어감
    var wordCount = {};
    var hashtags = [];

    // 단어의 빈도 수 계산
    for (var j = 0; j < words.length; j++) {
      var word = words[j];
      // 영어 단어에만 소문자 변환 적용
      if (/^[a-zA-Z]+$/.test(word)) {
        word = word.toLowerCase();
      }
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    // 반복되는 단어 추출 및 해시태그 형식으로 변환
    for (var word in wordCount) {
      if (wordCount[word] >= 5) { // 단어가 다섯 번 이상 반복되는 경우
        hashtags.push('#' + word);
      }
    }

    // 결과를 J열에 저장
    sheet.getRange(i + 1, 10).setValue(hashtags.join(' ')); // J열은 10번째 열
  }
}

 

 

이 스크립트는 [a-zA-Z0-9가-힣]+ 정규 표현식을 사용하여 알파벳, 숫자, 한글 단어를 모두 포함하여 추출합니다. 단어 경계 \b를 제거하여 한글 단어 추출에 문제가 생기지 않도록 했습니다. 또한, 영어 단어에만 toLowerCase() 함수를 적용하여 대소문자 구분 없이 처리합니다. 이렇게 추출된 단어들에 대해 빈도수를 계산하고, 그 빈도수가 5 이상인 경우에만 해시태그를 생성하여 J열에 저장합니다.

 

한글 텍스트에서 특정 전치사나 접속사 제외시키기

한글 텍스트에서 특정 전치사나 접속사와 같은 불용어(Stop Words)를 제외하는 기능을 추가하려면, 먼저 제외하고 싶은 불용어 목록을 정의한 후, 이 단어들을 텍스트 분석 과정에서 건너뛰도록 스크립트를 수정해야 합니다.

 

아래는 불용어를 제외하고 단어 빈도수를 계산하는 수정된 스크립트입니다:

 

ffunction extractHashtagsToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // 시트의 모든 데이터 범위 가져오기
  var values = dataRange.getValues(); // 데이터 값 가져오기

  // 불용어 목록 정의
  var stopwords = ['은', '는', '이', '가', '수', '있습니다', '을', '를', '와', '과', '에', '에서'];

  for (var i = 0; i < values.length; i++) {
    var text = values[i][3]; // D열의 내용 (인덱스는 0부터 시작하므로 3은 D열)
    var words = text.match(/[a-zA-Z0-9가-힣]+/g);
    if (!words) continue; // 매칭되는 단어가 없는 경우 다음 행으로 넘어감
    var wordCount = {};
    var hashtags = [];

    // 단어의 빈도 수 계산
    for (var j = 0; j < words.length; j++) {
      var word = words[j];
      // 불용어 제외
      if (stopwords.indexOf(word) >= 0) continue;

      // 영어 단어에만 소문자 변환 적용
      if (/^[a-zA-Z]+$/.test(word)) {
        word = word.toLowerCase();
      }
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    // 반복되는 단어 추출 및 해시태그 형식으로 변환
    for (var word in wordCount) {
      if (wordCount[word] >= 5) { // 단어가 다섯 번 이상 반복되는 경우
        hashtags.push('#' + word);
      }
    }

    // 결과를 J열에 저장
    sheet.getRange(i + 1, 10).setValue(hashtags.join(' ')); // J열은 10번째 열
  }
}

 

 

 

이 스크립트는 한글, 영어, 숫자 단어를 추출하면서 미리 정의된 불용어 목록(stopwords)에 있는 단어들을 제외합니다. 이렇게 추출된 단어들에 대해 빈도수를 계산하고, 그 빈도수가 5 이상인 경우에만 해시태그를 생성하여 J열에 저장합니다.

불용어 목록은 필요에 따라 추가하거나 변경할 수 있습니다.

 

단어 뒤에 붙는 접미사나 조사를 제거

단어 뒤에 붙는 접미사나 조사를 제거하는 것은 한국어의 문법적 특성을 고려해야 하는 복잡한 작업입니다. 일반적인 정규 표현식으로는 이를 완벽하게 처리하기 어렵지만, 간단한 패턴을 사용하여 일부 흔히 사용되는 접미사나 조사를 제거하는 정도의 처리는 가능합니다.

 

예를 들어, '~는', '에서', '을', '합니다'와 같은 접미사나 조사를 제거하려면, 해당 패턴을 정규 표현식으로 정의하고 텍스트에서 이를 제거하는 코드를 추가할 수 있습니다. 하지만, 이 방법은 언어의 모든 복잡성을 다루지는 못하며, 때로는 의미 있는 단어의 일부를 잘못 제거할 위험이 있습니다.

 

아래는 간단한 접미사와 조사를 제거하기 위해 수정된 스크립트입니다:

 

function extractHashtagsToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // 시트의 모든 데이터 범위 가져오기
  var values = dataRange.getValues(); // 데이터 값 가져오기

  // 제거할 접미사와 조사 패턴 정의
  var pattern = /(는|에서|을|합니다)$/;

  for (var i = 0; i < values.length; i++) {
    var text = values[i][3]; // D열의 내용 (인덱스는 0부터 시작하므로 3은 D열)
    var words = text.match(/[a-zA-Z0-9가-힣]+/g);
    if (!words) continue; // 매칭되는 단어가 없는 경우 다음 행으로 넘어감
    var wordCount = {};
    var hashtags = [];

    // 단어의 빈도 수 계산
    for (var j = 0; j < words.length; j++) {
      var word = words[j];
      // 정의된 접미사와 조사 제거
      word = word.replace(pattern, '');

      // 영어 단어에만 소문자 변환 적용
      if (/^[a-zA-Z]+$/.test(word)) {
        word = word.toLowerCase();
      }
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    // 반복되는 단어 추출 및 해시태그 형식으로 변환
    for (var word in wordCount) {
      if (wordCount[word] >= 5) { // 단어가 다섯 번 이상 반복되는 경우
        hashtags.push('#' + word);
      }
    }

    // 결과를 J열에 저장
    sheet.getRange(i + 1, 10).setValue(hashtags.join(' ')); // J열은 10번째 열
  }
}

 

 

이 스크립트는 정의된 정규 표현식 패턴(pattern)을 사용하여 각 단어의 끝에 있는 특정 접미사나 조사를 제거합니다. 다만, 이 방법은 간단한 패턴에 국한되며, 모든 경우를 커버하지는 못합니다. 보다 정교한 처리를 위해서는 한글 형태소 분석 라이브러리를 사용하는 것이 좋으나, Google Apps Script에서는 직접적으로 지원하지 않습니다.

---

결과 중에 반복되는 단어와 함께 #만 나오는 태그 현상 해결 방법

중간에 #만 나오는 경우, 특정 조건 하에 단어가 빈 문자열('')로 처리되어, 빈도수는 기준을 충족하지만 실제 단어가 없는 상태로 해시태그가 생성되는 것으로 보입니다. 이는 다음과 같은 시나리오에서 발생할 수 있습니다:

 

• 단어 정제 과정에서의 문제: 단어가 정제 과정을 거치며 모든 문자가 제거되어 결과적으로 빈 문자열이 되었을 수 있습니다. 이는 정규식 또는 불용어 처리 과정에서 발생할 수 있습니다. 특히, 단어의 접미사나 조사를 제거하는 로직(word.replace(pattern, ''))이 원래 단어를 완전히 제거할 수 있습니다.
• 단어 빈도수 계산의 오류: 단어가 빈 문자열('')로 처리되었음에도 불구하고, 이 빈 문자열에 대한 빈도수가 충분히 높아 해시태그 생성 조건을 만족하는 경우입니다. 예를 들어, 여러 셀에서 빈 문자열이 동일하게 처리되어 빈 문자열의 빈도수가 증가할 수 있습니다.

 

이 문제를 해결하기 위한 방법은 다음과 같습니다:

1. 단어 정제 로직 검토: 단어를 정제하는 로직에서 빈 문자열이 생성되지 않도록 확인해야 합니다. 정규식이나 불용어 처리 로직을 재검토하고 필요한 경우 조정해야 합니다.
2. 빈 문자열 처리 추가: wordCount를 계산하기 전에 단어가 빈 문자열인지 확인하고, 빈 문자열은 계산에서 제외하도록 로직을 수정합니다.
3. 해시태그 생성 로직 개선: 해시태그를 생성하기 전에 단어가 비어 있지 않은지 확인하고, 빈 문자열에 대해서는 해시태그를 생성하지 않도록 합니다.

확인 해보면서 찾은 주요 수정 포인트는 다음과 같습니다:

• 빈 문자열 확인 추가: 단어가 빈 문자열인 경우, 단어 빈도수 계산에서 제외합니다. 이는 wordCount 계산 로직에서 수행할 수 있습니다.
• 해시태그 생성 로직 수정: 해시태그를 생성할 때, 빈 문자열이나 유효하지 않은 단어에 대해서는 해시태그를 생성하지 않도록 합니다.

 

다음은 수정된 extractHashtagsToColumn 함수의 일부입니다:

function extractHashtagsToColumn() {
  // ... 기존 코드 ...

  for (var i = 0; i < values.length; i++) {
    var text = values[i][1]; // B열의 내용
    var words = text.match(/[a-zA-Z0-9가-힣]+/g);
    if (!words) continue; // 매칭되는 단어가 없는 경우 다음 행으로 넘어감
    var wordCount = {};
    var hashtags = [];

    for (var j = 0; j < words.length; j++) {
      var word = words[j];
      word = word.replace(pattern, ''); // 정의된 접미사와 조사 제거
      if (stopwords.indexOf(word) >= 0 || word === '') continue; // 불용어 제외 및 빈 문자열 제외

      if (/^[a-zA-Z]+$/.test(word)) {
        word = word.toLowerCase();
      }
      if (wordCount[word]) {
        wordCount[word]++;
      } else {
        wordCount[word] = 1;
      }
    }

    for (var word in wordCount) {
      if (wordCount[word] >= 8 && word !== '') { // 단어가 8번 이상 반복되고 빈 문자열이 아닌 경우
        hashtags.push('#' + word);
      }
    }

    sheet.getRange(i + 1, 7).setValue(hashtags.join(' ')); // 7번째 열
  }
}

 

이러한 수정을 통해 빈 문자열에 대한 해시태그 생성을 방지하고, 유효한 단어에 대해서만 해시태그를 생성하게 됩니다. 이렇게 하면 #만 나오는 문제를 해결할 수 있을 것입니다.