Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
0
votes
1answer
18 views

Merged cells not preserved when copying and pasting

I wrote a script to copy and paste a block of cells from one sheet to another in Google Sheets, and everything is working fine except the merged cells are no longer merged after the cells are pasted. ...
1
vote
1answer
150 views

How does one automatically update rows/data with a Slicer filter when a spreadsheet is edited?

How does one automatically update a Slicer when data is changed on a spreadsheet? I would like it so that when I change cells and data on the spreadsheet, the Slicer filter will automatically update (...
0
votes
1answer
23 views

Is there a way to embed a specific cell from a Google Sheets to Google Docs?

I want to fill in a sentence with blank spaces on Google Docs from information that I can provide from Google Sheets. Is this possible? "For my student number X = [student_number], the required ...
0
votes
1answer
27 views

Notification email with data from different columns when edit is made to cell in the same row

I've browsed similar questions but I'm a bit flabbergasted. Google Sheet name: Book List Records Tab Name: Books sent to Wisconsin When any cell in column E is (manually) changed in the tab mentioned ...
0
votes
1answer
34 views

Copy Row Script & Paste at last Row in the Same Sheet

Do you guys have idea in script how can I copy the details on quotation GE-2108-0271-Rev.0 in few pages and paste the row at the bottom in the same sheet? Page 1: to be exact I need the number ...
0
votes
0answers
25 views

Clear Some Cell Values Based on Another Cells Value

Just looking for some help with the below script, as I need the IF statement to be between "29" and "50" and not just 29 or more, as this impacting other cells that it shouldn't. ...
0
votes
0answers
25 views

Small differences between data visible in Google Analytics panel and provided by Google Analytics API

I'm using a Google Analytics API to transfer data from Analytics to a Google Sheet. However, for data within the scope of the same filters and data range, I can see small discrepancies (~10%) between ...
2
votes
0answers
95 views

Get the title and other metadata of a PDF using Google Apps Script

I have a lot of research papers in my google drive. They are all in PDF format. To better manage this large collection of papers (>1k), I want to make a simple table (a Google sheet), in which I ...
0
votes
1answer
25 views

Pop Up in Google Sheets for Viewers and Editors

Is there a way to create a pop-up messsage that shows in Google Sheets not only to Editors/Collaborators but also to those with "view only" privileges?
0
votes
1answer
735 views

Date comparison is not working as expected

I'm working on a small project, using Google Sheets and Google Script to generate a tennis scheduling system. Basically the user enters their info on a Google Form, which is passed to Google Sheets ...
0
votes
1answer
32 views

Daily record log to different spreadsheet

I would like to be able to log the last value with yesterdays timestamp to a completely different Google sheet, but am a bit stomped by the complexity of this. I have a sheet that logs "...
0
votes
1answer
43 views

running script over multiple sheets

My programming knowledge is weak at best. I would like to be able to run the below script over a few of my sheets in google-sheets. The sheets names would be "Liquor Room", "Mezz" &...
1
vote
2answers
119 views

Help with dynamic dependent dropdown

How do I change this dynamic dependent dropdown list script so it works with my particular Google Sheet. function onEdit(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var ...
-1
votes
1answer
17 views

Sending email with check box formula gives me range error using script

Here is the script for this it keeps saying range undefined and I cant figure it out function onEdit(e) { // this script is installed as an installable onEdit trigger /* if the checkbox is ...
0
votes
1answer
222 views

Problem with invalid regular expression missing / (slash)

I have a spreadsheet. I want to send emails from the Apps Script. The logic to send email as follows. Only send 1 email to each unique value in Column A (Co Name) the content of the email will include ...
0
votes
1answer
43 views

RSS News Feed collection into Google Sheet Problem

So I was given a good direction from a previous post, however there are a few flaws that somehow made it stop working. Here is the original goal: Have a google sheet that refreshes the rss link and ...
0
votes
1answer
441 views

Google sheet RSS feed automatically Update

So my goal is to have a google sheet that collects news from different RSS feeds and updates as new article happen in the feed. So far I have found two "methods", one is inserting "?=...
0
votes
1answer
25 views

Counting sheets viewer

How do I use Google Apps Script to find out how many "current viewer" and their status, such as 'idle' or not. I've already search thru all the question and answer, but only found how to ...
0
votes
1answer
94 views

Track highest and lowest number reached in a specific cell over a period of time in google sheets

I have a cell that references the total value of a stock portfolio. I want to figure out how I can let Google Sheets (over a period of time, say a month) automatically tell me the highest number and ...
0
votes
1answer
17 views

After deleting cell contents, cell is no longer "blank" when comparing in a conditional using == ""

In my else statement below, if a cell in column G is blank I want to clear the contents (the timestamp) of another cell in column F using the event function onEdit(). However, after testing the code ...
0
votes
1answer
128 views

How can I use the onEdit() trigger to timestamp a cell if another cell in the same row is checked "TRUE" in apps script?

I am able to add a timestamp in column 5 using a conditional when I'm comparing the column to a number, but what I am actually trying to do is add a stamp to the corresponding cell in column 5 if a ...
0
votes
0answers
20 views

Duplicate and rename a sheet (only it`s data) at fixed hr and day [duplicate]

every monday I've to make a copy of a sheet which is called week_nn, where nn is a progressive number of the current week for that sheet from its beginning. At some point of the day, we have to fix ...
0
votes
1answer
32 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
18 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
18 views

Unlinking checkboxes

I have 3 checkboxes. When I check one, others get checked too. How to unlink them? //Validating Permition if(shUserForm.getRange("C14").getValue() == "0"){ ui.alert("Blank ...
-1
votes
1answer
185 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
41 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 ...
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
72 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
40 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
94 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
21 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
42 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
163 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
2answers
131 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
78 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
684 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
162 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
28 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 ...
1
vote
1answer
261 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
1answer
82 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
53 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
202 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
16 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
117 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
239 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
155 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 ...
-1
votes
1answer
364 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 ...

1 2
3
4 5
33