Last active
November 11, 2024 12:35
-
-
Save shun0211/230921287e7f384e2345b957a6b331d6 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const getCompanyNames = () => { | |
| const book = SpreadsheetApp.getActiveSpreadsheet(); | |
| // スプレットシートの名前を「営業リスト」にする必要あり | |
| const sheet = book.getSheetByName("営業リスト"); | |
| const data = sheet.getDataRange().getValues(); | |
| // 1行目はヘッダーなのでスキップし、会社名のみ抽出 | |
| return data.slice(1).map(row => row[0]).filter(name => name); // 空白行を除外 | |
| }; | |
| const fetchApiResponse = (companyName) => { | |
| const url = 'https://api.dify.ai/v1/workflows/run'; | |
| // プロジェクトの設定 -> スクリプトプロパティから DIFY_TOKEN を設定 | |
| const token = PropertiesService.getScriptProperties().getProperty('DIFY_TOKEN'); | |
| const payload = { | |
| inputs: { | |
| company_name: companyName | |
| }, | |
| response_mode: 'blocking', | |
| user: 'abc-123' | |
| }; | |
| const options = { | |
| method: 'post', | |
| contentType: 'application/json', | |
| headers: { | |
| 'Authorization': `Bearer ${token}` | |
| }, | |
| payload: JSON.stringify(payload), | |
| muteHttpExceptions: true | |
| }; | |
| try { | |
| const response = UrlFetchApp.fetch(url, options); | |
| return JSON.parse(response.getContentText()); | |
| } catch (error) { | |
| Logger.log(`Error for ${companyName}: ${error}`); | |
| return null; // エラーハンドリング | |
| } | |
| }; | |
| const extractApiData = (response) => { | |
| if (response && response.data && response.data.outputs) { | |
| return { | |
| phoneNumber: response.data.outputs.phone_number, | |
| homepageUrl: response.data.outputs.homepage_url, | |
| companyOverview: response.data.outputs.text | |
| }; | |
| } | |
| console.log("データが不正") | |
| return null; | |
| }; | |
| const updateSheetWithCompanyData = (companyName, companyData) => { | |
| console.log("companyName", companyName) | |
| console.log("companyData", companyData) | |
| if (!companyData) return; // データがない場合は何もしない | |
| const book = SpreadsheetApp.getActiveSpreadsheet(); | |
| const sheet = book.getSheetByName("営業リスト"); | |
| const data = sheet.getDataRange().getValues(); | |
| // 会社名が一致する行を見つけてデータを更新 | |
| for (let i = 1; i < data.length; i++) { // 1から始めてヘッダー行をスキップ | |
| if (data[i][0] === companyName) { | |
| sheet.getRange(i + 1, 2).setValue(companyData.homepageUrl); // ホームページ列（2列目） | |
| sheet.getRange(i + 1, 3).setValue(companyData.phoneNumber); // 電話番号列（3列目） | |
| sheet.getRange(i + 1, 4).setValue(companyData.companyOverview); // 会社概要列（4列目） | |
| } | |
| } | |
| }; | |
| const sendApiRequestsAndUpdateSheet = () => { | |
| const companyNames = getCompanyNames(); | |
| console.log("companyNames", companyNames) | |
| companyNames.forEach(companyName => { | |
| const response = fetchApiResponse(companyName); | |
| const companyData = extractApiData(response); | |
| updateSheetWithCompanyData(companyName, companyData); | |
| }); | |
| }; | |
| sendApiRequestsAndUpdateSheet() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment