Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
0
votes
2answers
798 views

Protect spreadsheet against duplicate data and sheet addition/deletion

There are some people that love to troll and spam my Google spreadsheet with useless info. Google Sheet lacks granular permission restriction. I need to prevent duplicate data from being submitted. I ...
1
vote
1answer
36 views

Can I use Google Sheets to Automatically Update Font Styling, Size, Color, etc.?

I have been creating a contact list in Google Sheets to continually add and update names, emails, organizations, etc. for work. I am often copying and pasting this information from other sources as to ...
0
votes
1answer
294 views

Google Sheets script server error on activeSpreadsheet.getNamedRanges()

I have a script to get a list of Named Ranges on a google spreadsheet. I was working fine until yesterday, where the execution is failing with: We're sorry, a server error occurred. Please wait a ...
0
votes
1answer
29 views

Google Sheet Script for automatically entering date/time stamp when the adjacent column

I've found a script on here for entering a timestamp in a column based on edits to the adjacent column: Date and time stamp automatically added in column if(e.source.getActiveSheet().getName() == &...
1
vote
1answer
50 views

How can I get the information from a column next to the active cell to be included in the email?

I have this script running which works fine. However as you see, it only copies the information of the active cell in Column 10 toString. How can I get the information next to it in Column 11 to be ...
0
votes
1answer
20 views

How to apply formatting on specific Google Sheets and columns?

I have a Google Spreadsheet with many sheets. I want to apply (through code) formatting to specific columns to the sheets I specify. I have tried this but my code applies the formatting to every sheet ...
0
votes
1answer
581 views

How can I disable the authorization needed to open Google Sheets Macros?

Whenever I try to open any macro enabled sheets it requires authorization, even though it's a first party application. Is there any way to stop Sheets from asking it or give permissions to everyone I ...
2
votes
1answer
152 views

Automatically create a calendar document using Google Drive and Google Apps Script

Is it possible to use Google Apps Script to 'automatically' create a calendar document in Google Drive with the following document features. Traditional calendar table Accurate month and year ...
0
votes
1answer
118 views

Put Date in Specific Column

I found the following code in another post here to add a date after an edit but it places it in an offset column, I would like the date in Column 8 if column 3 or 4 has been edited. What do I change? ...
0
votes
2answers
479 views

Using NumberRangeFilterBuilder for a slider controlled variable input?

I've been looking for a slider control in sheets where I can play with a variable input by moving the slider left or right. Within the following example: A1 = Value | B1 = VAR | C1 = A1*B1 I want ...
1
vote
1answer
335 views

Google Forms sending me duplicate email on automatical email

So, I've used this code, just reworked for my needs: Can Google Forms send emails automatically? The problem I have is that the account that sends those emails also receives them. Is there a way to ...
0
votes
1answer
42 views

Help to modify code please

I would like to use this code : function onEdit(e) { if(e.range.rowStart === 1 && e.range.columnStart === 1) { e.range.offset(0,1).clearContent(); } } but only on a tab called '...
0
votes
1answer
699 views

Script to Duplicate Template Tab Multiple Times and Rename Tabs from Cell Range?

Basically, I have a list of 200 people's names in the range 'Names'!A1:A200. I have another tab named Template. I need a script to duplicate Template 200 times and rename each of the tabs to the name ...
0
votes
1answer
762 views

OnEdit won't apply to all tabs in Google Sheets

I am running a script that automatically organizes priority in a to-do list. It works great on the current tab, but when moved to a sheet within the worksheet the same OnEdit function will not run. ...
0
votes
1answer
386 views

Google Apps Script slow applying sheet/range protection

I am building a set of spreadsheets using Google Apps Script. Each spreadsheet is being created with a set of 10 sheets. Now that the all seems working, I have added some code to protect ranges, ...
5
votes
3answers
9k views

Set title of currently selected chart from named cell

