Stack Exchange Network

Stack Exchange network consists of 175 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
1answer
9 views

Multiplying a cell value on edit

I am trying to make a functionality that allows me to enter a value into a cell and have that value automatically be multiplied by another cell, overwriting the original entry. For example, if Cell ...
5
votes
2answers
6k 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 ...
2
votes
3answers
360 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...
2
votes
1answer
1k views

Prevent “invoked too many times per second” error in Google Apps Script

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom ...
0
votes
1answer
22 views

Why would I be getting the error: Too many simultaneous invocations 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 ...
0
votes
2answers
22 views

Empty cell reference when “Loading…”

I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is a loading time and this ensures that the cells are empty during loading. Because my formula ...
0
votes
1answer
53 views

Move ROW upon selecting a specific option from drop down menu

I am looking to use Google Sheets to keep track of my jobs. I have a spreadsheet set up so the status of a job is indicated by a dropdown menu. When a job is completed I would select "Closed" from the ...
33
votes
3answers
23k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
7
votes
2answers
25k views

How can I apply a script to only one specific sheet within a spreadsheet?

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A: function onEdit(event){ var sheet =...
0
votes
1answer
39 views

Running script to replace “null” with nothing in Google Sheets

I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
0
votes
1answer
1k views

Exporting Google SpreadSheet to PDF (filename referring to a cell D7)

I have developed a script which allows me to export a spreadsheet to a pdf file with a click of a button. I have assigned the script to a macro so that it is triggered by clicking on an image. Script ...
0
votes
1answer
38 views

How to trigger a script in Google sheets when a value changes

I try to fire a script when a value in a certain cell or range of cells changes. (it's not changed by the user, so I can't use onEdit) I've tried to use onChange, but this doesn't fire my script at ...
1
vote
1answer
42 views

Run a Script When Tab Programmatically added to a Google Spreadsheet

I'm trying to pull data from an Adwords report to a Google Sheet through an Adwords script. This will be done on a daily basis. Every time the Adwords script runs, a new tab is created (and an old one ...
0
votes
1answer
15 views

Email non-empty cells in tabular format

I'm trying to trigger an email to send all data on a sheet in columns B:F. I've found this tutorial quite helpful but can't quite get my head round it. /** * Sends emails with data from the current ...
0
votes
1answer
2k views

How do I update one column based on the value of another using a script?

I would like to change the value in the D column based on the value of the C column if the cell in C contains "YES", using a script instead of a formula. Sample spreadsheet
1
vote
1answer
208 views

How to color an adjacent cell based on a 6-digit hex number without adding the “#”

I've looked at a few things (this link, WA's Colorize a cell in Google Spreadsheets based on cell data, and this link), but haven't been able to quite get the behaviour I want in Google Sheets. What I ...
1
vote
2answers
479 views

Is there a way to get MD5 Checksum from the files with a custom formula?

I have a Google Sheet full of important links to images on Google Drive. Is there a way to get MD5 Checksum from the files with a custom formula? Here is my scenario: No. | PHOTO | MD5 ...
0
votes
2answers
583 views

Display only last 4 digits in Google Sheets

I have an eight-digit number (20150613), I just want to display the last 4 digits like 0613. I know I can use a formula like =right(20150613,4), but I don't want to do that; I need the number to stay ...
1
vote
1answer
720 views

Colors in conditional formatting based on cell hex values [duplicate]

I am looking for a solution to change the background and font color of a cell based on hex-values in another cell. I am managing a google spreadsheet for an online basketball league. I have several ...
2
votes
1answer
88 views

How to automatically create a templated Google sheet?

I'm trying to figure out how to automatically create a templated Google sheets worksheet when a new entry is made in a different sheet. So the flow would be: Person's name is added into Google sheet ...
0
votes
2answers
576 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 ...
0
votes
2answers
16 views

Cannot Find Function 'getRange' in Google Script

I'm working on setting up a series of scripts for a google sheet where I want to be able to auto sort based off of when something gets entered into the third column, sorting first by the first column (...
0
votes
1answer
13 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 ...
0
votes
1answer
46 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
26 views

Google Sheets API call creates scientific numbers that I cannot then run calculations on

I am using API in Google Sheets to call prices of cryptocurrencies vrs Bitcoin from URL: https://api.coinpaprika.com/v1/tickers?quotes=BTC In some cases I am getting scientific notation instead of ...
0
votes
0answers
4 views

Apps Script to skip files that have no blank rows

