We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
10
votes
2answers
49k views

Calculating shaded cells

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
0
votes
0answers
8 views

Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets

Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet? The spreadsheet I'm trying to do this on ...
0
votes
0answers
10 views

Change Google Sheet cell background color based on hex value within

So I've been looking online and I am not able to find an answer that is working for me. I have a Google Sheet, and some of the cells within that sheet have hex values in them. What I am trying to do ...
1
vote
2answers
984 views

Move cursor to cell in all sheets

I have the following script: function setActiveCells() { var wb = SpreadsheetApp.getActiveSpreadsheet(); var ws_names = wb.getRangeByName('an_sheets').getValues(); var ws_cells = wb....
0
votes
1answer
12 views

How to Redirect to another Website upon Google Form Submission?

I have a Google Form which is backed by a Google Apps Script with an onFormSubmit function, which fires when the Form is submitted. The next thing I want to do is, after the Form data processing is ...
1
vote
0answers
5 views

Delete Google Group posts by age

I am looking into using a Google group collaborative inbox for unsolicited resumes (in Europe). One important requirement for companies managing resumes, is that they should be deleted after some X ...
0
votes
0answers
9 views

Appscript for UPPER function

I have this script that works perfectly: function firstColumnUPPER() { var activeCell = SpreadsheetApp.getActiveSheet().getActiveCell(); if (activeCell.getColumn() == 2) { if (activeCell....
2
votes
3answers
9k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
0
votes
0answers
11 views

Apply Filter script on multiple sheets

I made a script to filter column D3 on active sheet, I would like to do the same on multiple sheet at the same time from an action button on my index page. E.g. (tab1, tab2, tab5, tab7) What do I ...
0
votes
1answer
27 views

Find and replace rich text within a cell

I would like to know if it is possible in the current sheets api, or the google script for sheets, to find and replace rich text. In my specific example, I have red text that I would like to remove ...
7
votes
1answer
8k views

Export all sheets in a spreadsheet to csv in Google Apps

Is there a way to export all sheets in a google spreadsheet to csv as individual files ?
0
votes
1answer
337 views

Spell check with formula or apps script

I have text in the table. I want to check the text whether there is a typo or not. Usually to check my typo using the default Spelling Tool from Google Sheet. The question is: Can the spelling check ...
1
vote
2answers
1k 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
1answer
20 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 ...
2
votes
1answer
41 views

Reference formulas from one sheet into multiple sheets

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced. For example, if I have =...
4
votes
1answer
7k views

Show a time-stamp in one cell when another cell is updated in Google Sheets

I want to time stamp when a cell was filled out in the column to the right of only certain columns. We use a simple spread sheet to keep inventory and I'd like to know when someone takes something or ...
0
votes
2answers
594 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
15 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
0answers
11 views

Changing permissions on Google Drive file/folder

I was wondering if anyone could help me, I have a script that I would like to create but I am not sure where to start. I would like a script that takes selected files/folders in Google Drive and sets ...
3
votes
1answer
1k 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 ...
0
votes
0answers
16 views

Is there any way to set different formulas for multiple ranges all at once?

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
1
vote
1answer
324 views

How do I make this Google Sheets Script faster

function getCellValue(){ var x=2; var y=2; var targetSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); var targetSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('...
1
vote
1answer
34 views

Automating Find & Replace to change font colour

I write about the card game bridge, so I need to automate this formatting all the time: Find all "♣" and color the text green (#00b700). Find all "♦" and color the text orange (#ff8100). Find all "♥" ...
0
votes
1answer
96 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
vote
2answers
171 views

What function can I use to conditionally format text depending on cursor position or active cell?

In Google Sheets, I have a 'Freeze' on Column A. I want the text in Column A, Row X to Conditionally Format as BOLD RED, while my cursor is on any cell for Row X. Is there a way to manage that?
1
vote
1answer
153 views

How do I circumvent protected sheets to expand or collapse groups and rows in Google Sheets?

A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns ...
-2
votes
1answer
56 views

How can I speed up this script in google sheets?

I am wondering if there is a more efficient way to speeding up this script? var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trends"); var wsOptions = SpreadsheetApp....
0
votes
0answers
13 views

Added importJSON function and importHTML stopped working

I built a great Google Sheets spreadsheet which has been working for a year with no hickups. It pulls data from websites using importHTML. I then tried to add the importJSON function to the ...
3
votes
1answer
788 views

Can a Google Apps Script get events from a .ics file

I know how to add external calendars (in .ics format) to Google Calendar manually. Is there any way to do it from within a Google Apps Script? That is, is there a command that imports from a provided ....
1
vote
1answer
2k views

How to duplicate a Google Sheet template sheet dynamically, preserving the protected cells permissions

I have two Google Sheet script functions that both work independently: 1) duplicates a template sheet, populating each duplicate sheet from a separate row of data in a source range; 2) makes a single ...
0
votes
0answers
10 views

How to post an aspx web forms and get result

There is a asp.net web page, aspx, Search By Address, which works fine in the browser, and I can inspect the request in Chrome debugger, but instead of doing this search manually, I'd like to do it ...
3
votes
2answers
24k views

Clear range of cells

I'm trying to make a script for Google Spreadsheets (which is my first time), by clearing a range of some cells. So: for example I want to clear D5:D12 but not D13 and D14, but then again I want to ...
0
votes
1answer
259 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 ...
2
votes
2answers
35 views

Is there any way to set different values for multiple ranges all at once?

Is there any way via script to put "Apple" in cell A1 and "Banana" in cell B4 simultaneously? The motivation to do so would be that the sheet would only recalculate one time. Currently it re-...
0
votes
2answers
25 views

optimize onEdit managing set of many checkboxes

I have onEdit script that hides or shows rows by checking/unchecking checkboxes (yes/no values). Looks like this if(orpscscchbx.getValue() == 'no') { var rowsHided = 0; for (var i = 0; i ...
1
vote
1answer
13 views

I have the formula I want to use but now sure how to change it with time another time zone

I have a formula setup that I want to use. But I have a separate sheet that I want to use in a different time zone. The one I have written is in EST. but I want another sheet to use the same formula ...
0
votes
1answer
239 views

Upload Limit of Drive.File.Insert

While I use the following app script code to upload file from URL to GDrive it is executed flawless but when I change the source URL with a large file's URL (more than 1GB) it uploads only 50MB for ...
-1
votes
0answers
18 views

Display only last 4 character in Google Sheets for privacy

Display only last 4 character in Google Sheets Current situation: I have a scanner which will scan customer id. The id number will be in google sheet. However, on scanning I do not want google sheet ...
0
votes
1answer
114 views

Check if value exists before copy-paste

Can someone help please as I am new to Google Scripts. I have this simple code that copies values from one sheet to another function copyrevision() { var ss = SpreadsheetApp.getActiveSpreadsheet ()...
1
vote
0answers
30 views

Custom function on Google Sheets that can extract hyperlinks from formulated cells

I recently found this post, which is so close to my solution, but not quite there! Extract the link text and URL from a hyperlinked cell The issue with this custom formula (=LinkURL()) is that it ...
0
votes
0answers
25 views

Google Sheets automated proper case?

I'm playing the game World of Warcraft and my guild has an attendance signup sheet for raids we do every week together. Everyone visits the sheet and types in their class|name|talents so that the rest ...
0
votes
2answers
452 views

How to automatically forward Gmail messages when a label is applied?

I have found this question that is answered: How can I automatically forward Gmail emails when a label is applied? However I'm using that exact script and have replaced the label and recipient with ...
0
votes
1answer
130 views

Why don't work function onEdit(e) when using Zapier

I created a chatbot that using Zapier (through Webhook and JSON) for sending to my Google Sheet a name and a phone of user and Apps Script must generate a coupon code automatically. I used function ...
2
votes
0answers
29 views

*UPDATE* Hide/unhide certain columns on some conditions

UPDATE: I FIGURED IT OUT!!!! YEY!!!! Im trying to have certain columns in my Google Sheet hide or unhide, depending on some conditions. I realized that this is difficult. But I've managed (I think) ...
0
votes
2answers
39 views

Is there a way to protect/copy only some formatting in Google Sheets?

I have a spreadsheet that I use to track customer orders. It has the customer details on a row, their order number and a line of check boxes for the items that they want for that particular order. For ...
0
votes
1answer
3k views

How to move a row into another sheet?

In Google Sheets, I need to hide rows if column H says, "Completed" My preference would be to cut and copy the row to a new sheet if column F reads "Completed", but sounds more difficult. If you ...
1
vote
3answers
3k views

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

When I have multiple sheets in a 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 change. ...
17
votes
2answers
23k views

In Google Spreadsheet, finding what formulas reference a given value

I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice Tools>Detective>Trace dependents and Edit>Find &...
0
votes
0answers
16 views

Google Sheets script that allows hiding and filtering of rows in a protected range?

Like many others I am trying to allow the average user to hide rows in a spreadsheet that has an entire protected column. Currently, they are denited because hiding is considered editing. From this ...
0
votes
1answer
38 views

Transfer a row to another sheet then clear the original row

I saw this problem and the answer was perfect except that I do not want to delete the row. I only need the cells cleared of existing values. How to move a row into another sheet?