Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

449 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
2answers
431 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 ...
5
votes
2answers
4k views

Copy range from specific values to top of another sheet

I want to copy everything from the range "Start" to "End" to the top row of another sheet (Sheet2). Sheet1: The data between "Start" to "End" can be any number of rows. This is my current code. It ...
4
votes
2answers
2k 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 ...
4
votes
1answer
1k views

How can I create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet?

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros. What is: 1) I have a Gantt spreadsheet which lists all the ongoing and foreseen project as follows (by ...
3
votes
1answer
133 views

How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?

My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx). Currently, I want to automatically convert all Microsoft ...
3
votes
1answer
2k views

Reference Cells in Sorted Filter View

I have a Google Sheet that looks like this: That Google Sheet has some Google Script that looks like Google This: function myFunction() { var ss = SpreadsheetApp.getActiveSheet(); var rng = ss....
3
votes
1answer
1k views

Simultaneously sort multiple ranges on a single sheet in varying orders

I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
3
votes
2answers
12k views

Keep multiple sheets synchronised (both ways)

I need to have multiple Google Sheets be synced both ways. For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only ...
3
votes
2answers
1k views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
2
votes
0answers
25 views

Content control or invisible placeholders

Related to this question dated 5 years ago and where it is stated that "Google Docs doesn't have invisible placeholders at this time, but it has bookmarks." MS Word has 'content controls' ...
2
votes
0answers
177 views

How to Group Set of Rows & Skip One Every Time

I'm currently working in a Google Sheet that has about 12,000 rows. Because of the layout, I want to group every 5 rows, then skip one, then group the next 5. Right now, I have as script that is ...
2
votes
0answers
678 views

Auto sorting by multiple columns in Google Sheets

I have a data-set which I would like to have sorted with first column 6 and then column 7. From this post: How can I make some data on a Google Sheets auto-sorting? I have gotten ideas about making a ...
2
votes
0answers
842 views

How to check if there is any Data Validation error in the whole spreadsheet?

Is there a way to check in a Google Sheets spreadsheet, if there is at least one cell violating a Data Validation rule? If such functionality is not built-into the app, how can I write a Google Apps ...
2
votes
0answers
465 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) ...
2
votes
1answer
986 views

Importing data into Google Form response sheet with date, list, grid

I have a normal Google Form that is filling answers into a response spreadsheet but I want to import pre-existing data stored on another Google Sheet (100 entries) that match the Google Form responses ...
2
votes
0answers
73 views

IMPORTRANGE, where range shifts often, to different spreadsheet

What I have: a client information spreadsheet that contains company information including their last order date following the status of their account (active/follow-up). What I need: the last order ...
2
votes
1answer
58 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 =...
2
votes
1answer
21 views

Touchscreen tap edits drawing object, doesn't run assigned macro

In Google Sheets, I created a drawing object and assigned it a macro. When I use my laptop in "normal" mode using the touchpad (or at my desktop using my mouse) I can execute the macro by clicking on ...
2
votes
0answers
357 views

Numbered list with Cyrillic letters

In Google Docs, I would like to have a numbered list using Cyrillic letters, such as: а б в г д The regular choices for numbered lists offer the options of Arabic numerals, Latin lowercase,...
2
votes
2answers
4k views

How do I get Google forms edit url to work

I want to generate an edit url link in the google sheet for a google form which is linked to it. There are already instructions on how to do it. However they do not work for me. I have followed the ...
2
votes
0answers
2k views

Change tab color based on cell value that is a formula OR cell background color

1st time question asker, so I'm apologizing in advance if my question is worded wrong or doesn't make sense or is, frankly, to wordy (I tend to over explain). I have a sheet with, currently, 10 tabs. ...
2
votes
0answers
480 views

How to pass a parameter allow= “geolocation” when embedding Google Apps Script in a Google site?

The target is to embed a Google Apps Script gadget into my Google site. The function of this script is to show a google map, determin the location of the client and center the map to the found ...
2
votes
1answer
738 views

Add a new row to a spreadsheet, copying down formulas when a certain cell reaches a particular value

I've got a simple Google Sheet (it's literally one line), that has in A2 =now() to calculate the current time, and then a series of formulas calculating various dates/times based on the current date ...
2
votes
4answers
6k 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. ...
2
votes
1answer
109 views

Script to place duplicate Google sheet beside the sheet it copies (instead of the end)

Is there any way to modify my script to have the sheet it's copying insert the new sheet immediately to the right of the one it's copying? I have tons of tabs in the file, and it's starting to get ...
2
votes
1answer
822 views

How to send Google Form results to a particular sheet ("tab") of a Spreadsheet

Is there a way to create a Form that submits to a particular TAB of a Google Sheet? Use Case: We are using this for meeting pre-check-in. There's standard form. I'd like the results for each ...
2
votes
1answer
2k views

Change function from running only on Active Sheet to run on All Sheets (tabs)

I have a script (from here) that changes tab name based on cell value. This works great on the active sheet but I would like it to check and change all tabs based on cell B2 of each tab. function ...
2
votes
1answer
1k views

Put the timestamp of last edit on master sheet, next to edited sheet's name

