Google Sheets Script 이벤트는 구글 Docs 스프레시트를 함수기반으로 컨트롤 할 수 있습니다. 백그라운드로 수행합니다.



1. Google Sheets Script 이벤트의 Main Context에서 GoogleSheetsControlClass 클래스 목록을 보면 함수 종류와 사용법에 대하여 알 수 있습니다.






2. Event Properties


  • Event Name : 이벤트 이름
  • Stop If Error : 에러 발생 시 중지 여부


  • Google Cloud Key : Google API 에서 다운로드 받은 Json 키 내용





3. Google Sheets API


https://developers.google.com/sheets/api



[참고 예제]


public void GoogleSheets_Script(GoogleSheetsControlClass gsc)
   {
         //문서 아이디
      string spreadsheetId = @"1BOXhmWyJL-u2iHhpFDPuIHH0gs9659fb7sniAXdeL9s";
      
      string spreadsheetId2 = @"1Cy3O5E-9-w3WiMWnBD1QcPW5sPYphBkXYEOaW-s-DYI";

      //시트 정보 가져오기
      //foreach (var item in gsc.GetSheetsList(spreadsheetId))
      //{
      //   PrintLog(item.SheetId.ToString());
      //   PrintLog(item.SheetTitle);
      //}
      
      //sheetid to title
      //PrintLog(gsc.GetSheetsName(spreadsheetId, 151070104));
      
      // title to sheetid
      //PrintLog(gsc.GetSheetsId(spreadsheetId, "설문지 응답 시트2").ToString());
      
      //시트 추가
      //gsc.AddSheet(spreadsheetId, "abc");
      //gsc.AddSheet(spreadsheetId, "abc", new SHEET_COLOR() { Red = 100, Green = 100, Blue = 100});
      
      int sheetId = 0;
      
      sheetId = Convert.ToInt32(gsc.GetSheetsId(spreadsheetId, "시트1"));
      
      //시트명 변경
      //gsc.RenameSheet(spreadsheetId, sheetId, "시트1");
      
      // 1 cell
      //var result = gsc.GetCellValues(spreadsheetId, sheetId, 0, 0);
      // 3x3 cell 
      //var result = gsc.GetCellValues(spreadsheetId, sheetId, 0, 0, 2, 2);
      // all data      
      var result = gsc.GetCellValues(spreadsheetId, sheetId);
      
      //string으로 변환
      string tot = gsc.ListObjectToClipboardString(result);

      total = tot;
      //row, col print
      //StringBuilder sb = new StringBuilder();
      //
      //for (int nRow = 0; nRow < result.Count; nRow++)
      //{
      //    if (nRow > 0)
      //        sb.Append(Environment.NewLine);
      //   for (int nCol = 0; nCol < result [nRow].Count; nCol++)
      //   {
      //       if (nCol > 0)
      //           sb.Append("\t");
      //       sb.Append("\"" + result [nRow] [nCol].ToString() + "\"");
      //       
      //      //PrintLog(nRow.ToString() + "/" + nCol.ToString() + " > " + result[nRow][nCol].ToString());
      //   }
      //}
      //
      //total = sb.ToString();
      
      //시트2 라는 시트에 total 값 붙여넣기
      //gsc.SetCellValues(spreadsheetId, Convert.ToInt32(gsc.GetSheetsId(spreadsheetId, "시트2")), 0, 0, tot, "\t", PASTE_TYPE.PASTE_NORMAL);
      
      //gsc.CopyPaste(spreadsheetId, Convert.ToInt32(gsc.GetSheetsId(spreadsheetId, "시트1")), 0, 0, null, null, Convert.ToInt32(gsc.GetSheetsId(spreadsheetId, "시트2")), 0, 0, null, null, PASTE_TYPE.PASTE_NORMAL);
      
      //
      List<SHEET_ROWDATA> lstVar = new List<SHEET_ROWDATA>();
      for (int nRow = 0; nRow < 10; nRow++)
      {
         SHEET_ROWDATA row = new SHEET_ROWDATA();
         for (int nCol = 0; nCol < 5; nCol++)
         {
            SHEET_CELLDATA cell = new SHEET_CELLDATA();
            //cell.Note = "Note123";
            //cell.Note = string.Empty;
            //string
            cell.CellType = CELLDATA_TYPE.STRING;
            cell.StringValue = nRow.ToString() + "/" + nCol.ToString();
            //number
            //cell.CellType = CELLDATA_TYPE.NUMBER;
            //cell.NumberValue = nRow;
            row.ColumnDatas.Add(cell);
         }   
         lstVar.Add(row);
      }
      gsc.SetCellValues(spreadsheetId, Convert.ToInt32(gsc.GetSheetsId(spreadsheetId, "시트2")), 7, 3, lstVar);
      
   }
}