There's a sample over here that will show how to set the first's chart title to the active cell: function onOpen(){ SpreadsheetApp.getUi().createMenu('Charts').addItem('Update title of the first ...
0
votes
1answer
27 views

Clear range of cells based on another

I'm having trouble trying to write a script that will clear a range of cells based on another. For example I want to clear out only the cells C:H on each row if cell K on that row contains the word "...
3
votes
3answers
7k 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....
7
votes
1answer
5k views

How to add an automatic “last edited” timestamp and the user who edited entry to Google Docs?

Is there a way to insert an automatic "last edited" timestamp in the footer of a Google Document? I would like to make sure that time stamp is on the document when I print. I figure out how to enter ...
0
votes
1answer
21 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 ...
1
vote
1answer
2k 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 ...
1
vote
1answer
451 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 ...
1
vote
1answer
41 views

Automatically Merge Group of Cells Below a Text Value

I'm building out a scheduling assistant; the look is great, but it is very tedious to Merge & highlight each schedule block individually, so naturally I turned to Apps Script. I'm entering ...
3
votes
1answer
170 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 ...
2
votes
1answer
51 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 =...
0
votes
1answer
17 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 ...
4
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 ...
2
votes
1answer
46 views

Is there a way to copy a selection spanning multiple rows, and paste them as merged cells spanning two rows each, in Google Sheets?

I'm going to be honest; my biggest issue is describing what I wish to accomplish. I can't find the right word for it, so the title might not make a lot of sense. But the pictures should be clear, I ...
0
votes
1answer
352 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 ...
0
votes
1answer
16 views

Get exactly data to put on email message?

I got a function to email notice like this code below: function approvedby() { var approvedrange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses 1").getRange("E2:E500"); var ...
19
votes
2answers
43k views

How to import JSON data into Google Spreadsheets?

There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there any similar solution to import data in JSON ...
0
votes
1answer
92 views

Can automatic send emails when checkbox = True?

I'm looking for an script can automatic send emails when checkbox is True. This is my format example: What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly ...
1
vote
2answers
283 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?
0
votes
1answer
15 views

Partial Font color in a cell

Wanted to see if there's a way to automate changing the color of the font in cell for selective parts. For instance, I have in one cell: If you have any questions, please <a href="{linker('...
0
votes
1answer
36 views

ARRAYFORMULA and FILTER

The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing In the first sheet ("Matrix"), I have ...
0
votes
1answer
20 views

How to use google apps script to validate a cell?

I have been trying to write this code to validate a range of cells. If the cells contain the text "true" then set value as "Incorrect Data" else "DD Data" /** @OnlyCurrentDoc */ function myFunction() ...
0
votes
1answer
430 views

Add dynamic questions in Google forms

I am using google forms to punch orders in my Google Sheets. Although I am facing a challenge as the number of products is variable. For example Question 1: Item Name Question 2: Quantity I ...
0
votes
1answer
119 views

Create a custom DateTimePicker in Google Sheets

In a sheet I have a column to enter a datetime value that would later be checked by a script to create a trigger. I would like to set a picker in that column to ease selection and avoid errors. I ...
0
votes
1answer
25 views

Google sheets, how do I combine data from two cells, where there is a URL (web-link) in the cells, without loosing the link(url)

I have tried to write a custom function in google sheets. I tried to create a function to joint the content of two cells. It works for text, however it does not work if the text contains a web-link (...
0
votes
1answer
959 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 ...
1
vote
2answers
5k views

Automatically “split text to column” using Google App Script

I'm trying to figure out "if" I can automatically "split text to column" using Google App Script and the trigger of "on Form Submit". If yes, can someone help me figure out the solution to following ...
1
vote
0answers
40 views

How to make this Google Sheets script dynamic?

I'm working in a script to paste a table on the same sheet, in my code I'm only pasting the table on the row A18 function NewQuote() { var ss = SpreadsheetApp.getActive(); ss.getRange("A18&...
0
votes
2answers
112 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 ...
0
votes
1answer
434 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 ...
0
votes
1answer
560 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
2answers
137 views

Insert a weekday in Google Forms' date selector

We have created a Google form that works perfectly, there is only one issue, and that's the DATE selector, it sends the DATE, but we would like to receive the DATE and the WEEKDAY in the form we ...
0
votes
2answers
26 views

Move data script

I am setting up a script to copy data from one location and paste values into another location (same tab). However it is only picking up the first line. How can i get it to pick up the whole range ? ...
0
votes
1answer
34 views

Sending an email to the form submitter with a response number using script

I am looking for a script that will email the form submitter a quote number after they have submitted their request. I pass the information submitted in the form to another page where it is matched ...
0
votes
1answer
28 views

Time until Specified Date

Looking to add an auto-updated amount of time countdown timer into Google Slides with the working script from this question. Getting Setup Go to Google Slides Create a text box, right click to find ...
0
votes
2answers
79 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 ...

1
3 4
5
6 7
28