I'm trying to come up with a script that will print a timestamp of the last edit made to a specific tab within a google sheet. Imagine you have 17 retail stores, each one having its own tab with a set ...
2
votes
1answer
1k views

Any way to separately Validate and create a Dropdowns in Google Sheets?

Sample Scenario: Sheet 1 has a 3 Dropdowns from sheet 2 Sheet 2 has two lists, {"A";"B"}, and a second dynamic list that copies the first IF the value doesn't already appear on sheet 1 The behaviour ...
2
votes
1answer
526 views

How to export (in JSON) a filtered view?

I use Google Spreadsheets as a "web service" to generate a JSON having the information to display in my website. It is OK to export a JSON file: var url = 'spreadsheets.//list/1gRIVkL117xxG8m9nQRa--...
2
votes
1answer
1k views

Email notification if cell value goes below set amount

I'm having trouble finding a Google sheet script that does what I need so I figured I would ask if someone could help me. Basically I have a simple sheet setup to track inventory of one item. There is ...
2
votes
0answers
5k views

Google sheets query returns "loading..."

I've put together a web application that relies on a series of Google components. Google Forms collects data from users Google sheets saves the data (automatically) Additional "calculated" columns ...
2
votes
1answer
159 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 ...
2
votes
1answer
1k views

How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row?

I'm trying to setup a form and spreadsheet database to track inventory on a daily basis at a remote location. The idea is that the person at the remote location can update our stock via a simple ...
2
votes
1answer
429 views

Download list of contact information and check if Google Account

In order to retrieve a list of all of my contacts into a Google spreadsheet, I exported them as a Google CSV and then imported them into a new spreadsheet. Determine if the contact is a Google ...
2
votes
1answer
4k views

Why would I be getting the error: Too many simultaneous invocations: Spreadsheets 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 ...
2
votes
1answer
671 views

Is there a script to stamp & password protect a PDF on Google Drive?

I have a few PDFs on a Google Drive, I would like to watermark/stamp & password protect (to disallow editing) before I share them. Is there a solution using Google App Scripts?
2
votes
1answer
3k views

Reference a Google Spreadsheet Row containing specific Text within Google App Script for spreadsheets

I am having trouble finding a function that can reference a row (the number of that row) containing specific text as criteria. I thought that VLookUp would work, but it's not really. Ideally it's ...
1
vote
1answer
150 views

Where is the Apps Script located which Google Account is referring to?

Scenario Under myaccount.google.com > security > Third party apps page, there are a few App Scripts apps listed, like so: But there is no link or project ID or any way to actually reach or ...
1
vote
1answer
20 views

How to Set Values for Discontinuous Cell Locations All at Once?

I am trying to populate a form template on a sheet. Say the a1 locations of the fields on the form to populate are: [B7, B9, D8, D11] (In actuality, I have 65 different fields.) I want to populate ...
1
vote
0answers
37 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 ...
1
vote
2answers
126 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
0answers
19 views

Extract the first three unique characters of a name to create a code

I have a column D where I store the VENDOR COMPANY NAME. I have a column B where I want to calculate a SHORTCODE unique for each vendor. This code will be the first 3 letters of the VENDOR COMPANY ...
1
vote
1answer
23 views

How do I print a specific number of A based on a value from a cell in Google Sheets

There's a cell that would contain the number of times a letter would be printed in a cell. i.e. cell a1 = 3. b1 should look as "A A A" my rough script is: for(var arow=0; arow<4; arow++) {...
1
vote
0answers
19 views

Function to create a calendar for one year with gaps (date and days in separate cells)

Is it possible to have a function within a Google Docs spreadsheet which I can link to a button which will create a calendar in the following format: Number will be 1 - 365 for the days Date will be ...
1
vote
0answers
43 views

Function("url") With A1 Cell Contents as Part of URL Query

I am trying to import JSON data into Google Sheets essentially like IMPORTHTML("URL"&A1&"URL"). Basically trying to adjust the URL by the contents of Cell A1. It seems to ...
1
vote
0answers
41 views

How to Use Google Sheets' Macro to Copy Data from Volatile (randbetween) Cell

What I would like to be able to do is execute a macro that copies the data from a cell with a randbetween formula, and pastes that data (copy/paste values only) into another cell. When I attempt this ...
1
vote
0answers
19 views

How to update author detail of comments in Google Sheets?

I want to update the 'displayName' and 'email' of commenters within Google Sheet using 'Spreadsheet' Service along with 'Drive' service. I have used both Drive.comments.patch(comments, fileId, ...
1
vote
0answers
13 views

How to create formula that copies 15 rows and inserts below

I am a newbie when it comes to formulae and macros in spreadsheets. I am creating a manager sheet for a tabletop gaming group, and during the game players may add additional characters to their ...
1
vote
0answers
36 views

Is there a way to transpose rows with an varying number of columns and replicate cells based on number of columns?

I have a Google Form featuring an name, date, and checkbox field capable of generating multiple choices, anywhere from 1 to 50. I'm looking to have this form filled monthly by hundreds of users. When ...

1
2 3 4 5
9