ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
1. 申請一個 API keyhttps://scribehow.com/shared/Creating_a_new_secret_key_for_OpenAI_platform__BALFOVVuS1SMCe2JI6hSwQ
2
2. 用你的 API key 取代掉左邊 Secret Key 裏面的內容，然後將內容全選複製起來const SECRET_KEY = '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'; // Replace with your OpenAI API key
const TEMPERATURE = 0;
const MAX_TOKENS = 2000;
const MODEL = "gpt-4";

function ChatGPT(prompt, temperature = TEMPERATURE, max_tokens = MAX_TOKENS, model = MODEL, outputDirection = "right") {
const url = "https://api.openai.com/v1/chat/completions";
const payload = {
model: model,
messages: [
{ role: "user", content: prompt }
],
temperature: temperature,
max_tokens: max_tokens,
};
const options = {
contentType: "application/json",
headers: { Authorization: "Bearer " + SECRET_KEY },
payload: JSON.stringify(payload),
};
const res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
return res.choices[0].message.content.trim();
}

function runChatGPT() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveRange(); // Get the entire selected range
var numRows = range.getNumRows();
var numCols = range.getNumColumns();

for (var i = 1; i <= numRows; i++) {
for (var j = 1; j <= numCols; j++) {
var cell = range.getCell(i, j);
var formula = cell.getFormula();

if (!formula.includes("callChatGPT")) {
Logger.log("Cell (" + i + ", " + j + ") does not have callChatGPT formula");
continue; // Skip to the next cell if it doesn't contain the callChatGPT formula
}

var params = formula.split("callChatGPT(")[1].split(")")[0].split(", ");
var references = params[0].split("&");
var combinedValue = "";
for (var k = 0; k < references.length; k++) {
if (/^[A-Z]+\d+$/i.test(references[k].trim())) { // Check if the string is a cell reference
combinedValue += SpreadsheetApp.getActiveSpreadsheet().getRange(references[k]).getValue();
} else {
combinedValue += references[k];
}
}

var prompt = combinedValue;
var temperature = params[1] ? parseFloat(params[1].trim()) : TEMPERATURE;
var max_tokens = params[2] ? parseInt(params[2].trim(), 10) : MAX_TOKENS;
var model = params[3] ? params[3].trim().replace(/"/g, '') : MODEL;
var outputDirection = params.length > 4 ? params[4].trim().replace(/"/g, '') : "right";

var response = ChatGPT(prompt, temperature, max_tokens, model, outputDirection);

// Determine the output cell based on the outputDirection parameter
var outputCell;
if (outputDirection === "below") {
outputCell = sheet.getRange(cell.getRow() + 1, cell.getColumn());
} else { // Default to "right"
outputCell = sheet.getRange(cell.getRow(), cell.getColumn() + 1);
}

// Output the response to the determined cell
outputCell.setValue(response);
}
}
}

function callChatGPT(promptRef, temperature = TEMPERATURE, max_tokens = MAX_TOKENS, model = MODEL, outputDirection = "right") {
return "Ready for runChatGPT()";
}

function testRunChatGPT() {
const simulatedCell = {
getFormula: function() {
return '=callChatGPT("A1&A2", "0.5", "1500", "gpt-4", "below")';
},
setValue: function(value) {
Logger.log("Output: " + value);
}
};
runChatGPT(simulatedCell);
}

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Run ChatGPT', 'runChatGPT')
.addToUi();
}
3
3. 創造你的 extensionhttps://scribehow.com/shared/Create_and_deploy_a_custom_menu_for_ChatGPT_in_Google_Sheets__6Jfi01-6RMSz-R0Xg4i4Tg
4
4. 第一種用法，直接生成：https://scribehow.com/shared/Creating_a_Custom_Menu_to_Run_ChatGPT__KNCAsUDrSZ6qhmeaGnuYLw
5
5. 第二種用法，要參照生成：https://scribehow.com/shared/How_to_Run_ChatGPT_in_a_Google_Spreadsheet__GIsUMecsQJyyPOZGrBLhjA
6
6. 測試的結果測試 ChatGPT 的試算表
7
7. 課程中的測試案例https://docs.google.com/spreadsheets/d/1UAyMH71EfUascMU25peG9pIiaXZ3Kdp3LOwd23UYZFM/edit#gid=1339219092
8
9
這是可以在 Google Sheet 上使用的 ChatGPT、GPT-4。
目前 API 是使用李慕約的信用卡，API 在 11/17 會失效，需要自行申請。
你可以隨意使用本頁的程式碼，但請複製這一個頁籤的資訊，表明原作者，以及讓其他人參考。

作者不會對本程式碼的負責，請自行評估風險後決定使用。

作者：李慕約 muyueh@muyueh.com 。
GPTs 的第一堂課｜李慕約的 AI 工作應用
https://blindegg.kktix.cc/events/chatgpt4-1
10
11
12
注意：因為在課程開放的兩個小時，API 就被用掉了 600 元，所以目前已經將 API Key 暫定，請記得自己申請一個使用。
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100