Cofacts prompt engineering (see https://g0v.hackmd.io/@cofacts/rd/%2F%40cofacts%2FrknFrmdk3 for the project)

appsscript.json

{
  "timeZone": "Asia/Taipei",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

main.gs

// (c) 2023 Cofacts 真的假的 - 訊息回報機器人與查證協作社群
// This code is licensed under MIT license.

/**
 * returns the col index for each column.
 */
const COLUMNS = {
  PARAMS: 'B',
  ROLE: 'C',
  CONTENT: 'D',
  PROMPT_TOKENS: 'E',
  RESP: 'F',
};

function run() {
  const sheet = SpreadsheetApp.getActiveSheet();

  for(const {rowIdx, params, messages} of getSelectedConversations()) {
    const resp = getChatCompletions({
      n: 3,
      ...params,
      messages
    });

    const rowEndIdx = rowIdx+messages.length-1;

    const tokenCountRange = sheet.getRange(`${COLUMNS.PROMPT_TOKENS}${rowIdx}:${COLUMNS.PROMPT_TOKENS}${rowEndIdx}`);
    tokenCountRange.mergeVertically();
    tokenCountRange.setValue(resp.usage.prompt_tokens);

    const respRange = sheet.getRange(`${COLUMNS.RESP}${rowIdx}:${COLUMNS.RESP}${rowEndIdx}`);
    resp.choices.forEach(({message: {content}}, i) => {
      const range = respRange.offset(0, i);
      range.mergeVertically();
      range.setValue(content.trim());
    })
  }
}

/**
 * Parse user selected ranges into conversations.
 * Start row of the conversation is defined by the "param" column.
 *
 * @returns {{rowIdx: number; params: object, messages: {role: string, content: string}[]}[]};
 */
function getSelectedConversations() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet.getSelection().getActiveRangeList().getRanges();

  return ranges.reduce((agg, range) => {
    const startRowIdx = range.getRowIndex();
    const rowsInRange = range.getNumRows();
    for(let i = 0; i < rowsInRange; i += 1) {
      const rowIdx = startRowIdx + i;

      // Detect start of conversation by param column
      const paramCell = `${COLUMNS.PARAMS}${rowIdx}`;
      const paramValue = sheet.getRange(paramCell).getValue();
      if(paramValue) {
        let params;
        try{
          params = JSON.parse(paramValue);
        } catch(e) {
          throw new Error(`Malformed JSON settings on cell ${paramCell}: ${e}`)
        }
        agg.push({rowIdx, params, messages: []});
      }

      if(agg.length <= 0) throw new Error('The first row of the selection must have params settings.');

      agg[agg.length - 1].messages.push({
        role: sheet.getRange(`${COLUMNS.ROLE}${rowIdx}`).getValue(),
        content: sheet.getRange(`${COLUMNS.CONTENT}${rowIdx}`).getValue(),
      })
    }
    return agg;
  }, []);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Chat GPT')
    .addItem('Generate response', 'run')
    .addToUi();
}

openai.gs

// (c) 2023 Cofacts 真的假的 - 訊息回報機器人與查證協作社群
// This code is licensed under MIT license.

// Fill in API key here
const OPENAI_API_KEY = 'xx-xxxxxxxxxxxxxxxxxxxx...';

function getChatCompletions(body) {
  const jsondata = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      authorization: `Bearer ${OPENAI_API_KEY}`
    },
    payload: JSON.stringify(body),
  });
                  
  return JSON.parse(jsondata.getContentText());
}