회사에서 구글 스프레드시트로 작업을 하다보면, 

자주사용하는 구글 시트는 맵으로 관리하는게 편하다.


나는 원가 계산 담당자로 일할때,

월별 원가 계산을 위한 파일을 하나의 map파일로 관리를했다.


대충 아래와 같은 모양이었다.


LookupURL데이터범위
2023년 12월원재료https://docs.google.com/spreadsheets/d/1A5C7016k2XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX23_12!C8:AB200
2023년 12월부자재https://docs.google.com/spreadsheets/d/6060BEc4n0XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'2023년 12월'!A2:N1300
2023년 12월생산지시https://docs.google.com/spreadsheets/d/H374y4iA5EXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX생산지시(월마감)!U2:AJ600
2023년 12월재공품https://docs.google.com/spreadsheets/d/OPmQB42475XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX재공품수불부!B4:AN310
2023년 12월매입https://docs.google.com/spreadsheets/d/I0QfVU7G4MXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'유상자재(12월)'!A4:M
2023년 12월완제품_천안https://docs.google.com/spreadsheets/d/dw7OU57u6TXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'화장품재고(공정재고연동)'!B3:BU300
2023년 12월공정SThttps://docs.google.com/spreadsheets/d/s02ur5fCYNXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'23년 12월'!B28:M44



'───────────────────────────────────────────────


원가계산을 끝내고 나서는 각 수불부의 데이터가 변경되면 안되기 때문에 엑셀로 백업해놓았다.

각 파일마다 조회해서 파일 > 다운로드 를선택하기고 귀찮아서.. ChatGPT에 자동화 코드를 물어보았다.


구글 스프레드시트에 a1셀부터 a5셀까지 월별 매출관리 구글 시트 url이 입력되어 있어. 각각을 xlsx 파일로 저장하는 구글 맵스 스크립트를 작성해줘 


꽤 괜찮은 코드를 작성해줬다.

 function convertAndSaveSheets() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var urls = sheet.getRange("A1:A5").getValues();


  urls.forEach(function(url, index) {

    if (url[0] !== "") {

      var externalSs = SpreadsheetApp.openByUrl("url[0]);

      var blob = convertSpreadsheetToXlsx(externalSs.getId());

      DriveApp.createFile(blob).setName("월별 매출 " + (index + 1) + ".xlsx");

    }

  });

}


function convertSpreadsheetToXlsx(spreadsheetId) {

  var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx";

  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url, {

    headers: {

      'Authorization': 'Bearer ' + token

    }

  });

  return response.getBlob();

}



그래도 너무 대충 물어봤던지.. 수정할게 많아 보인다.

아예 처음부터 필요한 조건을 충분히 제시하고 코드를 작성하게 해보자. 


챗GPT에서 새로운 토픽을 만들고  아래와 같이 질문했다.

구글 스프레드 시트에 입력돼 있는 구글 시트 URL을 참조해서 각각을 엑셀 파일로 저장할꺼야. 아래 조건을반영해서 앱스 스크립트를 작성해줘     


구글 시트 URL이 입력된 구글 스프레드 시트 : https://docs.google.com/spreadsheets/d/1A5C7016k2XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

구글 시트 URL이 입력된 범위 : '구글시트'!C4:C10

저장할 엑셀 파일 명 : '구글시트'!B4:B10

엑셀 파일을 저장할 구글드라이브 URL :  https://drive.google.com/drive/folders/19dwC1Icy4XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 



한번에 마음에 쏙 드는 답변을 얻진 못했지만.. 계속 질문을 추가하면서 아래의 코드를 작성했다.  

욕심을 많이 부렸던 것인지.. 저장된 파일 이름이 좀 길다. 나중에 원천 데이터를 찾아갈 수있는 단서는 달아놨다.

("2023년 12월원재료_1fsYejNmlihLXzt5nYdLr8JFpujPFcr8369smJfBmFCA_20231231_제조팀(길똥이)_12월 원재료 수불명세서.xlsx") 


 // 구글 스프레드시트 URL 목록을 이용해서 각각을 엑셀(.xlsx)파일로 특정 구글 드라이브에 저장하는 코드

// made by joowon oh(abyul@naver.com, http://abyul.com/) @ 2024-01-25 00:09:42
function saveGoogleSheetsAsExcel() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1A5C7016k2XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit#gid=55282498');
  //var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('구글시트');
  var folderId = '19dwC1Icy4XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ';
  //var urlRange = sheet.getRange('C4:C10');
  //var fileNamesRange = sheet.getRange('B4:B10');
  var urlRange = sheet.getRange('C4');
  var fileNamesRange = sheet.getRange('B4');
  var urls = urlRange.getValues();
  var fileNames = fileNamesRange.getValues();

  urls.forEach(function(url, index) {
    Logger.log(url[0]);
    if (url[0] !== '') {
      var externalSs = SpreadsheetApp.openByUrl(url[0]);
      var ssID = externalSs.getId();
      var originalName = DriveApp.getFileById(ssID).getName();
      var blob = convertSpreadsheetToXlsx(ssID);
      var folder = DriveApp.getFolderById(folderId);
      var newFile = folder.createFile(blob).setName(fileNames[index][0] + '_' + ssID + '_' + originalName + '.xlsx');
      Logger.log("Files have been saved to Drive. Please visit the following URL to view them: https://drive.google.com/drive/folders/" + folderId);
    }
  });
}
// 구글 스프레드시트의 id를 받아서 엑셀 파일용 blob으로 변환해주는 외부함수
// made by joowon oh(abyul@naver.com, http://abyul.com/) @ 2024-01-25 00:09:42
function convertSpreadsheetToXlsx(spreadsheetId) {
  var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx";
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });
  return response.getBlob();
}


이 게시물을...
profile