New answers tagged

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');...
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