13Google Apps Script Galleries

Create a custom menu with 2 functions

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

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',
      }}
    />
  );
}

function snippet some other tag

Custom Menu - 1 function

Create a custom menu with only 1 function

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

about 1 month 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
}

ai artificial intelligence openai gpt

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

about 1 month 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

22 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

19 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

19 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

19 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)
}
}

Title Case

Add title Case or headline capitalization to your google sheets as a native function.

Andrew

11 days ago

Better Sheets Submitted

/**
 * @customfunction
 */
function titleCase(anyText) {
  var words = anyText.split(' ');
  var capitalizedWords = words.map(function(word) {
    return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
  });
  return capitalizedWords.join(' ');
}

Automatically Open to Tab

in this case the tab is called "OPENME" but you can name the tab you want anything and change the text "OPENME" to the name of the sheet you want to open every time someone, or yourself, opens the spreadsheet file.

Andrew

11 days ago

Better Sheets Submitted

function onOpen(){
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OPENME").activate()

}

OpenAI API Custom Function

Call OpenAI's API with this simple custom function. Use your own API Key with this seemingly native function.

Andrew

2 days ago

Better Sheets Submitted

/**
 * @customfunction
 * @param {string} prompt - the prompt text
 * @param {string} apikey - Your API Key from Open AI
 * @param {string} model - AI Model
 * @param {string} max_tokens - Maximum tokens to spend
 * @param {number} temperature - Between 0 and 1
 * @param {number} top_p - Between 0 and 1
 * @param {number} frequency_penalty - Between 0 and 1
 * @param {number} presence_penalty - Between 0 and 1
 * @param {string} role - [OPTIONAL] System Role
 */
function OPENAI(prompt,apikey,model,temperature,max_tokens,top_p,frequency_penalty,presence_penalty,role="") {
  var data = {
  "model": model,
  "messages": [{"role": "system", "content": role},
                {"role": "user", "content": prompt }],
  "temperature": temperature,
  "max_tokens": max_tokens,
  "top_p": top_p,
  "frequency_penalty": frequency_penalty,
  "presence_penalty": presence_penalty
}
  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
}

ai artificial intelligence openai gpt

Embed Testimonials with SheetTo

Add this code to your Apps Script file 1. Extensions > Apps Script 2. Paste code 3. Deploy > New Deployment 4. Execute as "Me" 5. Who has access "Anyone" 6. Click Deply 7. Get Web App Url Make sure to use a tab called "display" with 5 columns. Timestamp How do you like product? Your Name Your Role Your Company → Watch this Video: https://bettersheets.co/tutorials/how-to-power-testimonials-with-google-forms-and-sheets.how-to-power-testimonials-with-google-forms-and-sheets

Andrew

2 days ago

Better Sheets Submitted

function doGet() {
  var lastRow = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("display").getLastRow()

  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("display").getRange(1,2,lastRow,4).getValues()
  
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);

}