Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
1
vote
1answer
14k views

Cell last modified date & user stamp Google Sheets?

I want the cells of two columns in my Spreadsheet to auto-update with the date (m/d/y) and name of user when cells in a certain range are edited. For instance, Catherine goes to sheet on 3/6/2017 (...
0
votes
1answer
19 views

Automatically update inserted sheets cells in Google Docs

I have a Google Doc which has linked cells to Google Sheets. I want to type a Script that automatically updates the document whenever the cells in Google Sheets are changed (it receives answers from a ...
0
votes
1answer
336 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
37 views

How do I stop script from reading rows more than once?

Running into an issue where the script is reading the same row more than once. For example, I run the script and it pushes row 1 to the calendar as intended. When I add more info in row 2 and run the ...
-1
votes
1answer
22 views

Google Scripts Sheets Conditional Sorting

I just recently discovered Google Scripts, and I was wondering if there was a way to sort by a function. For example, if the values in column B were in the range of all real numbers, would there be a ...
0
votes
1answer
48 views

“Exception: Service Spreadsheets failed while accessing document with id” when using insertCells

I have a confusing problem. I'm using GAS to shift several cells down by one. Worked fine for a while, then I started getting this error message: Exception: Service Spreadsheets failed while ...
0
votes
1answer
36 views

Add fields and update array and scripts

I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that. Here is what it should look like: The URL is the link for the form. Here is ...
2
votes
1answer
2k views

Conditional formatting with color scale for many rows independently once

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values)...
7
votes
1answer
6k views

Send email with an attachment located in Google Drive

I want to send a PDF attachment located on Google Drive from a Sheet. Code I'm using is: function sendEmails() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // First row of ...
1
vote
1answer
90 views

Can I record the users within a Google sheet?

I'd like to have an auto-generated list of users with timestamps within a spreadsheet, tabs, or if possible specific cells. Ideally, once a cell has been altered, I'd like another cell to record the ...
0
votes
1answer
26 views

Change File Name Automation in Google App Script

I am working on a script in App Script that will change the file name of all the files in a certain folder based on their creation date using this format yyyy_MM_dd_filename. I was able to create the ...
0
votes
1answer
293 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
0
votes
2answers
254 views

How to Clear Cell(s) based on the Value of another Cell

I am looking for a script that will clear cell(s) when the value of another cell changes or is equal to ("X"). In the attachment I would like when Column "G" gets checked marked (TRUE), then ...
-1
votes
1answer
39 views

How to change specific cell to Zero

I'm currently using this code I got from Google. But the problem is it changes not only the range I specify which is N9:N14 but it goes til N54 whats wrong with this code I want only these ...
26
votes
4answers
100k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
0
votes
1answer
29 views

Filter the data of two different column

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JIRA which consists of the following columns: Issue Type Key Status Priority Summary ...
1
vote
2answers
134 views

Adding 1 to a Cell with VLookup

So this is my first time trying to write a script, and the sheet I am working on is here. The script I'm trying to run would allow me to push a button and have it use Vlookup to add 1 to a cell in ...
0
votes
0answers
22 views

Automatically append database query results to a Google sheet

Looking for conceptual help in automating the appending of data to a Google Sheet. I have a PostgreSQL database currently hosted on Heroku (AWS) that is backing a Ruby on Rails application. What I'd ...
10
votes
1answer
11k 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
96 views

Changing a cell value based on another cell's color?

In my Google Sheets in column F, I want to include the word "LATER" based on magenta color (#ff00ff) that is in column B starting from row 227, how should I write this script? I'll really appreciate ...
0
votes
1answer
38 views

How to Copy Cell Value and Format From One Tab to Another Using a Formula?

An answer was provided in this question: webapps: Cell reference with colour formatting for doing so within a tab, but not across tabs. Does anyone have suggestions for a script that will work across ...
0
votes
2answers
16 views

Auto Sort different ranges

I have a google sheet that I would like to sort different ranges by the 2nd column in each range. Example: A B C D E F G p 7 1 h 9 1 q 6 2 j 3 2 g 9 3 p 7 3 I need the range A:C sorted by B ...
0
votes
0answers
10 views

Created an OnFormSubmit trigger but still getting error

I'm trying to create a script that will populate a Google Sheet template based on a Google Form--basically, following this verbatim . My code looks like this: function onFormSubmit(e) { //open the ...
0
votes
1answer
5k 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
158 views

Limit number of rows

I have a Google Sheets document that keeps adding hundreds of rows and slowing down the project, and sometimes causing it to crash. Is there a way to limit the number of rows to say 100. I do not ...
0
votes
0answers
21 views

Is there a way to pull first modification date from a cell with Google App Script?

I have a bunch of Google Spreadsheets with a lot of data (inputs from a webpage from past years), but unfortunately, there was no column with a timestamp at that time. Is there a way to pull from ...
2
votes
1answer
75 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 ...
0
votes
1answer
22 views

Script that reads rows and columns in a sheet and returns strings with frequency of occurrence

first time caller! I have a single column that contains comma separated names (lots of names) -- I am trying to find a script that can read the comma separated string entries in cell and give me a ...
0
votes
2answers
25 views

Insert text in every Nth cell

Here is the sample Spreadsheet: https://docs.google.com/spreadsheets/d/1wlO33EWcERa0yYL2xb3BINFt5f441p9MbcB2mPe8eZQ/edit?usp=sharing I need to insert the text in cell D1 into every third cell in ...
0
votes
0answers
23 views

Connecting Google Sheets / Gmail script with several Google Accounts

I have written a Google Sheets script which allows creating Gmail drafts based on data in a Google Sheet. This currently only created drafts for the user who is currently logged in. Is it possible to ...
0
votes
1answer
17 views

Google Script returning full timezone information from a Google Sheet cell - how to stop it?

My project is using Google Sheets to create a register system for childcare in a school. Because of the sensitive information within the document, I'm not at liberty to share access to the project. '...
2
votes
4answers
1k views

Avoid decimal point when integer

Is there a way to use a custom number format in Google Sheets so that decimal point will be visible only when decimals are present? For instance 9 8,2 10 Is there a newer solution about achieving ...
0
votes
1answer
22 views

How to apply “sendAlertEmailWhenCellValueIsNotEmpty” script to multiple tabs in Google Sheets

I would like to apply this to let's say "Weekly" and "Monthly" tab. Here's my script: function sendAlertEmailWhenCellValueIsNotEmpty(){ //send an email alert when cellToWatch is not empty //set ...
2
votes
2answers
5k 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 ...
2
votes
2answers
84 views

How to get hidden URL value in Google Sheets?

I have a column of URLs with hidden URL values (meaning that when you click on it you are redirected to the real website e.g. www.disney.com): Cell example: =HYPERLINK("https://www.example.com/go....
0
votes
1answer
16 views

TimeStamp syntax to appear 24 Hrs Timestamp

I am using var TIMESTAMP_FORMAT = "yyyy-MM-dd hh:mm:ss a"; var TZ = "GMT-7"; in my script code of Google document I want it to be convert into 24 hrs timestamp What changes should I do ?
0
votes
1answer
3k views

Show/Hide Rows based on text in a cell

I'm trying to toggle which rows are shown/hidden on multiple sheets in my Google Sheets document. Simply put, a user will provide an input on Sheet1, which will populate a "show" or "hide" value in ...
0
votes
2answers
59 views

How do I keep all the values when I split a cell by delimiter (comma) in Google Sheets?

I have a spreadsheet in Google Sheets, where I am currently running a script. There are two tabs, the first tab is the following: The second tab is the following: This is the script I am running: ...
0
votes
0answers
39 views

Copy from individual cell in Google Sheets Table Chart

I'm using appscript to build and insert a table chart into a sheet when a button has been pushed. var chart = sheet.newChart() .setChartType(Charts.ChartType.TABLE) .addRange(outrange2) .setPosition(2,...
0
votes
1answer
38 views

How best to handle a range moving between getValue and setValue?

I have a simple experiment to illustrate the issue: function testRangeAddressAfterSort() { var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); var testSheet = spreadSheet.getSheetByName("...
0
votes
0answers
37 views

How do I pass hidden metadata into a Google Apps Scripts HTML service?

I found this interesting question: How do I record the value of GET parameters passed to a Google form in hidden inputs? One of those answers says: Google Forms doesn't supports hidden form fields ...
6
votes
5answers
23k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
0
votes
1answer
37 views

Trying to change sharing access of team drive files(more than 2,000 files) and generate link on google sheet via google script

I have a lot of files on a team/shared drive that I want to generate links of. I managed to do it, but I also want to make the links viewable by anyone with the link. When I added the script to change ...
0
votes
2answers
245 views

Auto Create New Google Sheet With Column Headers Based on List of Names

I'm trying to create a VERY basic CRM tool. I want to create a running list of customer names. Once I enter a name in Column A I'd like a new Sheet automatically created with pre-set columns I can ...
12
votes
2answers
54k 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 ...
1
vote
1answer
56 views

ImportHTML Altering Data Format/URL Fetch Issue

4/17 6PM Update *** I am now trying to use the URL Fetch App. I've been playing with this for hours, but can't seem to figure out the issue. I am just trying to get the info from this site as plain ...
1
vote
2answers
2k 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
39 views

Randomly mark the column based on the percentage of the total data from each operator

I have data as in this link. The data will be checked by several operators. Each operator must check data as much as 50% of the total data of each operator randomly. If the number of data from the ...
0
votes
1answer
362 views

How can I use a script across multiple pages of a Google Sheet

I'll start by saying I'm very new to this. Here's my problem. I have a GoogleSheet I created to use as a seating chart in my classes. It is designed so that when I click the blue check button the cell ...
0
votes
0answers
12 views

How to auto-reply to reply-to field?

I would like send auto-reply mails to email addresses written in reply-to field. How this can be achieved? Sample Mail Received : from: abc@gmail.com reply-to: def@gmail.com to: ghi@gmail....

1
2
3 4 5
28