Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
9
votes
2answers
10k 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
86 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
37 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
3 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 ...
-1
votes
1answer
28 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 ...
-1
votes
0answers
13 views

How to change empty cells to Zero [closed]

I have another problem I also wanted the zeros to appear in empty cells like this one or 'til the CHANGE OF RECORD section but the problem is I'm using this code for each rows which will make it a ...
0
votes
1answer
147 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
16 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
21 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
20 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
14 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. '...
-1
votes
0answers
7 views

How to create a product tour of a spreadsheet using Google Script [closed]

I am searching for a way to create a little "guided tour" to a spreadsheet using Google Script, in order to show users the proper way to fill the spreadsheet and the purpose of some buttons (...
-1
votes
0answers
12 views

How can I add my custom-built Google Doc utility (Google Apps Script) to other Google Docs?

With the help of StackExchangers, I built an automatic formatting tool in Google Apps Script that I use constantly in Google Docs. It appears under "Utilities". If I make a copy of the ...
4
votes
4answers
11k views

Getting all files' file-id from a folder in Google Drive

I've a folder in my Google Drive and would like to get the file id of all the files against their file name in a spreadsheet. Currently I have to open all the files in the folder individually and ...
2
votes
4answers
968 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 ...
1
vote
1answer
21 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
67 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....
1
vote
1answer
17 views

Google Docs footer error

I got the below script for automatic "last edited" timestamp in the footer of a Google Document. function lastEdited() { var doc = DocumentApp.getActiveDocument(); var id = doc.getId(); ...
0
votes
1answer
26 views

Need help with Google Sheets moving rows and setting formatting upon move completion for Google Sheets Script

I have a script which I use to copy rows with checked checkboxes to a "Copmpleted sheet". The script is set to run on a timer and it works without any issues. The script and description can ...
0
votes
1answer
15 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
1answer
12 views

How to show dialog box in the Google Apps Script itself?

How to show dialog box in the Google Apps Script itself? ScriptApp has no getUi() method.
0
votes
2answers
58 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
35 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
36 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
2answers
23 views

How do I change the date to the coming Monday every Friday at 9:30 pm in Google Sheets?

I would like to write a function that checks what day of the week today is, and whenever it is Friday 9:30 pm it should populate the cell with the date of the coming Monday. So since today is Friday ...
0
votes
0answers
25 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
0answers
16 views

Google Forms script on specific responses [closed]

I have a form that picks up messages from staff, alongside a date they want the messages relaying to the mailing list. I know I need a script each day (at 8.30am exactly), to look at the responses and ...
0
votes
1answer
34 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
218 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
55 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
38 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
353 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
9 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
vote
1answer
45 views

Need help with Google moving rows on open for Google Sheets Script

I am fairly new to Google script. My knowledge of Java is limited as well (I believe the two are similar). I have a script that moves rows when a checkbox is checked. It also adds some rows and does ...
0
votes
1answer
732 views

Saving Google Doc as PDF isn't showing all images

I'm trying to set up a Google Sheet that populates fields and inserts images in a Google Doc template and downloads it as a PDF. I'm having some trouble with the images part - in particular, when I ...
0
votes
1answer
4k views

Google Sheets macro for printing

I wrote the next macro, but isn't working. For example: I have a button on the sheet (invoerblad) for printing, the button must print the sheet (Printblad), and when the print action is finished, it ...
0
votes
2answers
80 views

Duplicating sheets with simple date from a named range

When I run the script to duplicate sheets with a date is give the tab a long date name Thu Oct 18 2018 00:00:00 GMT-0400 (EDT). I would like it to read in a 10/18/18 format.
0
votes
1answer
466 views

How to extract a link URL from one word within a paragraph element in Google Docs with Google Apps Script

I'm trying to pull the URL of a hyperlinked word from within a paragraph element in a Google Document using Google Apps Script. Below are two GAS functions. The 1st doesn't work. The hyperlink is only ...
0
votes
1answer
174 views

Help needed with script that auto-updates cells in column in Google Spreadsheet showing last modify date

I'm looking for some help with a script for a spreadsheet. I found a script (see below) that does half the trick for me, but I encounter two difficulties. What I am trying to accomplish is the ...
1
vote
1answer
533 views

Linking multiple script created forms to 1 sheet in pre-existing spreadsheet

I work in manufacturing. I have created a Google form for our leadership safety audits which requires unique information (name(s), area being audited, and date) and also includes 25 multiple choice ...
1
vote
1answer
23 views

How to create dynamic text which include values from cells for alert in Google Sheets script?

I wrote the script which transfer values from one sheet to another sheet. I added message of confirmation (alert) which is showing list of values before their transfer: var response = ui.alert('...

1
2 3 4 5
28