Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,788
questions
0
votes
2
answers
27
views
Custom function to move and delete a row isn't doing anything
I have a script bound to my Google spreadsheet with a simple onEdit(e) trigger that isn't working.
The script is supposed to move the edited row to Closed Cases, then delete the original row, if the ...
- 13.2k
0
votes
2
answers
64
views
Two custom fnctions triggered by OnEdit both returning errors
I have two custom functions bound to a Google spreadsheet with an onEdit() simple trigger that are returning errors.
The first function sortStatus() sorts the columns in range A2:R by I2:I (col 9) ...
- 3,377
3
votes
1
answer
2k
views
Reference Cells in Sorted Filter View
I have a Google Sheet that looks like this:
That Google Sheet has some Google Script that looks like Google This:
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
var rng = ss....
CommunityBot
- 1
0
votes
1
answer
2k
views
Auto insert Formula on the next empty row upon form submit
I have a spreadsheet that contains 3 columns with leave request data coming from a google form.
[From Date] [To Date] [Name of the Requestor]
5/16/2018 5/18/2018 John
5/18/2018 5/20/...
CommunityBot
- 1
1
vote
1
answer
242
views
Incorrect range height, was 64 but should be 938 using setValues
I am attempting to loop through different latitudes and longitudes of a location and a destination and an arrival time through Google Directions APIs specified in my spreadsheet.
function ...
CommunityBot
- 1
1
vote
1
answer
916
views
Google script to delete on condition
I'm trying to fix a script for a Google Spreadsheet, https://docs.google.com/spreadsheets/d/1J3VRBXkx5B3p1fmrVpkI3Y6F5EGns9Vao7FyxOQsSVo/edit?usp=sharing
I wrote to following:
function ...
CommunityBot
- 1
1
vote
1
answer
42
views
Format a numbered list in google docs into a table template?
I have a list with a url, an emoji (of a set of 3), and a name in a numbered list.
Taking a list like this:
https://news.ycombinator.com/item?id=38122061 🥾 D. onohoe
https://news.ycombinator.com/...
CommunityBot
- 1
0
votes
1
answer
103
views
Need Script changed to a different page in the same sheet
I have a script on a sheet in my Google Sheets workbook that emails when a cell changes to "TRUE", but the cells in question are a vlookup to another sheet in the workbook.
This works in the ...
CommunityBot
- 1
0
votes
1
answer
61
views
Increment the numeric value of Col D by 1 if Col E is "True"
I have tried to follow the example below to color E to Red if E = True
Figured I would keep changing the script until
What I want is to increment the value in Col D by 1 if the Value of Col E is "...
CommunityBot
- 1
1
vote
1
answer
121
views
Google Sheets - Button link to script with a range input
Is there a way to write a script, assign it to a button, and press the button to execute the script AFTER selection a cell range that serves as an input parameter to the script in question?
CommunityBot
- 1
0
votes
1
answer
34
views
Timestamp on change of another range
I have a Planning sheet that I use to follow budgets that are reported every week.
I'd like to have K7:18 stamped with a date when there is a change in the corresponding cell of I7:I18 range.
I cannot ...
CommunityBot
- 1
0
votes
1
answer
80
views
Sending email form Google Sheet Issue
I took this script from the Google developer site and modified it to suit my needs. The issue i am having is where the "EMAIL-SENT" lands on my sheet.
I need it to be placed on the 4th column right ...
CommunityBot
- 1
0
votes
1
answer
17
views
Determine if onEdit() was triggered by inserting or removing a row
I have two spreadsheets, SS1 and SS2.
I need to update SS2 when a row is inserted into or removed from SS1, or when a certain range of cells is changed in SS1.
I use an installed onChange() trigger to ...
- 13.2k
13
votes
3
answers
81k
views
Basic date manipulation in Google script
I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read ...
- 11
0
votes
1
answer
141
views
How to add date stamp when criteria met in google spreadsheet?
This is the current script. When a cell in column 26 is edited, a datestamp is added to the cell beside it in column 27. Now I would like to add additional criteria. Example: When col 26 value is &...
CommunityBot
- 1
0
votes
1
answer
147
views
Trying to search(from sheet1 ) for a value on in any row of sheet2
I have 2 sheets "Main" and "Data Storage"
Main: should allow me to search "Data Storage" and locate information form any column(let say Column B') and provide me with ...
CommunityBot
- 1
0
votes
1
answer
696
views
Create triggers on recently programmatically copied Google Forms
I have this main Form that uses its responses to create several other Forms after it's been submitted, but I need that these Forms that were recently created to send emails on their submissions ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Pasting timestamp for edits by multiple users in Google Sheets
I got a good Google Sheet script for pasting a time stamp at the designated column of the same row on editing any cells of another particular column. Here it is On Edit of column 11: timestamp is to ...
CommunityBot
- 1
0
votes
1
answer
41
views
Help adding checkbox to google sheets
I am a novice to google app scripts. However I need to copy selected rows (with a checkbox) to a second sheet. I am using this code below. However it copies the entire source sheet. I only need rows ...
CommunityBot
- 1
3
votes
1
answer
2k
views
Simultaneously sort multiple ranges on a single sheet in varying orders
I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
CommunityBot
- 1
0
votes
1
answer
239
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 ...
CommunityBot
- 1
0
votes
2
answers
741
views
How to export CSV without commas in unpopulated cells?
I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website.
However, when I go to export, it seems ...
CommunityBot
- 1
1
vote
1
answer
17
views
Trigger script to share calender with new user whenever form updates sheet with a new email address
I have a Google form that allows people to submit their name and email address in order to sign up for a private shared calendar.
The form populates a Google sheet.
I want to automatically add new ...
- 3,377
0
votes
1
answer
34
views
Pulling all data from a column
tl:dr - I have a code that can work but want to make it better by pulling everything from column E into 1 message (including header)
I am trying to get the output of a singular column to post to ...
CommunityBot
- 1
1
vote
1
answer
90
views
How to auto-update cell border color based on how long ago the cell was last updated in Google Sheets?
It doesn't seem that conditional formatting is able to achieve what I'm trying to do. I'm looking for a way to make a range of cells to each auto-update its own border color based on the duration of ...
CommunityBot
- 1
0
votes
1
answer
38
views
Remove protection from a specific cell when contents are deleted
I am trying to remove the protection from a specific cell after its content is deleted and then change its background to white.
Here is the script:
function onEdit(e) {
var spreadsheet = ...
CommunityBot
- 1
2
votes
1
answer
35
views
How to do autoresizecolumns() in a Google Sheets file when data imported from another Google Sheets file is changed
I'm new to Google Sheets and apps scripts. This is my first ever question. I apologize in advance if I don't get it quite right.
Google Sheets file Staff Roster sheet Roster contains names, contact ...
- 3,377
0
votes
1
answer
697
views
Move rows on 2 sheets older than 24 hours to archive
I have been trying to figure this one out for a while now and would really appreciate some assistance.
I have a Google Sheet that is being updated by a Google Form. Due to this I needed to mirror the ...
CommunityBot
- 1
1
vote
0
answers
20
views
Insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty [closed]
Hi I'm new to the Google Sheets App Script and not sure where to start. I'm trying to write a Script that will insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty.
For ...
- 3,377
0
votes
1
answer
77
views
Script to automatically change the active sheet after user completes populating a range of cells
i am looking for solution to merge text fragements in Apps script to automate a process.
I have 3 sheets (sheet_1, sheet_2,sheet_3). In each of the sheets i have 10 cells (A1:A10) to be filled by the ...
CommunityBot
- 1
0
votes
1
answer
425
views
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....
CommunityBot
- 1
5
votes
3
answers
7k
views
Why would I be getting the error: Too many simultaneous invocations: Spreadsheets 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 ...
- 44.6k
1
vote
2
answers
65
views
Show each n-th column, hide others in Google Sheets
I have several big tables, and I know that each 4th column is interesting for me, i.e B,F,J,N, etc (The total number of columns is different in different documents)
Is there an easy way (some sort of ...
CommunityBot
- 1
0
votes
1
answer
125
views
Why does my script return a TypeError when comparing getA1Notation to a string?
I am trying to write a custom script that is bound to my Google spreadsheet. I want to trigger it using onEdit(e) based on the value changing in the dropdown located in B3 of 'Pax Split Sheet'.
I had ...
- 44.6k
0
votes
1
answer
107
views
I am using Google App Scripts SpreadSheet.openById(), but it assumes Sheet0 as active sheet?
I am trying to create a trigger to get id when there is a change in sheet selection which is working fine when each google sheet has its own google app script, but I want to create a google app script ...
CommunityBot
- 1
0
votes
1
answer
377
views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
CommunityBot
- 1
0
votes
1
answer
25
views
How do I display an alternative charchter in a Doc that is pulling data from a sheet?
I have created an HTML form (not a Google Form) that populates a Google Sheet. The form has both text inpt and checkboxes (multiples of each.) On the Google Sheet, if a checkbox is checked on the ...
CommunityBot
- 1
1
vote
1
answer
35
views
Get exactly data to put on email message?
I got a function to email notice like this code below:
function approvedby() {
var approvedrange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses 1").getRange("E2:E500&...
CommunityBot
- 1
1
vote
1
answer
91
views
SpreadsheetApp.openById gives unauthorised error
I am trying to trigger a function in Google Apps Scripts, and am using openById("Sheet_Id);
But on using it, I am getting this error:
Exception: You do not have permission to call SpreadsheetApp....
CommunityBot
- 1
1
vote
3
answers
365
views
How can I sort multiple ranges by one reference cell that is outside the second range?
I have a roster that has (on sheet 1 ('Roster')): Name, 1st Week of Attendance, 2nd Week of Attendance, and then other non-relevant data.
On the second sheet ('Contact Info' it has Name, a cell that ...
CommunityBot
- 1
-1
votes
2
answers
4k
views
How to get a numerical value of a date with Google Apps Script? [closed]
I have a value 0.000347222222222222 in a cell, which when formatted as time, looks like 30/12/1899 00:00:30 .
How to get it's numerical value with Google Apps Script?
sheet.getRange('A1').getValue() ...
- 3,377
2
votes
1
answer
1k
views
Email notification if cell value goes below set amount
I'm having trouble finding a Google sheet script that does what I need so I figured I would ask if someone could help me. Basically I have a simple sheet setup to track inventory of one item. There is ...
CommunityBot
- 1
2
votes
1
answer
1k
views
How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row?
I'm trying to setup a form and spreadsheet database to track inventory on a daily basis at a remote location. The idea is that the person at the remote location can update our stock via a simple ...
CommunityBot
- 1
0
votes
1
answer
183
views
Clear cells based on checkbox repeated Google Sheets
I have checkboxes in columns A, E, H, L, O, S, V, and Z
I have a script that is working which allows me to check the box in a2 and it deletes data in e2 and f2. I then am able to reverse the script so ...
CommunityBot
- 1
1
vote
1
answer
225
views
Add a new row and move data to that row when box is checked
I currently have a Google spreadsheet that keeps track of all orders and the progress:
Column 1: Order Form Sent
Column 2: Order Form Received
Column 3: Delivered to Pick Up Site
Column 4: Picked Up
...
CommunityBot
- 1
1
vote
1
answer
81
views
Custom conditional in Google Sheets: How to integrate in Apps Script?
My goal is to have Google Sheets detect when a cell's contents has a strikethrough added, and to change the font color of that cell in response. I learned that this is not possible without an Apps ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Put the timestamp of last edit on master sheet, next to edited sheet's name
I'm trying to come up with a script that will print a timestamp of the last edit made to a specific tab within a google sheet. Imagine you have 17 retail stores, each one having its own tab with a set ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Create a custom DateTimePicker in Google Sheets
In a sheet I have a column to enter a datetime value that would later be checked by a script to create a trigger.
I would like to set a picker in that column to ease selection and avoid errors.
I ...
CommunityBot
- 1
0
votes
1
answer
30
views
I can not remove data validation with 2 criterias - Function onEdit(e)
I would like to run function onEdit(e) to remove Data validation with these 2 criterias as below:
Remove for columns G,H,I,K if L is "no need".
Remove for columns M,N,O,Q if R is "no ...
CommunityBot
- 1
1
vote
1
answer
110
views
Automate auto-reply based on specific Google Calendar Events
I'm currently trying to automate an auto-reply with Google Apps Script for whenever I am out of office based on my Google calendar events named 'OOF'. Even if everytime I'm getting closer, there's ...
CommunityBot
- 1