| 67 | } |
| 68 | |
| 69 | function formatReport() { |
| 70 | auth(); |
| 71 | project = 'ENTER PROJECT'; |
| 72 | var ss = SpreadsheetApp.getActiveSpreadsheet(); |
| 73 | var aggSheet = ss.getSheetByName('Comments'); |
| 74 | var reportSheet = ss.getSheetByName('Report') || ss.insertSheet('Report'); |
| 75 | reportSheet.clear(); |
| 76 | |
| 77 | var aggData = aggSheet.getDataRange().getValues(); |
| 78 | |
| 79 | cache = CacheService.getUserCache(); |
| 80 | token = cache.get("token"); |
| 81 | if (token == "") return "ERROR"; |
| 82 | Logger.log(`Token = ${token}`); |
| 83 | url = `https://us-central1-aiplatform.googleapis.com/v1/projects/${project}/locations/us-central1/publishers/google/models/gemini-1.5-flash:generateContent` |
| 84 | data = { |
| 85 | contents: { |
| 86 | role: "USER", |
| 87 | parts: { "text": "I will provide you with YouTube Comments assess the sentiment in 5 sections. 1. Overall Sentiment of the video with positive, negative and neutral proportions stated along with 100 word overall summary. 2: Postive Sentiment - Summarise the positive sentiment and provide three reference tweets. Following the same pattern for Negative and Neutral in 3 and 4. 5: Suggestions for video improvement - Provide three key point to improve the next video we make based on the comments." + ' Do not include the video title. Do not create seperate sections for the reference tweets. Only reffer to the data provided. Full Data:' + aggData} |
| 88 | }, |
| 89 | generation_config: { |
| 90 | temperature: 1, |
| 91 | topP: 1, |
| 92 | maxOutputTokens: 1000 |
| 93 | } |
| 94 | } |
| 95 | const options = { |
| 96 | method: "post", |
| 97 | contentType: 'application/json', |
| 98 | headers: { |
| 99 | Authorization: `Bearer ${token}`, |
| 100 | }, |
| 101 | payload: JSON.stringify(data) |
| 102 | }; |
| 103 | |
| 104 | const response = UrlFetchApp.fetch(url, options); |
| 105 | if (response.getResponseCode() == 200) { |
| 106 | json = JSON.parse(response.getContentText()); |
| 107 | answer = json.candidates[0].content.parts[0].text; |
| 108 | Logger.log(answer); |
| 109 | |
| 110 | // Format the Markdown in Sheets |
| 111 | var lines = answer.split('\n'); |
| 112 | var rowIndex = 1; |
| 113 | |
| 114 | lines.forEach(function(line) { |
| 115 | var cell = reportSheet.getRange(rowIndex, 1); |
| 116 | var text = line.trim(); |
| 117 | |
| 118 | if (text.startsWith('### ')) { |
| 119 | // Handle H3 titles |
| 120 | text = text.replace('### ', ''); |
| 121 | cell.setValue(text) |
| 122 | .setFontWeight('bold') |
| 123 | .setFontSize(14) |
| 124 | .setBackground('#1c4587') // Dark Blue 3 background |
| 125 | .setFontColor('#FFFFFF'); // Set text color to white |
| 126 | } else if (text.startsWith('## ')) { |