Google Apps Script (GAS) is a JavaScript cloud scripting language that lets you extend Google Apps and build web applications.

learn more… | top users | synonyms (1)

3
votes
2answers
261 views

Can I convert a MS Word document to a Google Document format using GAS?

I know how to convert a Microsoft Word document to Google Documents format manually (right click, open with Google Drive). But is there any way to do this using Google Apps Script? I have looked ...
3
votes
0answers
95 views

Color entire row based on the status in Column L [duplicate]

How can I make my spreadsheet look like in the picture below? Starting row 5, I want to color the entire row based on the status in Column L.
3
votes
1answer
979 views

Google Spreadsheets: add unique QR code field to a spreadsheet based on a form in google docs using Script Trigger

At the end of each row filled in, using a custom Google Form, I'm adding three more columns: Date of creation Unique ID QR code (it is a list of computers) Unique ID and QR code are unique. QR ...
3
votes
0answers
430 views

How can I get the body of a Gmail message before the quoted text with GmailApp? [closed]

I'm programming a little Google App Script for which I need to get the real body of an e-mail (using gmail services). By real I mean the pure body of a particular message, without the text that ...
3
votes
1answer
229 views

How can I create a Google Spreadsheet 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 (though I'd really like to learn how to use it, so if you have any good tutorials or tips, let me know). ...
2
votes
2answers
1k views

Is it possible to pull my Company directory into Google sites intranet?

We use Google apps for enterprise, and I want to build an intranet, based on the Google sites intranet template In the Directory area, I want that the list will be updated from my contact/company ...
2
votes
3answers
115 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...
2
votes
2answers
177 views

Incrementing value read from spreadsheet exploding. 1 + 1 = 200 000 000 000

I read a value (in this case 7) from a Google Spreadsheet with a script, increase that value using source++ and write the increment back to the spreadsheet. I expect a series of 7, 8, 9, 10. ...
2
votes
1answer
195 views

Updating logging sheet from another sheet doesn't work after inserting code offset

I am trying to log the data, formulas and times of cell edits from one sheet into a logging sheet. The problem I am having is that it only seems to record changes made in the logging sheet. This has ...
2
votes
1answer
2k views

Date and time stamp automatically added in column

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
2
votes
1answer
673 views

Place cell data from a Google Spreadsheet into a Google Document

I'm trying to populate an invoice. I have a Google Spreadsheet that calculates hours per project, and I want to bring that into a Google Document, but despite much searching, have found no way to do ...
2
votes
1answer
431 views

Arrayformula with dates before and after

Here is what my spreadsheet looks like I enter recurring weekly payments details in G,H,I,J & K (Name,Amount,Day of week, Start Date, End Date respectively). This arrayformula: ...
2
votes
2answers
1k views

Making all cells in a column links from the values within the cell?

I have a column of cells in a Google Spreadsheet with values such as: 512 2123 2342 3532 What I would like to do is convert all these into a hyperlink and keep the value as the link text: ...
2
votes
1answer
2k views

Google Script for pulling gmail addresses into a spreadsheet

This Google Script goes through and finds the emails in my gmail which are in a label called "LabelToGet" and send me a link to the spreadsheet created with these emails. It works well, but I have ...
2
votes
1answer
805 views

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length: function isLengthLessThan(length, text) { return (text.length < length) }; How can I set up data ...
2
votes
1answer
78 views

Formula value change after a script pastes into another range

I have a script that pastes a formula from one cell into a range in a column. When pasted the range just disappears. I mean the whole formula is OK except the pointer. Any ideas how to deal with ...
2
votes
2answers
106 views

How can I apply a script to every sheet but the first one on a spreadsheet?

I've been working on a shared Google spreadsheet on Google Drive. The document has different sheets with a "Last Modified Date" Cell. I found an script for this to be auto-filled searching here: ...
2
votes
1answer
710 views

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet?

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet? Some of the responses on one will be public and viewable by anyone, but others will be private.
2
votes
3answers
1k views

Split, reorder, concat inline in Google Spreadsheets

I want to split a cell, reorder the pieces and recombine. How do I do that inline. For example: | name | reordered name | | Page,Larry | Larry Page | | Brin,Sergey | Sergey Brin | ...
2
votes
1answer
276 views

Repeat range under each other in Google Spreadsheets

I have a range of two columns with year and dates. In another sheet I want to repeat this range for X amount of times under each other. How can I do this? So for example I have this range: Year | ...
2
votes
1answer
1k views

How can I apply a script to only one specific sheet within a spreadsheet

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A function onEdit(event){ var sheet = ...
2
votes
3answers
6k views

Google Scripts : How to distinguish Empty cells from Zero

I want to count a number of specific responses from a form where the response can be left empty, zero or any other number 'd'. The following code: var data = sheet.getRange("B2:L").getValues(); ...
2
votes
1answer
1k views

Batch copying/renaming in Google Docs

I need to copy and rename a base form (a spreadsheet) to several other forms in Google Docs. Imagine I have the following form: My Base Form - @name (@course) After running the script or whatever, ...
2
votes
1answer
15 views

Google Spreadsheet Timestamp on Edit?

I got the major part of answer on this link: Google Spreadsheet Timestamp? The code on this link is below: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ...
2
votes
1answer
37 views

Script to create a new worksheet in Google Spreadsheet

I want to be able to have a button on a Google Spreadsheet. The user clicks on the button and a new worksheet is created based on a template (or blank existing worksheet). The new worksheet should ...
2
votes
1answer
28 views

How to sort Google Sheets columns by row height

I'm working on a sheet that involves multiple columns corresponding to various data. Each column has a varying number of cells of varying heights with wrapped text. What I want to do is make the sheet ...
2
votes
1answer
62 views

How do I loop a script through all pages but the first one?

I'm very new to all this. I'm trying to write a script that sorts sheets by descending date order each time a new row is entered. I need the script to run on all pages but the first (the summary ...
2
votes
1answer
120 views

Adding a script to Google Forms to identify incorrect phone numbers

I currently use Google Forms as a way for applicants to submit their forms. One of the fields that is required is for their contact information. In my country phone numbers have 11 digits. Is there a ...
2
votes
1answer
215 views

How do I refresh script results when a cell color is changed?

I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. ...
2
votes
1answer
141 views

Run script when clicked on picture in published Google spreadsheet?

I used the script underneath in Google Spreadsheet and added an image that when clicked upon runs the GOTOSHEETONE function to navigate to sheet: SHEETONE. So far so good. However, when I publish my ...
2
votes
1answer
49 views

Google Sheets Passing Range from separate sheet into custom function

I have the following custom function I've built to basically reproduce a needle haystack type of functionality function ListBy (Range, Range2, needle) { var holder = []; if(typeof Range != ...
2
votes
1answer
638 views

Prevent “invoked too many times per second” error in Google Apps Script

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom ...
2
votes
2answers
114 views

Return name of range as string in Google Spreadsheets

I have a range that is named apple in Google Spreadsheets. I thought a method like getRangeName("apple") would exist. I want to get the names of many ranges and set them as the header for the columns ...
2
votes
2answers
204 views

Automating Calls to New Google Sheets Add-ons

I'm building a Google Spreadsheet that uses an official add-on to pull data from Google Analytics using the new add-on store. What I'd like to have it do is automatically refresh the data every day at ...
2
votes
1answer
2k views

Get cell row and column index in Google Spreadsheet

I have spreadsheet with two sheets inside. The name of the first sheet is "fr" and the name of the second sheet "cql". Now the first sheet contains data as the picture given below. And the second ...
2
votes
1answer
62 views

How to capture a cell when entered or selected?

I am trying to capture, programmatically, the on cell entered and on cell selected method, but I don't seem to be able to find any documentation on it. I can get the time stamp for exiting the cell ...
2
votes
1answer
194 views

Referencing cells above and to the left in script

I'm trying to make a script for a custom function in google spreadsheet to work out the hours worked in a day function hwtd(). I've got it perfect in the cell (((F8-E8)+(F9-E9))+(IF((F10<E10), ...
2
votes
1answer
144 views

Putting my company directory into Google Sites with pictures

I'm using this code to pull the name and emails of my users into an intranet page on Google Sites. I was just wondering, is there some code that I can add to this so I can retrieve pictures? I would ...
2
votes
1answer
4k views

send HTML email from a spreadsheet using a different alias

I've been frolicking around sending emails from Google Spreadsheet. The sent email has two requirements to fill: It needs to be send from my work-alias It needs to be as HTML I can do the former ...
2
votes
1answer
182 views

onEdit only updates top row after pasting (editing) multiple rows

I'm using a Google Spreadsheet and have a script that updates the "last email" column with a date stamp when an employee enters an email address into the prior column. The problem occurs when my ...
2
votes
1answer
652 views

Google Apps Script - exceeded maximum execution time

I'm currently developing a new spreadsheet for my company to make one of the tasks we do on a weekly basis more easier - the way it works is data is imported into this spreadsheet and then the scripts ...
2
votes
1answer
120 views

Geocode in Google spreadsheets, keep results

I have a form for internships and I am passing over the address of the internship provider to a function which gives me latitude and longitude for it, which is then consumed by a webmap using tabletop ...
2
votes
1answer
211 views

Grading a Google Spreadsheet by answers given in a Google Form

I'm currently working in a school and students answered a 50 question form on ICT Ability. The results of this form have been put into a spreadsheet and I have to order the names in terms of ability. ...
2
votes
1answer
2k views

Panel with Vertical Scroll Bar that allows navigating to different worksheets

I am attempting to build a Script that would open a window with each worksheet I have in my spreadsheet, allowing me to click on the worksheet name to navigate to that sheet. I found the following ...
2
votes
1answer
814 views

How to automatically open a created text document after it being created

I've used this script to create a Google Document (like word document) with Google Spreadsheet. But, when I do so, the new text document created from a template is placed in the default chosen folder. ...
2
votes
1answer
846 views

If cell equals a value then move row to different Google Spreadsheet

I've got a script that sorts a Google Spreadsheet when a value in a column is changed. I'd like to add to the script the following functionality: If the value for column E = "Complete" then the ...
2
votes
1answer
454 views

Enabling script to run as author in a shared Google spreadsheet

I created a spreadsheet that I'd like to share with anyone with the URL. The sheet contains several app script functions. These functions both change the content of the sheet and copy data to ...
2
votes
1answer
1k views

How do you add a custom font size in Google Spreadsheets?

How do I add a custom font size for a Google Spreadsheet I have? There are some defaults like 14pt, 18pt, 24pt and 36pt, but I would like to have a font size like 30pt, is it possible?
2
votes
1answer
31 views

Importing columns across two Google Sheets in same spreadsheet

I have one spreadsheet which houses the database of my members. In the same spreadsheet, I have another sheet where I will track attendance for the events for each of these members. First issue: ...
2
votes
0answers
30 views

Adding columns automatically to Google Sheets

I am using Zaption and Socrative as assessment tools while teaching. After I download the CSV files, I import them to Google Sheets. I figured out a way of having the results sent to a PDF after ...