I created a script to delete blank rows from many spreadsheet files. the script I created like this: function delRow(id, name, startRow, blankRow){ SpreadsheetApp.openById(id).getSheetByName(name)....
1
vote
1answer
13 views

ArrayFormula stops working after shuffling rows

I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets. The problem I have is when I need to rearrange rows then what ...
0
votes
1answer
24 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
108 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 ...
3
votes
1answer
881 views

Jump to bottom of spreadsheet, blank (non-formula) row

I had a tall spreadsheet that I need to add data at every time it opens. Answers like Go to Bottom Cell have been helpful, but my quirk is that the first column contains formulas that return blank in ...
2
votes
1answer
37 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
0answers
13 views

How to keep “HH:mm:ss” as “HH:mm:ss” when archiving to new sheet

I am trying to script a function that pastes values into a new google spread sheet, one of the rows is a time in CST and in the archive sheet gets pasted as "12/30/1899" with the regular time. e.g 11:...
0
votes
1answer
473 views

Steam Webapi & Hours played for a specific game - Google Sheets

So I have managed to get a list of owned games and total hours in each game with =importJson. The only issue is I only want data for one game. The json query returns all owned games. The api wiki says ...
0
votes
0answers
15 views

Google Sheets - pull static value from cell that will change later

I have a sheet where I calculate the average of the daily value for the past 30 days with a rolling average. I'd like to be able to pull the daily values and store them in another sheet. I want to ...
0
votes
1answer
14 views

Displays data arrays with appendrow where other columns contain data

I wrote a script to display a list of names and folder IDs. The script that I wrote like this: function listFolders() { var sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange('A2:B').clear();...
2
votes
1answer
1k views

How do I insert an image from Google Drive into email body with script

Could anyone help me? I've created a Google Form that uploads a file (image) to a private drive folder, the form also sends responses to a Google Sheet including the image's URL. The code below gets ...
0
votes
0answers
13 views

Trying to figure out a Google Script

Looking for help on a script. To pull new rows from one sheet to update my report without duplicates or erasing manually made changes to current rows. I was originally using =ARRAY_CONSTRAINT() at the ...
0
votes
1answer
1k views

I can't get the script to show in the menu of a G Suite document

I'm new to scripting and have been trying to get this URL shortener script to show in my spreadsheet. I have clicked 'debug' to authenticate the script, then what? I thought the menu item would be ...
1
vote
0answers
10 views

How can I add in a date macro to a Google doc?

Really grateful for this answer from Thomas Wiersema, but am keen to find out what the issues might be if the Utilities menu isn't showing up after you've followed his instructions via Google scripts. ...
0
votes
1answer
8 views

Script to copy range transposed to another sheet

I need to copy a range from the sheet 'Source' to the sheet 'Database' but transposed. It should be the range B1:T9, not the whole sheet. I need to do it with a script as it is going to be a database ...
-1
votes
1answer
129 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,...
1
vote
1answer
21 views

Copy Contents of Cell When Checkbox is Checked - Google Scripts

Preamble: never used javascript before, but trying to learn through google sheets since I work with tons of data. Hello, I'm attempting to write a google script that will take the contents of a cell ...
0
votes
2answers
17 views

inputBox in onEdit does not work for users

I have a strange problem... Simple function like: function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getActiveSheet() var ui = SpreadsheetApp.getUi() var response = ...
1
vote
1answer
11 views

How to go to the first blank cell directly in a column of Google Sheets?

I use column A mainly. A1 is the name of data type, and A2, A3, A4, ... are data. When I open this file, It shows the screen containing the first cell, A1. If I fill data from A2 to A250, then I ...
1
vote
1answer
27 views

How to do permutation of characters and print it horizontally along that row in Google Sheets?

I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are. Is it possible? Because I searched it all over and only found vertical ...
0
votes
1answer
60 views

Failed to send email: no recipient

I have a google apps script developed for when a user submits a form. Its an app script from the spreadsheet attached to form responses. In the script I send back an acknowledgement email. Just ...
0
votes
1answer
31 views

How to automatically add new row in Google Spreadsheets

I want to automatically add row in Google Spreadsheets when I type information in row 5 by Google script. ]1
1
vote
2answers
1k views

Get a list of Drive files into a spreadsheet

I am playing around with AwesomeTable and like the Post-It template. My use case is to curate a nice interface to a list of frequently used files. The list won't change dramatically over time, but ...
1
vote
1answer
2k views

Does IMPORTRANGE automatically update the destination if the source is changed?

Not sure if this has been answered else ware but, I'm trying to use the importrange function in Sheets to import a list of dates from spreadsheet A to spreadsheet B. That part works fine, however, if ...
0
votes
1answer
489 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....