Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,692
questions
0
votes
0
answers
2
views
Apps Script - Set value if condition is met
I'm trying to create a script which will do the following:
Check if the number in col. B for each row is > 4
If it is: send an email & mark the associated cell in col. C as "Yes"
e....
-1
votes
0
answers
8
views
Need help with and Find Script [closed]
The function is not working. Anyone could help me?
https://docs.google.com/spreadsheets/d/1jiwgi2OCtDL_59S2lTY2bt5BuPK0gYmhv5zZCSwWX1c/edit#gid=640530318
function Localizar() { ...
0
votes
0
answers
16
views
Script to send email with value when new value is entered in a column
I am looking to create a script which sends an email when a new value is entered into a specific column. As part of the email, I would information from the row in which the value is entered in. Is ...
0
votes
1
answer
11
views
Why does my onEdit macro error when checking on an edit in a specific cell and sheet?
I've been battling with this for several days and used many previous answers found on these site, as well as a few others, but still can't get my script to run as intended.
I have a dropdown box on ...
0
votes
0
answers
17
views
Auto create new sheet when new data in column?
I'm trying to create a new sheet on google sheets whenever column B gets a new value. This sheet will be named the value that created it. . I also want to have a function in the new sheet that ...
0
votes
0
answers
43
views
Google Sheets: Making a function to find a number and impute it once
I am trying to make it possible to find the highest number under 30, and than place it in another cell. Then that cell will not change when another number becomes higher. For example if the highest ...
1
vote
1
answer
24
views
How to transpose (rotate) entire Google Sheets sheet?
My sheet is 123 rows and 93 columns and is very complicated: there are lots of grouped/hidden rows, grouped/hidden columns, notes in cells, comments in cells, complex formulas (including ones that are ...
0
votes
1
answer
26
views
if and for statements on Google Sheets with multiple tabs
I am trying to figure out how to send an email to a bunch of people on my mailing list, but with it being individualized for each person. I have their names and email addresses on one Google Sheet (...
0
votes
1
answer
24
views
I need to perform the same function for each row in my spreadsheet. I have 30 rows in total
I am attempting to calculate and store the number of days absent for my employees - I can do it for one row (employee) but I have thirty employees:
function updateDaysAbsent() {
var ss = ...
0
votes
1
answer
14
views
How to get a notification when a column in google spreedsheets changes
I need help with a code.
I need to get a notification on my email or Slack when for example, column D changes. But I would love to received what column changed.
Here is what I got right now.
function ...
0
votes
0
answers
25
views
Google Sheets - Autosort Script issues
I am trying to sort data descending based upon column B (2) which has different numbers in each cell. EG: B8 = 99, B14 = 70, etc. I'm intending for it to autosort and do it onEdit. I'm not good at ...
0
votes
1
answer
35
views
Hiding columns based on date
Pretty new to scripts.
I have a sheet with name information in columns A-C and dates from column D on.
I found a script that will hide all columns to the left of the column with the current date.
I ...
0
votes
0
answers
26
views
Create a pop up window script for Google Sheets as a password
I have created a sign in/out sheet for students within my building. Students will type their name in column A, click a checkbox as a timestamp when leaving and entering and a dropdown for where they ...
0
votes
1
answer
37
views
Need a Unique ID to be populated in Google sheets on Google Form submission
Looking for a dynamic unique ID for Google Forms, to populate into Google Sheets Response form. I don't want to use external addons.
Below is a code I found online but can't get it to work - I'm a ...
0
votes
0
answers
42
views
Sheets: Collaborators inserting their names as values into selected cells
Previous post marked closed due to being unfocused, so I pared it down. Not sure if this is now good enough, if not please can someone comment to explain so I can correct it?
I'm trying to find a way ...
0
votes
0
answers
24
views
Google sheets -How to modify a number in a formula based on user input
I was trying to automate a project where I would need to modify a number in one of the formula every time as per the user input.
For example: in the above formula, the highlighted number (9) should ...
0
votes
1
answer
21
views
add timestamp to this code
How do I add a timestamp in the format hh:mm:ss in column B (instead of "null")
const values = [[new Date(), null, text]];
const firstBlankCell = sheet.getRange(sheet.getLastRow() + 1, 1)...
0
votes
0
answers
10
views
Dynamically add answers [duplicate]
I want to ask questions as the following with Google Forms:
What lessons have you already completed?
(here I give a list of lesson options. Only one option can be selected)
Lesson A
Lesson B
Lesson C
...
-1
votes
2
answers
47
views
Google Sheets – Is there a Full Screen Function or Script?
I was wondering if there is a function that makes google sheets go full screen? For example a script that can be added to picture, that when clicked makes google sheets go full screen? Or upon opening ...
0
votes
2
answers
39
views
TypeError: Sheet.getMaxColumns is not a function. What do I need to do>
I had some code created using the classic editor that did the job of inserting 10 lines in two Google Sheets, which grow down over time with record building. The code speeded things up for the users. ...
1
vote
1
answer
86
views
Choose format for new Date() in Google Apps Script
I have tried almost all suggestions on the internet, yet nothing seems to work. Maybe someone can help a newbie out. I am using this at the end of my script:
sheet.appendRow([new Date(), null, text]);
...
3
votes
1
answer
126
views
Formula for matrix (dates in descending order)
As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") ...
3
votes
2
answers
127
views
Formula for matrix (dates in ascending order)
Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts)
Let's say I have a table that is updated daily showing the fruit ...
1
vote
2
answers
32
views
Is setting a trigger for a function to run with a triggered function not allowed?
I would like my function freezeSunday to run at exactly at 1pm on Sunday. Since it does not appear that you can trigger a function to run at a precise time (only at a randomly selected time within a ...
0
votes
0
answers
24
views
Insert Dropdown (Cell Range) from Google Sheets into Google Doc
I'm using Google Docs to collect a lot of information about property. Each property has its own Doc file. In this Doc, I have various Dropdown lists. I'm at the point where I need to update and add ...
2
votes
1
answer
71
views
"Copy to" confusion
In my spread sheet, there are 3 tabs, let's call them A,B and C.
Tab A has user input data
Tab B picks up that data (get.range and set.formula) and time stamps it (daily interval) with rows appended (...
1
vote
1
answer
56
views
AppScript: Compare a cell Value pre & post update to populate another cell
Newbie to scripts.
Link to the sheet with minimal essential information
In the status tab, there would be an inventory of equipment (currently only one (A1). The equipment can be in various states (...
3
votes
1
answer
72
views
Running onEdit script only in certain sheet
I'm having an issue with one of my scripts created from a macro. I want to update a filter onEdit but only when a certain sheet ("Invoice") is open. Here's what I have now that works across ...
2
votes
2
answers
113
views
Scheduling a script to run at a specific time rather than a range of time
I set a time-driven trigger to run my script, but the only choices in the pull-down menu for this are 1 hour ranges, 1-2pm, 2-3pm, etc. I chose 1-2pm, and one day the macro ran at 1:38pm and another ...
0
votes
1
answer
27
views
How do I forward an email, including the original forwarded message and sender email in Gmail
I am trying to figure out how we can forward the original message and include the recipient email, full chain, so for example:
---------- Forwarded message ---------
From: Ram Sam <test@example....
0
votes
1
answer
34
views
I need to update the app script to also capture Gmail attachments' name along with other details
First of all, I am not familiar with coding. I have a code that I use to pull certain information from Gmail based on the labels. Now the problem is, the code does not include a line to have the name ...
-1
votes
1
answer
25
views
Is there a way to use URLFetchApp to behave like importData for CSV rresults
For various reasons (see question Force the refresh of a Google Sheet) I am trying to replace the use of importData in an app script with a custom function that leverages UrlFetchApp.fetch() retrieve ...
0
votes
3
answers
83
views
Force the refresh of a Google Sheet
I know that there have been several attempts to answer this type of question that I've found through google searches, but none have worked for me, so I thought I'd ask again.
I have a Google Sheet ...
0
votes
1
answer
37
views
IMPORTRANGE keeps broken
I have several raw data files which I have linked with importrange to another files.
For some reason my files where getting broken since I keep getting importrange internal error. Also, they take too ...
-1
votes
1
answer
23
views
Assigning custom Key Combos w/ Google Sheets [duplicate]
I am trying to assist in moving over to Google Sheets from Excel. The one concern is that we currently have some users who are affluent in Excel and are concerned that their macros where they assign ...
0
votes
1
answer
17
views
How to make column go reversibly blank with custom validation and conditional format rules
I was able to use scripts editor to make some conditional formatting. Would like the option of covering up the answer column, column B reversibly. Would love some help with a script or how to do this. ...
0
votes
1
answer
189
views
Exception: Service Spreadsheets failed while accessing document with id
I've been using this script for the last two years but it stopped working two months ago with the mention : failed while accessing document with id, is there something that changed with Google?
...
0
votes
2
answers
27
views
How to transpose one tab of data into another tab?
I'm looking for guidance on how to set up my Apps Script file to transpose data from one tab to another. A sample file of my data can be found here.
I currently have 10 columns in the rawData tab: [...
0
votes
0
answers
18
views
Import coefficients and compare
I have a basic file where I have several parameters (on the left). For each parameter, I have one or more A and B coefficients.
In my second file, I have the same thing: parameters (not necessarily ...
0
votes
1
answer
30
views
Evaluate ImportJSON() custom function less often
I am using this importJSON script: https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs
Screenshot of my sheet: https://puu.sh/IWNt6/5d8a15138d.png
I make multiple calls in cells and I ...
0
votes
0
answers
55
views
How to create delay when script is running in Apps Script?
Basically, this script looks for changes in a column, then moves the whole row to target tab on next available row... BUT... when it shifts the row up on the original sheet it thinks that same cell ...
0
votes
0
answers
33
views
Need help with Sheets Pivot Table Coding
I am trying to code a script to generate a pivot table based on input from drop down menus in Sheets, but I am new to Google Apps Script and having issues understanding the syntax. I am trying to ...
0
votes
2
answers
25
views
How can I automate changing the default font in Google Sheets?
It is very tedious to go through all the steps over and over in every Google Sheet to change the default font away from Arial to something that doesn't make my eyes bleed. Is there a way to automate ...
0
votes
1
answer
68
views
Move row to very bottom of sheet (Google Sheets Apps Script)
I have a Google Sheet with a specific number of rows, and I want to use a script/macro to move a certain row to the very bottom.
const DESTINATION_INDEX = 4;
function myFunction() {
const ss = ...
0
votes
0
answers
19
views
I want to autosort when a change is made on a different sheet, not the active sheet
I have the following JavaScript:
function onEdit(event){
var sheet = event.source.getActiveSheet();
var editedCell = sheet.getActiveCell();
var columnToSortBy = 1;
var tableRange = "a2:...
0
votes
0
answers
54
views
Google app script editor so slow. over 1 minute just to print "hello"
Not sure why this is happening or when it started, but on 4/13/2022, I noticed executions from the editor on Google Apps Script is really slow. It takes over 1 minute to do anything.
function ...
0
votes
0
answers
18
views
"OutputSheet": function on edit: lookup value in "LookUpSheet" and change OutputSheet.value
I have a sheet with a column that contains a dropdown.
The options in that dropdown are readible strings (idiot proof)
But for further processing, these strings need to be converted to numbers.
So on ...
-2
votes
1
answer
34
views
Help on making Google Sheets script quicker and more efficient
I have the following script, and I would like to know if there is anyway to make it more efficient? It works great, but takes a little time to do what it needs to do. Mainly is there a way to condense ...
0
votes
1
answer
147
views
getRange() Error Exception: The starting row of the range is too small
I have this little script that's giving me some trouble. I get the following error:
Error Exception: The starting row of the range is too small
appendToRecords @ Code.gs:10
What I'm trying to ...
2
votes
1
answer
51
views
Google Sheets script add formula into cell but cell value doesn't update
I have a script that when I run it, should add a formula info cells, if the cells are empty, the script works without any problems but if I try to run it again to update the values of the formula, the ...