Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,193
questions
1
vote
1answer
151 views
Why would I be getting the error: Too many simultaneous invocations on a simple once a day trigger?
I've searched around and still can't find anything directly related to the failure message I'm receiving. The script hasn't changed and it used to run just fine, but now in the past week or so it's ...
0
votes
1answer
79 views
Scitpt to duplicate template sheet into new workbook, then rename
I am looking for a script that will create a menu item that will allow me to copy a template sheet that I have created (multiple times) into a new workbook, and rename the copies at the same time.
...
-1
votes
1answer
52 views
Function OnEdit run slow and timeout error
I have a short timestamp script for my google spreadsheet. But it run quite slow and make a lots
function onEdit(e) {
var TIMERECIEVE = [0,-29];
var DATETRECIEVE = [0,-28];
var DATEDONE = [0,-...
0
votes
0answers
19 views
Looking to open a timesheet in Google sheets and auto seek the current date [duplicate]
My employer has recently transitioned from excel to Google sheets as our timesheet platform, and we're finding that every time the doc is opened or recovered from suspension, it resumes at the top.
...
1
vote
1answer
647 views
Google Sheet Query smarter way to get multiple criteria
I use a spreadsheet where I want a list of items which contains multiple criteria.
(I need all cells in a range which contains the letter 'a - g'
The formula I use now is
=query(Planning!G5:G37; "...
1
vote
1answer
66 views
Auto update of cells with timestamp
I'm new to writing/using scripts. I want to use something to allow for multiple cells to be updated and tracked for the last time they were modified. I've tried various different scripts on here but ...
2
votes
1answer
97 views
Apps Script to search for names and file paths in folders by name
I'm making apps script to find files that don't contain the names "Done" and "InProgress".
The script that I have created is like this:
function testSearch(){
var sheet = SpreadsheetApp....
0
votes
1answer
20 views
Timestamp format with carriage return
I have this time stamp format:
var timestamp_format = "yyyy-MM-dd HH:mm:ss";
Which produced a result like:
5/25/2019 10:52:43
but I would like to have a carriage return between the date and time ...
0
votes
2answers
58 views
Export PDF link sheet ID auto change
I have the link for export as PDF a specific range on a sheet
=HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
1
vote
1answer
109 views
How to get and use a value of a cell in the PDF file name exporting using that script
How can I get and use a value of a cell to the PDF file name, when exporting/saving using that script. I would like to use a string written in a cell, I mean, put that information in a variable and ...
1
vote
1answer
33 views
Convert a text of a specific cell or exclude one cell from all text convertion to UPPERCASE
How to adapt this function to exclude a specific cell or how to apply just to one cell?
function onEdit(e) {
if (typeof e.value != 'object') {
e.range.setValue(e.value.toUpperCase());
}
}
0
votes
1answer
163 views
Failed to send email: no recipient
I have a google apps script developed for when a user submits a form. Its an app script from the spreadsheet attached to form responses.
In the script I send back an acknowledgement email.
Just ...
1
vote
2answers
256 views
How do you get current date to be added to a cell when a check box is checked in another cell
I want to get the current date added into a cell when a tick box is checked in another cell.
I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
1
vote
1answer
40 views
Copy Sheet To New Spreadsheet
I want to copy one sheet in the Spreadsheet file into a new Spreadsheet file.
The sheet that I want to copy contains formulas, I want to just copy the value to a new Spreadsheet file.
I have made the ...
0
votes
1answer
62 views
Sending pdf with Google sheet email
Trying to send a pdf attachment to an email using Google sheets. I have checked and tried to use the replies in previous responses but my code fails after sending the first email.
This is the code I ...
0
votes
1answer
62 views
Check if value exists before copy-paste
Can someone help please as I am new to Google Scripts.
I have this simple code that copies values from one sheet to another
function copyrevision() {
var ss = SpreadsheetApp.getActiveSpreadsheet ()...
1
vote
1answer
61 views
Google Apps Script, MimeType.MICROSOFT_WORD will list only .docx
I am using a Google Apps Script to list Word files in a folder, however this will work only for .docx files, not .doc.
Any idea?
var folderID = "ID#"
var sourceFolder = DriveApp.getFolderById(...
0
votes
1answer
86 views
Get the name and link file based on Folder ID
I created a script to get the name and link file based on Folder ID. The file name and link are displayed on sheet 'Get_Link'.
File spreadsheet
The script that I wrote like this:
function ...
0
votes
1answer
211 views
Running script to replace “null” with nothing in Google Sheets
I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
0
votes
1answer
993 views
How to trigger a script in Google sheets when a value changes
I try to fire a script when a value in a certain cell or range of cells changes.
(it's not changed by the user, so I can't use onEdit)
I've tried to use onChange, but this doesn't fire my script at ...
1
vote
2answers
244 views
Send a google form result to an email
I need help with the google form.
The problem is this: I want to receive an email with its results for every user who completes the form.
For example, if you had a form that required email, name and ...
0
votes
1answer
71 views
How to list total files for every subfolder in Google Drive?
On google drive I have a directory with over 1000 subfolders. I want to list all subfolders with the total amount of files for each subfolder. This is the script I got so far but it doesn't work cause ...
0
votes
2answers
41 views
Google Sheets API call creates scientific numbers that I cannot then run calculations on
I am using API in Google Sheets to call prices of cryptocurrencies vrs Bitcoin
from URL: https://api.coinpaprika.com/v1/tickers?quotes=BTC
In some cases I am getting scientific notation instead of ...
0
votes
0answers
20 views
Two timestamps referencing different columns in one google sheet? [duplicate]
I'd like to have a timestamp occur in column 6 when an edit is made to column 1. But another timestamp to occur in column 6 when an edit is made to column 8. I would be open to having the 2nd ...
0
votes
0answers
12 views
Google Apps script to read through folder of slide presentations, compare to template, and import updated slides into a presentation
I teach high school using Google Classroom. I have students used Google Slides to keep track of their projects. I create one template Slides Presentation and GC automatically creates an identical ...
1
vote
1answer
13 views
Bulk permissioning of Team Drives
I've been looking through the documentation but am coming up empty handed.
How can I assign permissions in bulk across multiple team drives? For instance, a new hire needs to be given Commenter ...
0
votes
1answer
36 views
How to automatically add new row in Google Spreadsheets
I want to automatically add row in Google Spreadsheets when I type information in row 5 by Google script.
]1
0
votes
0answers
42 views
Missing Google Apps Script trigger
Receiving the below email notification from google apps script for a trigger I used recently.
I'm done with the trigger now and want to stop it from firing.
When I open my scripts dashboard to ...
1
vote
1answer
225 views
Google Sheets count columns in a merge
Is there a formula function in Google Sheets to count the number of columns in a merge? I could assign a named range to the merge to make it easier....
For instance: see the highlighted merged cells ...
0
votes
1answer
38 views
Re-using a google form sent data, avoiding entering all data
I am building a Google form to manage summer camp subscriptions. Each request must enclose parents and child data.
The problem is, if a family wants to subscribe more than one child, the process must ...
3
votes
1answer
34 views
Can I limit how frequently an 'on open' script runs?
Basically I have a bunch of functions that run on Open, but if possible I'd like to limit them to only run once per 60 minute period. Just so that if a user accidentally refreshes or closes the sheet, ...
0
votes
0answers
156 views
Is there a way to check a user's permission in Google Sheets?
I am trying to find information on this but haven't really been able to find an answer.
Is there a way in Google Sheets to, through scripting, check a user's permissions for the sheet?
My ...
0
votes
2answers
293 views
Custom SHEETNAME function not working in Arrayformula
I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information ...
1
vote
1answer
58 views
Inserting date automatically when a tick box is ticked
I have a simple sheet that has two columns with tick boxes in them (columns 5 and 8) The goal is when the box is ticked the date and time are inserted in the column next to it. I have got it working ...
0
votes
1answer
25 views
Adding numbers to a cells formula?
Let's say A1= X+Y
Now I'd like to add +Z with a script so I end up with A1= x+y+z. I know how to copy it, or overwrite it, but not sure exactly how I would add to it with a changing formula in A1.
...
0
votes
1answer
278 views
ImportXML not stable in Google Sheets, how can I improve that?
I have ImportXML in my spreadsheet.
I am tracking the Alexa ranking of a list of websites.
Here is the formula:
=HYPERLINK(CONCAT("www.alexa.com/siteinfo/",A4), value(ImportXML(CONCATENATE("http://...
1
vote
1answer
23 views
Getting sheet to not update all at once
I'm making a sheet that imports a large amount of data from a site. I want to do this slowly so as not to clog up the site.
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ...
0
votes
1answer
221 views
How do I make this Google Sheets Script faster
function getCellValue(){
var x=2;
var y=2;
var targetSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var targetSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('...
1
vote
3answers
232 views
How to automatically scroll to bottom of Google doc when it is opened?
I'm looking for a way to automatically scroll to the bottom of a Google doc every time I open it. Something functionally equivalent to this answer would be great.
0
votes
1answer
343 views
Equivalent of IMPORTRANGE with script in Google Sheets [closed]
Since the Importrange() is not working as expected, do you know how I could express e.g the following function with a script?
={IMPORTRANGE("SheetKey","SheetName1!A:J") ;
IMPORTRANGE("SheetKey","...
0
votes
0answers
21 views
Changed default cell in Sheets to today's date - How do I default it so it displays this cell at startup?
I used this script to default the cell to today's date - How to make Google Sheets jump to today's row when opened?
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet ...
0
votes
1answer
25 views
Multi-var definition statement limit in Apps Script?
My personal javascript variable definition style is a single var statement followed by one or more definitions. It's an unimportant choice, but one that some find benefits in.
Carrying this over to ...
-1
votes
2answers
43 views
IF and THEN statements on spreadsheets using color
I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements:
=IF(...
0
votes
1answer
279 views
“missing ; before statement” error
I'm trying to get the number which is basically 116 into a cell on Google sheets with the following code, however I get the missing ; before statement error:
var copper = UrlFetchApp.fetch("https://...
3
votes
3answers
409 views
Is it possible to have Named Constants in Google Sheets
Excel has a notion of defined names which can be used to define constants/literals. Does GSheets allow this?
You can see this in action in the following Excel screenshot:
.
Cell B2 has the formula ...
-1
votes
1answer
11 views
How to replace reference cell and have it affect the main cell if it referencing?
I asked a similar question on the Google Support Forum. I was wondering how I could reference a cell with any sort of value in it to another sheet (inside the same Google Sheets document) and then ...
0
votes
0answers
320 views
Google script to determine if value is date for email sending
Dates in Google script are driving me CRAZY!
I have looked all over the internet for something that will allow me to use dates in my google sheet cells from entries that may be dates or text (For ...
0
votes
0answers
14 views
Copy data generated by QUERY
I want to copy the values generated by a complex scripted query to another sheet, for users to work on without deleting the original query.
The query generates a variable range of data, depending on ...
1
vote
0answers
112 views
How do I circumvent protected sheets to expand or collapse groups and rows in Google Sheets?
A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns ...
-1
votes
1answer
21 views
Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)
I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
