Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,517
questions
0
votes
1answer
19 views
Google Sheets - Turning text in cell into an image
I was wondering if it's possible to turn text into a picture. For example the text "Arizona Cardinals" is imported automatically into a Google Sheet and that cell recognizes "Arizona ...
0
votes
1answer
8 views
Send email when there is a change in column
I would like an automatic notification to be sent to several people when I entered 'Yes' in column C. I wish this would apply to Sheet1 only, not to the entire file.
I have this code
function ...
0
votes
1answer
10 views
Hi, i need some help.i have 3 checkbox,how could..when i choose checkbox.1, 2&3 cekbox is unactivate, so when i choose the 2cxbx,and so do the others
//Validating Permition
if(shUserForm.getRange("C14").getValue() == "0"){
ui.alert("Blank is Not Available! Choose your Permition");
return false;
} else if(shUserForm....
0
votes
0answers
4 views
How to locate & compare script sources across a bunch of copies of the same G. Sheet ? It's messy! [closed]
I'm getting used to coding in Sheets, using their script editor. I've coded a bunch of macros over time on a few projects, but one sheet in particular I keep multiple backups of (the data gets updated ...
-1
votes
1answer
19 views
How to transpose and split in Google Apps Script?
How do write script for below
google form response as below
time stamp
Document No.
Recipient Name
Document transmittal ref no
7/9/2021 21:36:11
170709, 170738, 171769, 172003, 172025
Susan
20210001
...
0
votes
1answer
14 views
Google script (custom function) doesn't get updated inside a cell
I have this function :
function clastrow(targetsheet) {
var SS= SpreadsheetApp.getActiveSpreadsheet();
var Sheet = SS.getSheetByName(targetsheet);
return(Sheet.getLastRow());
}
function ...
-1
votes
0answers
18 views
My Regexmatch isn't working for one specific row
My formula should be looking through the green table to identify any rows that match the yellow heading of either nurse, addiction professional, etc. Then if it finds a match it should be coming back ...
0
votes
1answer
8 views
What should I do to get a working script to not affect the formatting
I have a script from users Iamblichus and Cooper which creates a list of sheetnames and updates the list as the names are edited or new ones are created. The script does very well copying the data ...
0
votes
1answer
25 views
Paste special values using a script
I am a beginner and need to write a simple script to paste special values in Google Sheets. I tried the below script, but it's not working.
function livedata_Function() {
var spreadsheet = ...
0
votes
1answer
18 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 ...
0
votes
1answer
18 views
Needing to change a column to false when Data Validation is changed
I am using Google sheets and have a cell that has data validation and I am putting check boxes (I4:I) for people when items are received, they need to be changed to "False" when the Data ...
0
votes
1answer
10 views
openById / openByUrl always returns empty object/null [duplicate]
I am unable to access my spreadsheet via openById or openByUrl. Is there a permission setting I am not aware of? There is no verbose output indicating why it is unable to open the spreadsheet.
0
votes
0answers
17 views
Make links work on copied sheet
I have created a plan book template within google sheets and at the top I have a header to link to a certain day. When I make a copy of the sheet for another week the links are still for the last ...
0
votes
1answer
25 views
Looping through multiple tabs in multiple sheets, getting values by column names
I am trying to gather data from multiple tabs of different sheets, but a few of my files have the columns in different orders so some of the data is showing up in the "wrong" column (Column ...
1
vote
1answer
28 views
Looping through multiple tabs in multiple google sheets
I am working to create a "master" google sheet based on a folder of google sheet documents (currently 6 files). Each of these sheets have a slightly different number of tabs (some with ...
0
votes
0answers
5 views
Script to check if text contains an exact word [migrated]
I want to check if my text contains the specific exact word. For example if i want to check if it contains the exact word 'fire' :
fire - TRUE
the fire - TRUE
the fire bird - TRUE
...
0
votes
2answers
41 views
Is ss.getNamedRanges() faster than ss.getRangeByName(“Range1”) for using multiple ranges as 'global' variables?
I am trying to declare some 'global variables' for spreadsheet automation in Google Scripts. I know that Google Scripts runs everything every time something is changed (including the so called 'global ...
0
votes
1answer
19 views
Custom Script Formula Working with Time Inputs Keeps Returning 12/30/1899, How Do I Stop This
I've looked around and have seen a lot of people ask this same question, but all of the answers I get are either confusing or don't seem to fit with my situation. Here's a dummy sheet (first time, ...
0
votes
1answer
44 views
Can a script on Google Docs or Google Sheets really access all my Google Drive files?
I see this security risk listed in my google account security check-up:
"App details
Has access to:
See, edit, create and delete your spreadsheets in Google Drive
See, edit, create and delete ...
0
votes
2answers
41 views
Extract multiple numbers within parentheses in a text string and sum them in Google Sheets
I have order numbers and quantities recorded like this: # 5397 (5) # 5387 (15) which means 5 and 15, in total 20. The expected result is 20.
I already have a custom function implemented in Microsoft ...
0
votes
1answer
22 views
How to get the average percentage of a column with condition from text data
I start with text data from a Google Sheet like this:
A B
1 Subject1 Text1
2 Subject2 Text2
3 Subject1 Text3
4 Subject1 Text3
5 Subject1 Text2
What I want is to get the ...
0
votes
1answer
20 views
Hide/Unhide rows based on values in particular cells
I am trying to Hide/Unhide rows based on values in particular cells, have the following code which works in isolation but not together. Below only makes B121 be active while B55 stops working. If i ...
0
votes
0answers
8 views
Google Sheets to output variable to Discord [migrated]
I am attempting to create a script that outputs a cell in the same row to discord based on a checkbox. I've written up so far the following, although I'm really not very good with javascript/Google ...
0
votes
1answer
11 views
Can I assign two functions to a single menu item in Google sheet app script?
Is it possible to assign two function names into a single custom menu item like this script:
function onOpen() {
var menuItems = [
{name: 'Draw Borders', functionName: 'borders'},
{name: '...
0
votes
1answer
16 views
Script to Copy multiple sheets from an Excel file and convert it to a Google Sheets file
I have an Excel file with 3 tabs (Sheet1, Sheet2, Sheet3) attached to a Gmail message and I would like these to copy to a Google Sheet file with 3 tabs (SheetA, SheetB, SheetC). The problem I am ...
1
vote
2answers
29 views
Delete rows in Google Sheet after 30 days
I have a Google Form that populates a Google Sheet with rider registrations for a local cycling club. The sheet has a filter in place to display only the riders for the current day. This filter ...
1
vote
1answer
14 views
Cell formula which returns the number of sheets
I'm trying to add some "validation" to a google sheet to prevent errors from creeping in as the sheet is edited (think assertion when programming).
Because of the way my sheet is structured ...
0
votes
1answer
20 views
Apply custom formula across all columns using Array Formula
I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
0
votes
1answer
16 views
Problem with query data range if a value is TRUE
https://docs.google.com/spreadsheets/d/1XFbSKOgwFlsRahaOOCq1-EZPR4JcmaT2ywUc2RQffnk/edit?usp=drivesdk
I use this sheet to list data of patients in my hospital. Also in the last tab (Possible_Discharge)...
0
votes
1answer
32 views
I would like to split multiple URLs from a single Google Sheets cell and then add them as images into a Google Doc
I have a Google Sheet doc that has column called Imgs which contains a cell with multiple URLs that links to multiple images:
So, I would like to do the following with an Apps Script:
Split the URLs ...
0
votes
1answer
73 views
Google Sheets - How to get row index for a form submission (new vs update response) [closed]
OBJECTIVE
Create booking system using Google form, sheets and calendar;
There is one calendar for each room; the code and the sample data is based on room#41.
When there is a new submission, calendar ...
0
votes
1answer
30 views
How to add 1 question in Google Forms which takes multiple inputs from the user and stores in a single column in Google sheets
I am struggling to create a custom question in Google forms which can ask the user for Multiple inputs or Short texts.
For example If I ask a question to a shopkeeper: "Name all the vegetables ...
0
votes
1answer
55 views
Multiple timestamps in multiple columns on multiple Google Sheet tabs
I am trying to work out what script is required to have multiple timestamps on several tabs however each one I have tried doesn't work for me. I am new to this.
When text is added to column 2 I need ...
-1
votes
1answer
29 views
Google Sheets Appscript on apply filter event?
I want to execute a script as soon as I apply a filter to my Sheet. I can't find any event in the App script Docs that will allow me to do this, no onFilter event or anything obvious anyway. Does ...
1
vote
1answer
28 views
Insert timestamp in row when formula result changes in the same row in Google Sheets
I am trying to add a timestamp when a particular cell returns TRUE. With help from this community, I was able to modify the code below. It works perfectly if I type in TRUE. However, when a formula's ...
0
votes
2answers
21 views
Add one Column to Another, Then Delete the Column
This is a messy function but it works. I want to input values into column D, use the macro, have it add D values to C Values across from one another, then Delete all D values. I could write out C3:...
1
vote
1answer
32 views
How to stack two columns in Google Sheet with column name in another column?
I have these columns in the Google Sheet.
Date A Date B
1/1/2020 3 5/2/2021 328.28
1/2/2020 41 5/3/2021 322.75
1/9/2020 23 5/25/2021 342
1/12/2020 54 5/28/2021 ...
0
votes
1answer
10 views
Add all tabs to a script with out having to manually add them to the script
I am trying to make this as simple as possible for users in our office that may not know how to edit script.. right now I have the following code (see below) however I have to list out each sheet name ...
0
votes
1answer
23 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
...
0
votes
1answer
22 views
How can I transform a form created in Google Sheets into an App or something that can be used by multiple people that also autoupdates?
So, I've created a form of sorts in Google Sheets. No, I don't mean a Google Form linked to a spreadsheet, I mean a literal form created directly in a Spreadsheet. Picture for reference:
I've already ...
0
votes
1answer
49 views
Why does this Google Apps script for Gmail not do anything?
I am using the following script from Any way to send Gmail auto-response at certain times every week?. It does not send the expected email.
function autoReply() {
var interval = 5; // if the ...
0
votes
1answer
31 views
I need to automatically change data validation rules after adding row for all rows underneath
I am completely new to scripts. I have created a complex template on Google Sheets for accounting. I am using dependent drop-down lists. As it is a template and plan is to add rows for the items in ...
0
votes
1answer
83 views
Google Apps Script - While loop
I would appreciate any inputs on this Google Apps Script code!
I am trying to implement a do-while loop to copy the formula from cell B2 to C2 and follow that sequence from B3 to c3, B4 to c4, and so ...
0
votes
1answer
148 views
Script to search my Google Sheets for a partial text match in a cell and if it contains a match then clear or delete entire cell
I thought this would be more simple and easy to find online, but I'm having a difficult time so I'll try here first. I'm Looking for a script to search my Google Sheets for a partial text match in a ...
0
votes
1answer
35 views
Trigger for onChange on add new row only
I want the trigger which work for 'add new row' only, but not other edit function. Right now the changeType of the onChange are Edit(include add new row), INSERT_ROW (but not add new row), etc., which ...
0
votes
1answer
33 views
How can I identify a Google Sheets drawing in Google Apps Script?
I use Google Sheets to produce simple quizzes for students. Instead of just having a cell display <Good!> when a kid enters a correct answer, I'd like to make a drawing pop up. A sports fan ...
0
votes
1answer
21 views
How can you use a Gmail script to attach more than one image at a time?
I'm trying to set up a Google Sheet with some data to email images. I have the code I found online working below to send a single image attachment but I would like to send more than one image at a ...
0
votes
1answer
10 views
cells auto-populating from code when sheet is not called
I have built a code to reset cells to a certain value when a button is pushed. It works perfectly in the sheet that I want it too, however, it is auto-resetting in two other sheets as well, even ...
0
votes
1answer
47 views
Create events from a spreadsheet and write relevant ids back to the spreadsheet
I have a function to create events for field based workers. Eventually I will add this to an onEdit trigger and give the user (centrally based admin worker) some functionality to edit/delete events ...
0
votes
1answer
18 views
How do I code 3 series dependent drop down on multiple sheets?
I have a code to run a 3 tier dependent drop down list - it currently works for one sheet in my workbook named "BlankNode" but I need it to work for ~20 different sheets. I tried adding a ...
