Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

0
votes
2answers
12 views

How to merge Google documents with PDF files automatically?

I am trying to merge different file sources into one Google doc, in a programmatic way. That means I should use Google scripting. So far I do not have problems inserting text files, images, MS Word or ...
3
votes
3answers
5k views

How can I trace precedents in Google Sheet

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets? I have got the code for dependents from https://webapps.stackexchange.com/a/50149/88163....
0
votes
1answer
15 views

How to apply scripting only to rows with data in them?

I have a Google Sheet that adds multiples rows per day as forms get entered. What's the best, most hands-off way to apply formulae to rows as they get added? I've built a script, but it applies the ...
1
vote
1answer
32 views

Dynamic protection based on cell value

In my Google spreadsheet, I want to protect all cells below a checkbox if it's value is true and unprotect if it's value is false. Every cell in row 3 is a checkbox that this has to work for. Example ...
0
votes
1answer
11 views

Google spreadsheet mail as Excel attachment

I want to mail Google Spreadsheet as excel attachment, and also I want to write 4 to 5 lines in the body of a mail, and also I want to send this mail to many peoples some in "To", and some in "Cc" can ...
0
votes
2answers
696 views

How to make that a custom function update its result immediately

I'm using a custom function to read the sheet name (shown in the tab) and then return the name to the cell in the summary page. The script works, however it doesn't update when I change the name of ...
2
votes
1answer
14 views

Timestamping purchase entries

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I ...
0
votes
1answer
21 views

Running formatting script across multiple Google Sheets tabs

I have an onEdit script I'm using to protect/force formatting on a single tab within a google sheets document - and it works perfectly. The issue I have is that I'd like the same script to work on ...
0
votes
1answer
16 views

Calculations which won't recalculate

I'm trying to make a simple calculator in Google Sheets with checkboxes for certain values. For simplicity I'll refer to these Checkboxes as A and B. What I would like to have is the user selects A, B,...
0
votes
1answer
20 views

Data from HTTP link does not refresh [duplicate]

I have implemented the script below in my google sheets to get the data from the http link in cells of my google sheets. this script works fine but now I want it to be refreshed every 5 minutes. I ...
0
votes
1answer
17 views

Making Google Sheets script work on multiple tabs

Below is my script or code: function myFunction() { } function onEdit() { var s = SpreadsheetApp.getActive().getSheetByName('Sheet1'); s.showRows(1, s.getMaxRows()); s.getRange('H:H') ....
4
votes
2answers
3k views

Add a CheckBox with a script

How can I use a script to add a CheckBox to a Sheet? This is probaly trivial but I could not find a single example online and the CheckBox class isn't even mentioned in the docs. From the GUI, I ...
24
votes
9answers
34k views

How to specify the entire sheet as range in Google Sheets?

The best workaround I have found so far is: worksheet_name!$A$1:$YY but ideally I would like to be able to simply write, e.g.: worksheet_name! So, does anyone know: is there a syntax for ...
1
vote
2answers
2k views

How to make Google Sheet jump to today's column when opened?

I'm trying to adjust this code so that I can jump to today's date which are all across row 2 in my spreadsheet. function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss....
1
vote
1answer
2k views

Blinking cell in Google Sheets depending on cell content

I am trying to get a cell to blink (alternate colours white-red) when it contains the word "Red". I currently have the following code that I have found, but don't know how to modify it to particularly ...
0
votes
1answer
34 views

How can I use a script across multiple pages of a Google Sheet

I'll start by saying I'm very new to this. Here's my problem. I have a GoogleSheet I created to use as a seating chart in my classes. It is designed so that when I click the blue check button the cell ...
0
votes
1answer
44 views

Split multi-line (comma-separated) cell into new rows + duplicate surrounding row entries

Based on: Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) How can I rewrite this for use of a comma as separator and remove blank spaces? Input sheet: ...
-1
votes
2answers
484 views

Copying JS array into Google Spreasheet introduces NOT_FOUND cells

I want to create a historic sheet in Google Spreadsheets, where I take the stock data from one sheet and I fill the historic sheet using a specific date in the script. This is how my sheet looks like:...
-1
votes
0answers
8 views

Help me on converting the VB scrip to Google Script [closed]

Can anyone help me to convert the following VB script to Google script. Sub ShowPass() UserForm1.Show End Sub Sub ResetSIG() Dim pwd As String ' Turn off application events to prevent problems ...
11
votes
4answers
20k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
2
votes
1answer
988 views

Conditional number format (whole number, percentage) based on the value in another cell

I have a custom function provides results based on user input - once of these variables accepts either "Numerical" or "Percentage", which provides the data as standard number, or a percentage of the ...
0
votes
1answer
32 views

Creating a script for Google Sheets to send an email if cells in a row contain a certain value

I have a Google spreadsheet that I created to track daily equipment inspections. The data in the sheet comes from a form that is filled out by whoever inspects the equipment for that day. Here's a ...
0
votes
1answer
68 views

How to extract a link URL from one word within a paragraph element in Google Docs with Google Apps Script

I'm trying to pull the URL of a hyperlinked word from within a paragraph element in a Google Document using Google Apps Script. Below are two GAS functions. The 1st doesn't work. The hyperlink is only ...
0
votes
1answer
72 views

Speed up Google Sheets scripts

