Apps Script로 Google Sheets의 값들을 Insert 문으로 생성

 

 

 

 

나는 엑셀 수식을 이용해, Table의 Insert문을 아래와 같이 생성해왔다.

 

 

 

Google Sheets와 Apps Script를 이용해 작업을 최소화 하고자 한다.

 

 

아래의 데이터들을 INSERT 구문으로 생성하고자 한다.

첫 번째 열은 데이터 타입이다.

 

 

먼저 첫 번째 행의 data type들만 colTypeArr로 따로 뽑아낸다.

function createInsertState () {
  const sheet = SpreadsheetApp.getActive().getSheetByName("queryMaker");

  const getRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  const values = getRange.getValues();

  let colTypeArr = [];
  for ( i in values ) {
    const value = values[i];
    if ( i < 1 ) {
      for ( j in value ) {
        colTypeArr.push(value[j]);
      }
    }
  }
  console.log(colTypeArr);
}

 

 

 

다음으로 2번째 행부터 loop를 돌린다.

string type인 경우에는 더블 퀘테이션을 붙이고.

아닌 경우에는 값만 붙이도록 한다.

function createInsertState () {
  const sheet = SpreadsheetApp.getActive().getSheetByName("queryMaker");

  const getRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  const values = getRange.getValues();

  let colTypeArr = [];
  for ( i in values ) {
    const value = values[i];
    if ( i < 1 ) {
      for ( j in value ) {
        colTypeArr.push(value[j]);
      }
    } else {
      var this_string = "(";
      for ( j in value ) {
        const this_type = colTypeArr[j];

        if ( this_type == "string" ) {
          this_string += '"' + value[j] + '"';
        } else {
          this_string += value[j];
        }

        if ( j == value.length-1 ) {
          this_string += "),";
        } else {
          this_string += ", ";
        }
      }

      console.log(this_string);
    }
  }
}

 

 

if 문 분기 처리 부분은 아래와 같이 3항 연산자로 깔끔하게 변경 할 수 있다.

this_string += ( this_type == "string" ) ? '"' + value[j] + '"' : value[j];
this_string += (j == value.length-1) ? ")," : ", ";

 

 

이제 생성한 string을 리스트에 담아 setValues()를 해준다.

function createInsertState () {
  const sheet = SpreadsheetApp.getActive().getSheetByName("queryMaker");

  const getRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  const values = getRange.getValues();

  let colTypeArr = [];
  let stringResult = [];
  let qs = new Array(values.length-1);
  for ( i in values ) {
    const value = values[i];
    if ( i < 1 ) {
      for ( j in value ) {
        colTypeArr.push(value[j]);
      }
    } else {
      qs[i-1] = new Array(1);
      var this_string = "(";
      for ( j in value ) {
        const this_type = colTypeArr[j];
        this_string += ( this_type == "string" ) ? '"' + value[j] + '"' : value[j];
        this_string += (j == value.length-1) ? ")," : ", ";
      }
      qs[i-1][0] = this_string;
      stringResult.push(this_string);
    }
  }

  const setRange = sheet.getRange(2, sheet.getLastColumn()+1, sheet.getLastRow()-1,1);
  setRange.setValues(qs);
}

 

 

 

 

 

행과 열이 추가되어도, 자동으로 insert 문을 생성 해 줄 수 있다.

 

 

반복 실행이 가능하도록.

1열 마지막 컬럼이 비어있으면(getLastColumn()으로 확인),

아래의 모든 데이터를 지우는 로직을 추가한다.

function createInsertState2 () {
  const sheet   = SpreadsheetApp.getActive().getSheetByName("queryMaker");
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();

  const lastRange = sheet.getRange(1, lastCol, 1, 1);
  const lastValue = lastRange.getValue();
  
  if ( lastValue == "" || !lastValue ) {
    const deleteRange = sheet.getRange(1, lastCol, lastRow, 1);
    deleteRange.clearContent();
  }

  const getRange = sheet.getRange(1, 1, lastRow, lastCol);
  const values   = getRange.getValues();

  let colTypeArr   = [];
  let stringResult = [];
  let qs = new Array(values.length-1);
  for ( i in values ) {
    const value = values[i];
    if ( i < 1 ) {
      for ( j in value ) {
        colTypeArr.push(value[j]);
      }
    } else {
      qs[i-1] = new Array(1);
      var this_string = "(";
      for ( j in value ) {
        const this_type = colTypeArr[j];
        this_string += ( this_type == "string" ) ? '"' + value[j] + '"' : value[j];
        this_string += (j == value.length-1) ? ")," : ", ";
      }
      qs[i-1][0] = this_string;
      stringResult.push(this_string);
    }
  }
  const setRange = sheet.getRange(2, sheet.getLastColumn()+1, lastRow-1,1);
  setRange.setValues(qs);
}