New answers tagged google-apps-script
0
votes
Google Spreadsheet Script Trigger: This app is blocked
In "Advanced options" in the Script editor, link the script to a platform in GCP (i.e. console.cloud.google.com looks like any project, does not have to contain anything). After that, you ...
0
votes
Google Sheets - merge text in Apps script
For moving from sheet to sheet, this should work:
// Set up variables
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName('Sheet1');
var sheet2 = ss.getSheetByName('Sheet2');...
- 134
1
vote
Accepted
Creating a custom menu in google sheets
One way is to list menu items and their function names like this:
function onOpen() {
SpreadsheetApp.getActive()
.addMenu(
'⚙️Team Admin',
[
{
name: 'Add Agents',
...
- 9,467
2
votes
Creating a custom menu in google sheets
managed to sort it by editing as per below:
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('⚙️ Team Admin ')
.addItem('Add team members', '...
0
votes
How to integrate the real OpenAI ChatGPT with Google Sheets?
From ChatGPT FAQ (emphasis mine)
How can I implement this? Is there any implementation guide for this?
ChatGPT is being made available as a research preview so we can learn about its strengths and ...
- 42.7k
0
votes
Accepted
Automatically record daily values from a column in a new row
don't know how to transpose this
You are already recording the progress daily using a script function. To make the function copy data into a blank column instead of overwriting previously recorded ...
- 9,467
0
votes
IMPORTRANGE to refer the function but not the value/result
The easiest solution would be not change the child spreadsheets, but get the count directly in the Master spreadsheet, like this:
=count( iferror( importrange("...put spreadsheet ID here...",...
- 9,467
0
votes
Accepted
Copying Google sheets data to newly inserted rows (which have been inserted by a script)
Use Range.setValues(), like this:
/**
* Inserts rows in the active sheet, copying content from the row above.
* Rows are inserted after every row where column X2:X contains a number.
* The number in ...
- 9,467
0
votes
Apps Script to combine multiple sheets to a Master and have the Master automatically update if any of the child sheets are edited
You should not use importrange() to copy data from one tab to another within the same spreadsheet file. Use an { array expression } instead.
To automate that, so that new sheets get included as they ...
- 9,467
0
votes
Slow Script when duplicating tabs Google Sheets
You can make your script run a bit more efficiently like this:
function duplicateTemplate() {
const ss = SpreadsheetApp.getActive();
const temp = ss.getSheetByName('Template');
const vals = ss....
- 9,467
0
votes
Accepted
Adding multiple new rows in Google Sheets based on a cell value
That will require a script. Try something like this:
/**
* Inserts blank rows in the active sheet.
* Rows are inserted after every row where column X2:X contains a number.
* The number in column X2:X ...
- 9,467
1
vote
Create a countdown timer from 2 different date and time
To count down from the current time to the deadlines in column B1:B, use this formula:
=arrayformula( B1:B6 - now() )
Format the result column as Format > Number > Duration.
Column B
Formula ...
- 9,467
1
vote
Accepted
How to create pop up and add link to a cell
Here is a script that will monitor column W and show a dialog box when a value is selected from a drop-down list there.
/**
* Simple trigger that runs each time the user manually edits the spreadsheet....
- 9,467
0
votes
APP Script list Google Drive files in Sheet
For anyone interested, here's the updated script:
'function ListFldFiless() {
// replace FOLDER-ID with your folder's ID
// replace SHEET-NAME with the name of the sheet that will hold the list
...
- 3
0
votes
Accepted
APP Script list Google Drive files in Sheet
Instead of:
var mypdfs = myPDFfolder.getFilesByType(MimeType.PDF);
...use Folder.getFiles(), like this:
const myFiles = myPDFfolder.getFiles();
- 9,467
1
vote
Is there a way to detect with Google-Apps-Script whether Google Sheets is opened from Android app or from desktop browser?
I use the following to detect whether I am running using a browser on my Windows PC or on my Android mobile. I declare ui as a file level variable and then attempt to access it:
ui=null;
function ...
- 11
0
votes
How to automatically update one google sheet based on the data available in other sheet
Use filter() and match(), like this:
=filter( A4:C8, isna(match(A4:A8, A11:A15, 0)) )
- 9,467
0
votes
How to create pop up and add link to a cell
If scripting isn't required and you just need to validate that the entered data corresponds to a valid link, then why not just use Data Validation with a custom formula as the criteria, and use =ISURL(...
0
votes
Accepted
When my function is called from onEdit it does not perform the same as calling directly
You can check My Executions for failed executions, and view those logs to learn more about why the function fails.
Chances are that the function is failing because it cannot access the UrlFetch class. ...
- 9,467
0
votes
Google Script to only search specific worksheet name not whole spreadsheet
Wrap the desired part with a conditional based on the variable you already created:
if (sheet_name == 'Master Crime Spreadsheet') {
The code you chose
}
- 795
Top 50 recent answers are included