I use following scripts to do the following: Insert newline Copy info from line below to newline script takes extremely long, how do I function rijToevoegen() { var spreadsheet = SpreadsheetApp....
0
votes
2answers
19 views

Duplicating sheets with simple date from a named range

When I run the script to duplicate sheets with a date is give the tab a long date name Thu Oct 18 2018 00:00:00 GMT-0400 (EDT). I would like it to read in a 10/18/18 format.
6
votes
3answers
41k views

How to format the date in this Google Docs script

I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding ...
0
votes
1answer
98 views

how to apply script on specific sheets within a spreadsheet?

script (attempt): function moveValuesOnly() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var source = ss.getRange('P1!B10'); source.copyTo(...
0
votes
1answer
38 views

Is it possible to calculate in the background of a data validation cell?

Is it possible to calculate in the background of a data validation cell? I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...
1
vote
1answer
110 views

Linking multiple script created forms to 1 sheet in pre-existing spreadsheet

I work in manufacturing. I have created a Google form for our leadership safety audits which requires unique information (name(s), area being audited, and date) and also includes 25 multiple choice ...
1
vote
1answer
204 views

Script for GoogleSheets that inserts date, time, and user names of everyone entering text in a specified cell?

I wanted to ask for your help in building a script in Google spreadsheet that would allow users to respond to comments left by different users in the same cell (cell where the initial comment was ...
2
votes
1answer
49 views

Make function only work on first sheet, not in whole Google Sheets file

I have the following code which is for tracking the date of any edit made in a particular row. However, this code applies to the whole spreadsheet file, I would like to change this to be for the 1st ...
0
votes
1answer
15 views

How to navigate to a specific sheet in Google Sheets based upon a cell value

What I am hoping to do is take the =Now() date that is located on my default sheet and in the Row 1 Column 1 cell and somehow when the sheet is accessed onOpen() have it automatically change the ...
1
vote
1answer
13 views

Can I record the users within a Google sheet?

I'd like to have an auto-generated list of users with timestamps within a spreadsheet, tabs, or if possible specific cells. Ideally, once a cell has been altered, I'd like another cell to record the ...
0
votes
1answer
22 views

Making select columns UPPERCASE

In relation to the post linked below, is there a similar script out there that will apply this same functionality to only certain columns in a Google Sheet? Convert all text to UPPERCASE in a Google ...
0
votes
0answers
16 views

Classic Google Sites page throws error on the web browser console

I just noticed that it’s not even my code that causes the error — classic Google Sites page on load is throwing that error. I tried in incognito mode and I still get an error on any page load. I am ...
1
vote
2answers
258 views

Add border to whole row if cell in column D is not empty

I was researching how to "Add a border to the whole row if a cell in column D is not empty" - I've found similar script but I failed to edit it to my need! Below is the script I found - I already ...
38
votes
12answers
43k views

How to pass a range into a custom function in Google Spreadsheets?

I want to create a function which takes in a range... something like: function myFunction(range) { var firstColumn = range.getColumn(); // loop over the range } A cell would reference it using: ...
0
votes
1answer
37 views

Calculating Ranks in Google Sheets

I have a spreadsheet that has three tabs: The first tab is raw data imported from a website. it's about 10 columns and up to 300 rows. it has a mix of numbers in decimal form and percent. it all ...
0
votes
1answer
1k views

Show/Hide Rows based on text in a cell

I'm trying to toggle which rows are shown/hidden on multiple sheets in my Google Sheets document. Simply put, a user will provide an input on Sheet1, which will populate a "show" or "hide" value in ...
0
votes
1answer
78 views

Fine tuning a find and replace code

I have this code that works to find the word “Yes” and replace it with a 1. How do I also add in a line for it to find “No” and replace it with a 0? function fandr() { var ss=SpreadsheetApp....
0
votes
1answer
20 views

If I set the school to “School A” then only School A's classes should be visible

I have created a progress tracker for the Academy Trust I work in. The trust at the moment consist of 4 schools. Students move between the schools as the progress and between classes during the year ...
1
vote
1answer
27 views

Is there a way to emulate Vlookup in Google Script?

I did some looking around and I'm struggling quite a bit in finding the answer. I'm working on a Google Script for Google Sheets that works like a Vlookup, where you define a cell for it to look at, ...
2
votes
2answers
457 views

How to execute a trigger only when one of the sheets is changed

When I have multiple sheets in a google spreadsheet, I want to execute a trigger only when One of the Sheets is changed, not the others. OnChange() or OnEdit() is executing the trigger for any data ...
1
vote
1answer
27 views

Cell format protection + forced capitalisation [duplicate]

I've created a Google Sheets document intended for daily use by more than a dozen people. Text inputted in two of the columns is set up, via conditional formatting, to change colour based on the name ...
0
votes
0answers
18 views

Have script code itself dynamically update based on value of cell in sheet?

I'd like to have the script coding itself change as I change the data in a sheet. For example, I have a script set to list the all the files in a particular folder as well as their ids. It spits them ...
0
votes
1answer
38 views

How do you delete certain cells before copying?

I want to copy IDs and dates and have written this script so far. function copy(){ var s1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var s2=SpreadsheetApp....
0
votes
1answer
25 views

Google site, add common Google Apps script project to be called by other projects

I have created a Google site, in manage site there is option to add Google apps script and I can add multiple Google Apps Script projects that can be embedded in Google Site. Please see below ...
0
votes
2answers
31 views

Having Google Sheets not recalculate a formula on page load

I have a Google Sheet with a list of addresses, and a column that geocodes the addresses with mapquest via Google App Script. This works great, except it "recalculates" the addresses on page load. ...
0
votes
1answer
46 views

getEditResponseUrls - hyperlinks from second linked form not inserting

I am new to scripts, but I was able to find a one that uses getEditResponseUrls to insert the edit link from a form into a Google Sheet. I've now been asked to add a second sheet to my spreadsheet ...
4
votes
1answer
231 views

Odd Google Apps Script behavior

I have a Google Apps Script project that I have used for the past few months (successfully) and everything has worked just fine. A few weeks ago, I started getting the "Unable to load files" error ...