9Google Apps Script Galleries

Custom Menu - 2 functions

Create a custom menu with 2 functions

Andrew
Andrew

over 1 year ago

Better Sheets Submitted

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addItem('Second item', 'menuItem2')
      .addToUi();
}
Import react from @import

This is a description. It describes the page # Only allow a list of trusted parameters through. # Only allow a list of trusted parameters through. # Only allow a list of trusted parameters through.

Vladut Radulescu
Vladut Radulescu

over 1 year ago

User Submitted

Fix Request

Not Working

import CodeEditor from '@uiw/react-textarea-code-editor';

function App() {
  const [code, setCode] = React.useState(
    `function add(a, b) {\n  return a + b;\n}`
  );
  return (
    <CodeEditor
      value={code}
      language="js"
      placeholder="Please enter JS code."
      onChange={(evn) => setCode(evn.target.value)}
      padding={15}
      style={{
        fontSize: 12,
        backgroundColor: "#f5f5f5",
        fontFamily: 'ui-monospace,SFMono-Regular,SF Mono,Consolas,Liberation Mono,Menlo,monospace',
      }}
    />
  );
}
Custom Menu - 1 function

Create a custom menu with only 1 function

Andrew
Andrew

over 1 year ago

Better Sheets Submitted

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}a
OpenAI API Prompt and Response

Send a text prompt to OpenAI API using GPT 3.5 Turbo. Here's how the code works: Basically the function is named "ai" and then a text variable is used to fill in for the prompt you can enter in the function in sheets. Any text you add in the function in side of your cell will be used as "prompt" inside the function. We'll get the apikey for the API call from the cell A1 in the tab named "apikey" Then we'll add the settings like max_tokens and top_p as data. Then we'll create the payload and the headers for the API call. In this case we're using a POST api call to essentially add the prompt text to OpenAI and we'll end up getting a response that's text. We execute the API call with UrlFetchApp.fetch() along with the options we stated earlier. Then we'll get the response, get the text, and parse it with JSON. Finally we'll return just the message content from the JSON we get back to we can read the text the AI gives us back.

Andrew
Andrew

24 days ago

Better Sheets Submitted

function ai(prompt) {
  var apikey = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("apikey").getRange("A1").getValue();
  var role = ""
  var data = {
  "model": "gpt-3.5-turbo",
  "messages": [{"role": "system", "content": role},
                {"role": "user", "content": prompt }],
  "temperature": 1,
  "max_tokens": 500,
  "top_p": 1,
  "frequency_penalty": 0,
  "presence_penalty": 0
}
  var options = {
    'method': 'POST',
    'contentType': 'application/json',
    'headers': {
      'Authorization' : 'Bearer ' + apikey
    },
    'payload': JSON.stringify(data)
  }
  var answer = UrlFetchApp.fetch("https://api.openai.com/v1/chat/completions",options)
  var response = answer.getContentText()
  var json = JSON.parse(response)
  return json.choices[0].message.content
}
Add Custom Function Ability to any Google Sheets Function

Adds the use of the function inside of your google sheets' cells as part of autocomplete. Add this text just above any function you want to use natively in Google Sheets.

Andrew
Andrew

22 days ago

Better Sheets Submitted

/**
 * @customfunction
 */
Back up Spreadsheet Tab

Make a copy and rename to BACKUP plus the Date of the backup. Execute this automatically via Triggers in your Apps Script

Andrew
Andrew

8 days ago

Better Sheets Submitted

function createBackUpTab() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var weekly = ss.getSheetByName("WEEKLY")
  var date = Utilities.formatDate(new Date(),"GMT","yyyy-MM-dd" )
  weekly.copyTo(ss).setName("BACKUP: " + date )
}
Timestamp When Cell Changed to Done

Automatically create a timestamp when a column of cells is changed to "Done" The Timestamp goes into the next column over from the status. This can be edited to look at any column, for any text, and enter a formatted timestamp into any other column on same row as change occured.

Andrew
Andrew

5 days ago

Better Sheets Submitted

function onEdit(edit) {
var row =  edit.range.getRow()
var column = edit.range.getColumn()
var newValue = edit.value
if(column == 2 &&
    row >=2 &&
    newValue == "Done" ){
  var timestamp = Utilities.formatDate(
    new Date(),
    "GMT-5",
    "yyyy-MM-dd'T'HH:mm:ss")
  SpreadsheetApp
    .getActiveSheet()
    .getRange(row,3,1,1)
    .setValue(timestamp)
}
}
Timestamp When Done on Certain Sheet

This is a version of Timestamp when Done that adds a Sheet to the mix to make sure we're on the right sheet.

Andrew
Andrew

5 days ago

Better Sheets Submitted

function onEdit(edit) {
var row =  edit.range.getRow()
var column = edit.range.getColumn()
var newValue = edit.value
var sheetName = SpreadsheetApp.getActiveSheet().getSheetName()
var columnWeWant = 2
var textWeWant = "Done"
var timestampTimeZone = "GMT-5"
var timestampFormat = "yyyy-MM-dd'T'HH:mm:ss"
var nameofSpreadsheet = "Sheet1"
if(column == columnWeWant  && row >=2 && newValue == textWeWant && sheetName == nameofSpreadsheet ){
  var timestamp = Utilities.formatDate( new Date(),timestampTimeZone, timestampFormat)
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameofSpreadsheet).getRange(row,3,1,1).setValue(timestamp)

}
}
Timestamp Last Change of Row

For a column 3 or C, we want to timestamp anytime the row changes.

Andrew
Andrew

5 days ago

Better Sheets Submitted

function onEdit(edit) {
var row =  edit.range.getRow()
var timestampTimeZone = "GMT-5"
var timestampFormat = "yyyy-MM-dd'T'HH:mm:ss"
if( row >=2 ){
  var timestamp = Utilities.formatDate(
    new Date(),
    timestampTimeZone,
    timestampFormat)
  SpreadsheetApp
    .getActiveSheet()
    .getRange(row,3,1,1)
    .setValue(timestamp)
}
}