20Google Apps Scripts

Create a custom menu with 2 functions

almost 2 years 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

almost 2 years 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

almost 2 years 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

8 months 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

8 months 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

8 months 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

8 months 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

8 months 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

8 months 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

7 months 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

7 months 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

7 months 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

7 months 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);

}

Updated Open AI Model to Omni

Updated the code for AI API Calls from Turbo to Omni.

Andrew

6 months ago

Better Sheets Submitted

function ai(prompt) {
  var apikey = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("apikey").getRange("A1").getValue();
  var role = ""
  var data = {
  "model": "gpt-4o",
  "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
}

Remove Second Row

A Google Apps Script function that deletes the second row of a specific sheet named "Sheet1" in a Google Spreadsheet. Use Cases: Removing Placeholder Data: If the second row contains placeholder or example data that is no longer needed once the spreadsheet is populated with real data, this function can be used to remove it. Deleting Headers: If a new set of headers is to be inserted and the old headers in the second row need to be removed, this function can delete the old header row. Clearing Outdated Information: In scenarios where the second row contains outdated information that is no longer relevant, such as old dates or obsolete entries, this function can be used to clear it. Maintaining Formatting: If the second row is used temporarily to apply or test formatting styles, this function can remove it once the formatting tasks are complete. Workflow Automation: As part of an automated workflow, this function might be used to clean up a sheet at a specific step, ensuring that any temporary or intermediary data in the second row is removed. User Input Cleanup: In cases where users enter data into the second row for initial processing or validation, this function can delete that row after the data has been processed and moved elsewhere. Resetting Templates: If the spreadsheet serves as a template that gets reused, this function can delete any existing data in the second row to reset the template for new input. Error Correction: If there is an error or a mistake in the second row, such as an incorrect entry or a duplicated row, this function can remove the erroneous row to correct the data.

Andrew

5 months ago

Better Sheets Submitted

function deleteSecondRow() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sheet1")
    .deleteRow(2)
}

Get Sheet ID and Tab GID Into Your Google Sheet

The function retrieves and writes the active spreadsheet ID and the specific sheet ID of "Sheet1" into cells A1 and A2 of "Sheet1", respectively. This can be useful for referencing or logging purposes. Retrieve the Spreadsheet ID: The script retrieves the ID of the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet().getId(). This ID uniquely identifies the spreadsheet. Write the Spreadsheet ID to Cell A1: The script accesses the sheet named "Sheet1" and sets the value of cell A1 to the retrieved spreadsheet ID. Retrieve the Sheet ID of "Sheet1": The script retrieves the ID of the sheet named "Sheet1" using SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getSheetId(). This ID uniquely identifies the sheet within the spreadsheet. Write the Sheet ID to Cell A2: The script sets the value of cell A2 in "Sheet1" to the retrieved sheet ID.

Andrew

5 months ago

Better Sheets Submitted

function getSheetIDofSheet1() {
  
  var id = SpreadsheetApp.getActiveSpreadsheet().getId()
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").setValue(id)
  var gid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getSheetId()
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A2").setValue(gid)
}

function getSheetIDofSheet2() {
  
  var id = SpreadsheetApp.getActiveSpreadsheet().getId()
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("A1").setValue(id)
  var gid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getSheetId()
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("A2").setValue(gid)
}

Email form entries when form filled out

Create a simple html table email and send it automatically when a form is filled out.

Andrew

5 months ago

Better Sheets Submitted

function sendEmailsUponFormSubmit(e){
  // Get Form Values
    var formValues = e.namedValues
    var html = '<table>';
    for (Key in formValues) {
    var key = Key;
    var data = formValues[Key];
    html += '<tr><td>' + key + "</td><td> " + data + '</td>';
      };
    html += '</table>';
    var sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl()
    html += "<p> Sheet: " + sheetUrl + "</p>"
    var newDate = Utilities.formatDate(new Date(), "GMT-5", "MM/dd/yyyy-HH:mm:ss")
    var emailSubject = "Form Submitted: "+ newDate
    var sheetOwner = SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail()

    Logger.log(html)
    // Send Emails
      MailApp.sendEmail({
        to:  sheetOwner,
        subject: emailSubject,
        htmlBody: html
      })
}

Get month as full month name

This apps script gives you just the full name of the current month, without the day, year, or time. For example instead of Jan. it will be January. The code does the following: new Date(): This creates a new Date object representing the current date and time. .toLocaleString('default', {...}): The toLocaleString method formats the date based on the provided options and the user's local time settings. The 'default' here refers to the system's locale (language and region settings). You can replace it with a specific locale if needed (e.g., 'en-US' for US English or 'fr-FR' for French). { month: 'long' }: This option tells toLocaleString to return only the month in its "long" form. The "long" form means that the full name of the month will be used (e.g., "January" instead of "Jan.").

Andrew

3 months ago

Better Sheets Submitted

var month = new Date().toLocaleString('default', { month: 'long' })

Create Tab For Every Day of the Year

Creates a new tab for every day of the year.

Andrew

9 days ago

Better Sheets Submitted

function myFunction() {
 var date = new Date(2025,0,1)

while (date.getFullYear() === 2025){
  const sheetName = Utilities.formatDate(date,"GMT+6",'MMM d')
  SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName)
  date.setDate(date.getDate()+1)
}
}

Duplicate Template

Create a copy of the template. You can trigger this every day or every month, or every week.

Andrew

9 days ago

Better Sheets Submitted

function createNewTemplate(){

  var today = new Date()
  var sheetName = Utilities.formatDate(today,"GMT+6",'MMM d')
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  ss.getSheetByName("template").copyTo(ss).setName(sheetName)

}