Shearepoint에 정해진 엑셀 템플릿을 저장하고, Power Automate를 사용하여 템플릿에 리스트 데이터 및 이미지를 입력하려고 합니다. 이를 위해서는 엑셀 온라인을 사용해야 하고, Office Script를 작성하여 간단하게 데이터를 입력할 수 있습니다. Sharepoint 목록과 Office Script 기본 내용은 아래 링크에 있습니다.
1. 기본적으로 모델기반앱을 사용하면 사용할 수 있는 연락처를 사용하고, 경력 테이블을 추가했습니다. 경력테이블 생성 방법은 생략하겠습니다.
2. 저의 경우 경력 테이블은 Name, 시작일, 종료일, 내용을 넣고 연락처와 관계를 생성했습니다. 연락처:경력(1:N)으로 관계를 생성했습니다.
3. 다음은 Sharepoint에 엑셀 파일을 생성합니다.
4. 아래 이미지와 같이 사진, 기본 데이터, 경력 리스트를 입력하려고 합니다. 그리고 자동화 탭의 새 스크립트 버튼을 클릭하여 새로운 Office Script를 작성합니다.
5. 아래와 같이 Office Script를 작성합니다. 기본적인 Java Script 지식이 있다면 쉽게 알아볼 만큼 간단한 코드입니다.
코드의 설명은 주석을 확인하시면 됩니다.
function main(workbook: ExcelScript.Workbook,
// 파라미터 설정
fullname: string,
gendercode: string,
familystatuscode: string,
spousesname: string,
birthdate: string,
anniversary: string,
img: string,
career: NewCareer[]) {
// 엑셀 Sheet 가져오기
let sheet1 = workbook.getWorksheet("Sheet1");
// 정해진 위치에 데이터 입력하기
sheet1.getRange("E3").setValue(fullname);
sheet1.getRange("E4").setValue(gendercode);
sheet1.getRange("E5").setValue(familystatuscode);
sheet1.getRange("E6").setValue(spousesname);
sheet1.getRange("E7").setValue(birthdate);
sheet1.getRange("E8").setValue(anniversary);
// 이미지 입력
let image = sheet1.addImage(img);
image.setTop(30);
image.setLeft(30);
image.setHeight(120);
image.setWidth(120);
// 리스트 입력
const eduOffset = 11; //11번째 행부터 리스트 입력
for (let i = 0; i < career.length; i++) {
const currentCareer = career[i];
const formattedTest =
[[
currentCareer.new_name,
currentCareer.new_start_dt,
currentCareer.new_end_dt,
currentCareer.new_content
]];
const eduCell = `B${eduOffset + i}:E${eduOffset + i}`;
sheet1.getRange(eduCell).setValue(formattedTest);
}
}
// 경력 인터페이스
interface NewCareer {
new_name: string,
new_content: string,
new_start_dt: string,
new_end_dt: string,
}6. 여기까지 엑셀에서 해야 할 작업은 모두 끝났고, 이제 Power Automate에서 아래와 같이 흐름을 만들었습니다.
간단하게 설명하자면, 아래와 같습니다.
ID 기준으로 행 가져오기 -> 연락처, 경력 테이블 조회
변수 초기화 -> 경력 데이터를 입력할 배열 변수 초기화
각각에 적용 -> 경력 데이터를 알맞게 변환하여 변수 설정
스크립트 실행 -> 아까 엑셀에서 만들었던 Office Script를 실행
7. 연락처를 조회할 ID를 미리 복사하여 입력하고, 쿼리 확장은 경력을 조회하기 위한 내용입니다. SQL로 생각한다면 Join이라고 생각하면 편할 것 같습니다. 필요한 데이터만 조회할 수 있도록 경력에 대한 컬럼도 설정합니다.
8. 경력을 입력할 변수 이름을 List로 지정하고 변수를 초기화합니다.
각각에 적용에 다음과 같이 출력을 입력합니다. 기본적으로 쿼리확장을 통해서 가져왔던 경력 리스트는 동적 콘텐츠 추가에서 찾을 수가 없기 때문에 직접 식에 입력합니다.
각각에 적용 출력 -> outputs('ID_기준으로_행_가져오기')?['body/new_career_Contact_Contact']
경력 리스트에 대한 배열 변수에 추가합니다. 경력 리스트의 컬럼 또한 동적 콘텐츠에서 추가할 수가 없기 때문에 아래와 같이 입력합니다. new_name, new_content는 바로 입력하면 되고, 날짜 관련 컬럼은 UTC 시간 기준으로 불러오기 때문에 9시간을 더해주고, 날짜 형식도 지정해 줍니다.
{
"new_name": items('각각에_적용')?['new_name'],
"new_start_dt": formatDateTime(addHours(items('각각에_적용')?['new_start_dt'],9),'yyyy-MM-dd'),
"new_end_dt": formatDateTime(addHours(items('각각에_적용')?['new_end_dt'],9),'yyyy-MM-dd'),
"new_content": items('각각에_적용')?['new_content']
}
9. 다음은 엑셀 파일에서 작성했던 Office Script를 실행하는 작업입니다.
만들었던 엑셀 파일의 경로와 스크립트를 선택하면, 파라미터로 입력했던 컬럼들이 자동으로 생기고, 해당 데이터를 입력해 주면 됩니다. career의 경우 위에서 작성한 List 변수를 입력합니다.
10. 모든 작업이 끝났고, 오른쪽 위해 테스트 버튼을 클릭하여 수동으로 실행합니다.
11. 모든 작업이 정상적으로 실행되었다면 만들었던 엑셀파일은 열어서 데이터를 확인합니다.
12. 정상적으로 이름, 성별, 결혼여부, 배우자이름, 생일, 기념일, 경력 리스트가 입력되었습니다. 하지만 성별과 결혼여부는 남자, 기혼으로 입력되면 좋겠다고 생각할 것입니다.
13. 제대로 된 값을 확인하기 위해 Power Automate 실행 내역을 보고, 원시 출력 표시를 클릭합니다.
14. 성별의 경우 아래와 같이 있는데, 위에서 입력했던 값은 gendercode였긴 때문에 gendercode@OData.Community.Display.V1.FormattedValue를 입력해야 합니다. 기본적으로 동적 콘텐츠에서 추가할 수 있으면 간편하지만 현재는 그렇게 선택을 할 수가 없습니다.
15. 그렇기 때문에 식에 다음과 같이 성별과, 결혼여부를 다시 입력해 줍니다.
성별 -> outputs('ID_기준으로_행_가져오기')?['body/gendercode@OData.Community.Display.V1.FormattedValue']
결혼 여부 -> outputs('ID_기준으로_행_가져오기')?['body/familystatuscode@OData.Community.Display.V1.FormattedValue']
16. 모든 작업을 마치고, 위에서 했던 방법으로 다시 테스트를 하면 정상적으로 데이터가 입력된 것을 확인할 수 있습니다.
이렇게 간단한 코드로 엑셀 파일에 데이터를 입력하는 방법을 알아봤습니다.
실제 보고서 양식을 입력하고 자동화한다면, 위에서 했던 작업 외에 추가적으로 엑셀파일을 복사해서 새로 만든 후 새로 만든 파일의 스크립트를 실행하기만 하면 됩니다.
'Power Automate' 카테고리의 다른 글
| isInt, isFloat을 사용하여 Power Automate에서 정수, 부동 소수점 확인하기 (0) | 2022.10.20 |
|---|---|
| 엑셀 온라인의 자동화(Office Script), Sharepoint 목록의 데이터를 조회해서 Automate에서 엑셀 생성 자동화 하기 (0) | 2022.10.19 |
| Power Automate 흐름 실행 기록에서 트리거 출력 값 추가해 보기 (0) | 2022.10.11 |
| Power Automate Teams Flow 봇 동작에서 Hyperlink 사용하기 (0) | 2022.10.09 |
| Power Automate Teams Flow 봇을 활용한 승인 요청(결재) 만들기 (1) | 2022.10.08 |
댓글