Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

414 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
2answers
413 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
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 ...
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
119 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
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
22 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
136 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
395 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
326 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
851 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
67 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
55 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
19 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
280 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
1answer
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
421 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
655 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
5k 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
104 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
735 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
1k 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
998 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
515 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
156 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
425 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
657 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
0answers
14 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
18 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
15 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
27 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
26 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
18 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
11 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
22 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
vote
0answers
25 views

How to count cells with color that contain string in a range

I need to count the cells that have a color and contain a string within a range When I add a 3rd param it says Range not found line 14 which is var range = activeSheet.getRange(rangeA1Notation); Here ...
1
vote
0answers
33 views

How do I have a date stamp entered when a cell is changed?

I want to autofill the date of an edit in one cell into the next cell. Specifically: When I enter something into any cell in column 2, I want the cell next to it in column 3 to autofill the date. I ...
1
vote
0answers
37 views

Google Sheets split into new rows while duplicating surrounding row entries

I am creating a spreadsheet for a service drive my non-profit is doing. As people sign up through a Google Form, the information comes in like this: However, to make it easier on our volunteers, it ...
1
vote
0answers
26 views

Google Form to sheet to Doc, with calculations after pushing to sheet

NOTE! I am not a programmer I'm just fumbling through this! I've gotten as far as I can with searches, getting lost on multiple steps as most responses don't integrate on thing to the next. Background ...
1
vote
0answers
21 views

Capturing events on embedded charts within Google Sheets

I am trying to embedded column chart in Google sheets and would like to capture events like which column was clicked, then use the info to highlight certain cells. Is that even possible in embedded ...
1
vote
1answer
43 views

Find and replace Google Script

I've read a lot of questions and I tried several Script for Google Sheets but nothing works for a very simple case: I would love to replace all the points "." with the commas "," ...
1
vote
1answer
30 views

How can I get notified of changes to certain columns in google sheets

Zapier only provides functionality to monitoring rows. How can I monitor changes to only specific columns? function sendEmailAlert() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var data = ...

1
2 3 4 5
9