RemcoE33

Process Google Meet Attendance

Jun 22nd, 2021 (edited)
96
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function processGoogleMeetAttendance(){
  2.   //Change the 3 variables below.
  3.   const driveInputFolderId = '1QVo_pr_Wo6U4zp_qODSzg0y387WPH9Yx'
  4.   const driveProcessedFolderId = '1PSn8gXnGisV9t4xWsaPxxXhdYp0A6v1F'
  5.   const sheetName = 'csv'
  6.  
  7.   const files = DriveApp.getFolderById(driveInputFolderId).getFilesByType(MimeType.CSV)
  8.   console.log((files.hasNext()) ? `Found files` : `No files found in folder`)
  9.  
  10.   while (files.hasNext()){
  11.     const file = files.next()
  12.     console.log(`Processing: ${file.getName()}`)
  13.     const csvString = file.getBlob().getDataAsString()
  14.     let converted = Utilities.parseCsv(csvString)
  15.     const date = /"(.*?)"/.exec(converted[1][1])[1]
  16.     converted.splice(0, 4)
  17.     converted.splice(converted.length -4,4)
  18.  
  19.     const clean = converted.map(row => {
  20.       row.unshift(date)
  21.       return row.map(column => {
  22.         const noQuotes = /"(.*?)"/.exec(column)
  23.         if (noQuotes != null){
  24.           return noQuotes[1];
  25.         } else {
  26.           return column
  27.         }
  28.       })
  29.     })
  30.  
  31.    const ss = SpreadsheetApp.getActiveSpreadsheet()
  32.    const targetSheet = ss.getSheetByName(sheetName)
  33.  
  34.    targetSheet.getRange(targetSheet.getLastRow()+1,1, clean.length, clean[0].length).setValues(clean)
  35.  
  36.    file.moveTo(DriveApp.getFolderById(driveProcessedFolderId))
  37.    console.log(`Done processing ${file.getName()} and moved to processed folder`)
  38.  
  39.   }
  40.  
  41. }
Add Comment
Please, Sign In to add comment