Academy ↓
18Google 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();
}
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.
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',
}}
/>
);
}
Create a custom menu with only 1 function
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
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.
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
}
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.
8 months ago
Better Sheets Submitted
/**
* @customfunction
*/
Make a copy and rename to BACKUP plus the Date of the backup. Execute this automatically via Triggers in your Apps Script
7 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 )
}
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.
7 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)
}
}
This is a version of Timestamp when Done that adds a Sheet to the mix to make sure we're on the right sheet.
7 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)
}
}
For a column 3 or C, we want to timestamp anytime the row changes.
7 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)
}
}
Add title Case or headline capitalization to your google sheets as a native function.
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(' ');
}
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.
7 months ago
Better Sheets Submitted
function onOpen(){
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OPENME").activate()
}
Call OpenAI's API with this simple custom function. Use your own API Key with this seemingly native function.
6 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
}
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
6 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 the code for AI API Calls from Turbo to Omni.
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
}
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.
4 months ago
Better Sheets Submitted
function deleteSecondRow() {
SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName("Sheet1")
.deleteRow(2)
}
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.
4 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)
}
Create a simple html table email and send it automatically when a form is filled out.
4 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
})
}
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.").
about 2 months ago
Better Sheets Submitted
var month = new Date().toLocaleString('default', { month: 'long' })