OpenAi & AppSheet #1: Building The Foundation App

Documentation
1

------------------------------------------------------------------------------------------------------------------
HOW TO BUILD A ChatGPT-LIKE SYSTEM IN APPSHEET
------------------------------------------------------------------------------------------------------------------
The following is a very brief overview of how you can utilize Google Drive + Sheets + AppSheet + Apps Script + OpenAi to build a system that will allow you to have a conversation that carries along the context.

  • You can ask a question about a topic, then ask a follow up (without mentioning the original topic) and the answer falls in-line just like you’re hoping it will.
    • Provided you get your prompt engineering correct (^_^)

If you would like to skip the build process, you can easily make a copy of the app here

  • You’ll need to create your own copy of the script, and update the automation in the app so it uses your own script file
    • (perhaps I’ll make a video in the future on this)

Once you’ve made your own script file and switched the automation over, and you put your API key in the Welcome page, you’re set!

  • You can start using the system right then and there.

BUILD YOUR OWN!

  • Here’s a very high-level overview of the steps:
1. Create Data Source
  1. Open Google Drive
  2. Create a new folder
  3. Create a new Google Sheet
2. Create Threads Table
  1. Start Date (dateTime)
  2. Label (text)
  3. ID (text/key)
3. Create Messages Table
  1. Prompt (longtext)
  2. Response (longtext)
  3. Number (number)
  4. Reformatting (longtext)
  5. Total Tokens (number)
  6. ID (text/key)
4. Initialize app, setup columns, customize views

  1. Create an AppSheet app using your Google Sheet as the source

  2. Add the Threads table

    • Go through each column, make sure they are the correct type

  3. Add the Message table

    • Go through and make sure the columns are the correct types
    • Ensure that there is a reference column connecting to the Threads table

  4. Customize the UX to your personal preferences

5. Create ongoing history

  1. Message Reformatting (in message)
    - This takes the Prompt and the Response and puts them in a single place
    - This makes it easy for us to pick up this value, making a list of all of THESE values, which can the ben used to make a history of what’s been said

  2. Thread history (in thread)
    - This column takes the value from the [Message_Reformatting] column for all the [Related Messages] and creates a list of those values - which is the combination of the Prompt & Response in one
    - We then reformat this list into a nicely structured prompt to send to OpenAi
    - This column must be a virtual column, in order for it’s value to be dynamic and update when you add a new [Related Message]

6. Create Script using ChatGPT

  1. You can use the following paragraph, it should produce the exact script needed (if you’re playing along at home with the videos)
    Google Apps Script function to take a 'prompt' and 'apiKey' parameter and fetch OpenAI completions using the API endpoint https://api.openai.com/v1/completions, model text-davinci-003, maximum 500 tokens, returning the completion text (result.choices[0].text) and token usage (data.usage.total_tokens) in an array format (converting the token usage to a string).

  2. Script returns an array with the following items

    1. Your Response
    2. The total tokens (as a string)

  3. FYI: The script must be saved in an actual script file to work

7. Integrate script into an Automation Bot

  1. Create a new bot

    • Trigger = Adds/Updates
    • Condition = IsBlank([Response_Column])

  2. Task 1) Get Response

    • Call your script: passing it your API key and the prompt
    • You’ll want to provide a little instructions around your prompt, telling the Ai what you want it to do
    • Returns: Array(LongText)

  3. Task 2) Record Response

    • Run a data action: set row values
      • Response: Index([Get Response].[Output], 1)
      • Total Tokens: NUMBER(Index([Get Response].[Output], 2))

To help along the way, here’s some videos broken down into 4 main parts

There is a lot of room for improvement, a boat load of additional features and functionality that could be added, but this will get anyone started in a seriously short amount of time. (^_^)

  • Let me know what you’d like to see added
  • I plan on expanding this into an evolution series, making Update 2, 3, 4 etc. adding new features and functionality each time
    • You just have to let me know what you’d like to see!

Cheers, and Happy Apping!

Small moonwalker

7 Likes
2

You can find part 2 here:

1 Like