[피벗 테이블 예제]


public void GoogleSheets_Script(GoogleSheetsControlClass gsc)
    {
        String spreadsheetId = "1gBNFftzMaqQ_xt2jqGYXmPYEsEC0vWxrXxIM9bI8Hkw";
        int sheetId = 0;
        
        sheetId = (int)gsc.GetSheetsId(spreadsheetId, "시트4");
        
        // 피벗 테이블 삭제
        gsc.DeletePivotTable(spreadsheetId, sheetId, 8, 0);
        
        
        // 피벗 테이블 생성
        //string SpreadsheetId
        
        //int SourceSheetId
        //int? SourceStartColumnIndex
        //int? SourceStartRowIndex
        //int? SourceEndColumnIndex
        //int? SourceEndRowIndex
        
        //int TargetSheetId
        //int? TargetColumnIndex
        //int? TargetRowIndex
        
        //PIVOT_VALUELAYOUT ValueLayout
        
        //List<PIVOT_SET_ROWCOLUMN> GroupRows
        //List<PIVOT_SET_ROWCOLUMN> GroupColumns
        //List<PIVOT_SET_FILTER> Filter
        //List<PIVOT_SET_VALUE> Values
        
        List<PIVOT_SET_ROWCOLUMN> GroupRows = new List<PIVOT_SET_ROWCOLUMN>();
        {
            PIVOT_SET_ROWCOLUMN row = new PIVOT_SET_ROWCOLUMN();
            row.Label = "Categroy";
            row.ShowTotals = true;
            row.SourceColumnOffset = 0;
            row.SortOrder = SORT_ORDER.ASCENDING;
            GroupRows.Add(row);
        }
        {
            PIVOT_SET_ROWCOLUMN row = new PIVOT_SET_ROWCOLUMN();
            row.Label = "Model";
            row.ShowTotals = true;
            row.SourceColumnOffset = 1;
            row.SortOrder = SORT_ORDER.ASCENDING;
            GroupRows.Add(row);
        }
        
        List<PIVOT_SET_ROWCOLUMN> GroupColumns = new List<PIVOT_SET_ROWCOLUMN>();
        {
            PIVOT_SET_ROWCOLUMN col = new PIVOT_SET_ROWCOLUMN();
            col.Label = "Qua";
            col.ShowTotals = true;
            col.SourceColumnOffset = 3;
            col.SortOrder = SORT_ORDER.ASCENDING;
            GroupColumns.Add(col);
        }
        
        List<PIVOT_SET_FILTER> Filter = new List<PIVOT_SET_FILTER>();
        {
            PIVOT_SET_FILTER ftr = new PIVOT_SET_FILTER();
            ftr.SourceColumnOffset = 4;
            ftr.VisibleValues.Add("West");
            ftr.VisibleValues.Add("East");
            Filter.Add(ftr);
        }
        
        List<PIVOT_SET_VALUE> Values = new List<PIVOT_SET_VALUE>();
        {
            PIVOT_SET_VALUE va = new PIVOT_SET_VALUE();
            va.Label = "Qua Sum";
            va.SourceColumnOffset = 3;
            va.SummarizeFunction = PIVOT_SUMMARIZEFUNCTION.COUNT;
            va.CalculatedDisplayType = PIVOT_DISPLAYTYPE.PIVOT_VALUE_CALCULATED_DISPLAY_TYPE_UNSPECIFIED;
            Values.Add(va);
        }
        
        gsc.AddPivotTable(spreadsheetId
                          , sheetId, 0, 0, 6, 19
                          , sheetId, 8, 0
                          , PIVOT_VALUELAYOUT.HORIZONTAL
                          , GroupRows, GroupColumns, Filter, Values);
    
        
    }

Created with the Personal Edition of HelpNDoc: Free EPub and documentation generator