구글 스프레드시트 Json 데이터 시트에 표시하기

Json

문자열로 나열된 Json 데이터를 스프레드시트에 표시하는 방법을 알아보겠습니다.

목차
1. 커스텀 함수 만들기
2. 스크립트 작성하기
3. 사용예시

----

----

커스텀 함수 만들기

구글 스프레드시트를 열고 확장 프로그램에 Apps Script를 눌러서 새로운 gs 파일을 생성합니다.

spreadsheet custom. function

spreadsheet create .gs

파일이름은 원하시는 이름으로 정하시면됩니다.

스크립트 작성하기

아래의 내용을 .gs 파일에 붙여넣으시고 저장하시면 됩니다.

/**
 * Returns Json parse data
 *
 * @param {cell} Cell with json data
 * @customfunction
 */
function ImportJSONFromCell(cell, query, options) {
  return ImportJSONAdvancedFromCell(cell, query, options, includeXPath_, defaultTransform_);
}

function ImportJSONAdvancedFromCell(cell, query, options, includeFunc, transformFunc) {
  var obj = JSON.parse(cell.replace(";", ""));
  Logger.log(obj)
  
  return parseJSONObject_(obj, query, options, includeFunc, transformFunc);
}

function URLEncode(value) {
  return encodeURIComponent(value.toString());  
}

function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
  var headers = new Array();
  var data    = new Array();
  
  if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
    query = query.toString().split(",");
  }
  
  if (options) {
    options = options.toString().split(",");
  }
    
  parseData_(headers, data, "", 1, object, query, options, includeFunc);
  parseHeaders_(headers, data);
  transformData_(data, options, transformFunc);
  
  return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}

function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
  var dataInserted = false;
  
  if (isObject_(value)) {
    for (key in value) {
      if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
        dataInserted = true; 
      }
    }
  } else if (Array.isArray(value) && isObjectArray_(value)) {
    for (var i = 0; i < value.length; i++) {
      if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
        dataInserted = true;
        rowIndex++;
      }
    }
  } else if (!includeFunc || includeFunc(query, path, options)) {
    // Handle arrays containing only scalar values
    if (Array.isArray(value)) {
      value = value.join(); 
    }
    
    // Insert new row if one doesn't already exist
    if (!data[rowIndex]) {
      data[rowIndex] = new Array();
    }
    
    // Add a new header if one doesn't exist
    if (!headers[path] && headers[path] != 0) {
      headers[path] = Object.keys(headers).length;
    }
    
    // Insert the data
    data[rowIndex][headers[path]] = value;
    dataInserted = true;
  }
  
  return dataInserted;
}

function parseHeaders_(headers, data) {
  data[0] = new Array();

  for (key in headers) {
    data[0][headers[key]] = key;
  }
}

function transformData_(data, options, transformFunc) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      transformFunc(data, i, j, options);
    }
  }
}

function isObject_(test) {
  return Object.prototype.toString.call(test) === '[object Object]';
}

function isObjectArray_(test) {
  for (var i = 0; i < test.length; i++) {
    if (isObject_(test[i])) {
      return true; 
    }
  }  

  return false;
}

function includeXPath_(query, path, options) {
  if (!query) {
    return true; 
  } else if (Array.isArray(query)) {
    for (var i = 0; i < query.length; i++) {
      if (applyXPathRule_(query[i], path, options)) {
        return true; 
      }
    }  
  } else {
    return applyXPathRule_(query, path, options);
  }
  
  return false; 
};

function applyXPathRule_(rule, path, options) {
  return path.indexOf(rule) == 0; 
}

function defaultTransform_(data, row, column, options) {
  if (!data[row][column]) {
    if (row < 2 || hasOption_(options, "noInherit")) {
      data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  } 

  if (!hasOption_(options, "rawHeaders") && row == 0) {
    if (column == 0 && data[row].length > 1) {
      removeCommonPrefixes_(data, row);  
    }
    
    data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
  }
  
  if (!hasOption_(options, "noTruncate") && data[row][column]) {
    data[row][column] = data[row][column].toString().substr(0, 256);
  }

  if (hasOption_(options, "debugLocation")) {
    data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  }
}

function removeCommonPrefixes_(data, row) {
  var matchIndex = data[row][0].length;

  for (var i = 1; i < data[row].length; i++) {
    matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);

    if (matchIndex == 0) {
      return;
    }
  }
  
  for (var i = 0; i < data[row].length; i++) {
    data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
  }
}

function findEqualityEndpoint_(string1, string2, stopAt) {
  if (!string1 || !string2) {
    return -1; 
  }
  
  var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
  
  for (var i = 0; i < maxEndpoint; i++) {
    if (string1.charAt(i) != string2.charAt(i)) {
      return i;
    }
  }
  
  return maxEndpoint;
}
  
function toTitleCase_(text) {
  if (text == null) {
    return null;
  }
  
  return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}

function hasOption_(options, option) {
  return options && options.indexOf(option) >= 0;
}

----

----

사용예시

셀에 Json 문자열을 넣고 다른 셀에서 함수를 호출하여 Json 데이터를 표시합니다.

spreadsheet custom function

spreadsheet custom

다른 시트의 셀에 있는 데이터도 가져와서 사용할 수 있으니 Json 데이터를 다른 시트에 넣어두고 표시하게 되면 더 깔끔하게 사용할 수 있습니다.

구글 스프레드시트 다른 시트의 값 가져오는 함수와 간단한 예제

※ 구글 스프레드시트에서 자주 사용되는 함수의 예제들 모음

구글 스프레드시트 동적 DROPDOWN
구글 스프레드시트 COUNT
구글 스프레드시트 Json 파일로 저장하기
구글 스프레드시트 Export
구글 스프레드시트 함수 - AVERAGE
구글 스프레드시트 함수 - MATCH
구글 스프레드시트 함수 - INDEX
구글 스프레드시트 함수 - VLOOKUP
구글 스프레드시트 -다른 시트 참조
구글 스프레드시트 함수 - SUM
구글 스프레드시트 함수 - IF

서울시 청년 금융복지 상담

청년 주택드림 청약통장, 은행비교

청년도약계좌와 은행별 우대이율 조건

유니티 (Unity) Animator로 2d Animation 재생(Play)