Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
4
votes
1answer
2k 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 ...
1
vote
1answer
896 views

Need to Make this Code Sheet Name specific rather then the active sheet

I got this code on here but need to modify it so it only sees 3 sheets. 1 is Active Tickets. 2 is Closed Tickets. 3 is Pipeline. This is the code i currently have: function onEdit(event) { ...
2
votes
1answer
19k views

Fetch cell row and column indices in Google Sheets

I have a spreadsheet with two sheets inside. The name of the first sheet is fr and the name of the second sheet cql. The first sheet contains data as below: and the second as follows: I want do ...
2
votes
1answer
113 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 ...
13
votes
4answers
7k views

How to export / import a Google Apps Script as a file into a new spreadsheet?

I fear this may have an obvious answer, but I've been knocking my head over this and can't find it! When creating a new spreadsheet, we can easily create a script within it. I like to call it a macro....
2
votes
2answers
499 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: ...
3
votes
1answer
465 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), (F10-...
1
vote
1answer
9k views

Date and time stamp automatically added in column [duplicate]

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: ...
0
votes
1answer
116 views

Format a Cell with Script

I need to write a script to see if a cells value is found in an array, and if it is change the background color of that cell. Can't do this with "Conditional Formatting" as it needs to compare with ...
2
votes
2answers
23k views

Google Spreadsheet; Function to Search a Sheet for String and Return Value of Next Column

Here is what I have so far. it will: Search for keyword in my other sheet (JSON) When it finds the keyword (modname) it returns the row # The keyword is on the A column. What I want is for a ...
1
vote
1answer
386 views

Update Google Drive Chart based on which row selected

I have a table with some pretty basic data (it's a time series going across the columns, and different budget items going down). I want to create a chart that plots a particular row's data against ...
1
vote
1answer
4k views

Calculate score and ranking in Google Spreadsheet

I want to create a Google Spreadsheet which will work as a ranking system for a game. One sheet, "Ranking", should display the current ranking of the players, based on their points. Another sheet, "...
4
votes
2answers
972 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
1answer
7k views

Get the Id of the response from Google Forms via a Google Apps Script

How do I get responseId ? var form = FormApp.openById('myGoogleFormID'); var response = form.getResponse(responseId); function onSubmit() { // Here I'd like to get the id of the current response } ...
6
votes
2answers
5k views

Place cell data from Sheets into Docs

I'm trying to populate an invoice. I have a Google Spreadsheets that calculates hours per project, and I want to bring that into a Google Docs. Despite much searching, I have found no way to do this. ...
1
vote
1answer
368 views

500 error with Google admin sdk

I'm using the Admin SDK with Google Apps Script to create a directory of user's names and emails on a Google Site. The code seems to work fine because when I view the logs I see the results. [14-02-...
-3
votes
1answer
395 views

How do I conditionally format based on the value of cells in a column?

Unable to create a script. I would like to know if there is one that would allow me to color an entire row based on the value (a different color value) that I will find in the cells of column B. For ...
1
vote
1answer
1k views

Using the Google Admin SDK

I'm trying to use the Google Admin SDK to get all of the users for a directory on our site but I'm trying to figure out what exactly page on line 4 of the code means. function listAllUsers() { ...
1
vote
1answer
97 views

Google spreadsheet, sort adds a bunch of rows

I have a function that sorts a table, unfortunately when I call my function it adds a bunch of empty rows at the bottom that I don't like. var kotlijst = SpreadsheetApp.getActiveSpreadsheet() ....
2
votes
1answer
777 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 ...
1
vote
3answers
3k views

Make a counter, that counts cell change

I have a cell, let us call it A1, that contains an IF function. Cell A1 changes between two types of text: Healthy Injured What I want is a counter in for example B1 that counts how many times A1 ...
6
votes
1answer
37k 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 ...
3
votes
1answer
718 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, ...
2
votes
1answer
145 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 ...
0
votes
1answer
229 views

Provide a timestamp when a particular row is edited

I noticed this code from a thread I was reviewing as I needed the same functionality to provide a timestamp when a particular row is edited in a Google Spreadsheet; function onEdit() { var s = ...
3
votes
2answers
2k views

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

I'm using a Google Spreadsheets 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 ...
3
votes
1answer
2k 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 ...
5
votes
1answer
10k views

In Google Script, get the spreadsheet cell calling a custom function

In VBA for Excel, I can get the column of the cell making the call to a custom function like this: ThisCol = Application.Caller.Column What is the equivalent in Google Scripts? I've been trying to ...
2
votes
1answer
1k 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: =...
1
vote
1answer
607 views

Time Triggered Script to Change Cell Contents

What I want to do is for example: A time will be manually entered into a cell say D3, when it is 20 minutes before this I want cell D1 to change to "20 MIN", WHEN IT IS 5 minutes before I want to ...
1
vote
0answers
421 views

Entering time data quickly using code for a specific range of cells

What API code do I use if I want to quickly enter 24 hour times (without entering the colon and still retaining time formatting) for a certain range of cells? I know the following code works for ...
-1
votes
1answer
276 views

Creating a document (e.g,, MS Word) attachment in Gmail from a spreadsheet script

I've created a document file in the spreadsheet script. What I want to do is, from the script, create an email message with the document file (NOT a PDF) as a Gmail attachment. I spent about 2 days ...
1
vote
2answers
3k views

Script to automatically change text value

In GDocs under Tools > Preferences, I can define automatic corrections, so that if I type l*, it spells low, h* -> high, etc. In Google Spreadsheet there is nothing like that. Can I do that with ...
1
vote
1answer
1k views

Limiting range in OnEdit timestamp script

I would like to be able to limit the range of cells being assessed by this script to the first column. Apply the timestamp to the last column and only have the script run on a specific sheet "Short ...
0
votes
1answer
3k views

How to extend formulas and validation to newly added row (using “Add more rows” at the bottom, not using insert)?

I have formulas and validation in columns D, E, L, M and N. I want these formulas and validation to be automatic whenever I add rows using the option "Add more rows at the bottom", not using insert or ...
2
votes
3answers
9k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
0
votes
1answer
2k views

How can I record the date/time when a new row is added?

I want to add a date value when a new row is added in Google spreadsheet. I have seen the script on Add a 'creation date' value when a new line is edited, but I was not able to modify the code to my ...
2
votes
2answers
168 views

Google Spreadsheet - periodically stamp sheet contents

As the title, is there a way to have a "print" of the content of a sheet periodically? i.a., I'd like to have a trigger that every Monday at 08.15 A.M. copy the contents of a group of a cell of a ...
2
votes
0answers
110 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.
23
votes
5answers
20k views

Default sheet in Google Spreadsheet

Is it possible to mark a sheet as "Default" so that if anyone opens a Google Docs spreadsheet, the corresponding sheet is opened instead of the first one?
7
votes
2answers
1k views

Custom data transpose?

I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than ...
1
vote
1answer
2k views

How to use triggers with functions that require parameters from the spreadsheet?

I found a simple and great solution to retrieve JSON into a Google spreadsheet. I want the function ImportJSON to update regularly via a trigger (event or time-based, I still haven't decided what I ...
2
votes
1answer
4k views

Trying to get the id of a file, outputs an error [closed]

I have a folder and inside two spreadsheets. I can open the folder but when I try to get the id of the files inside I get this: Im getting this error TypeError: Cannot find function getId in ...
4
votes
2answers
3k views

How do I automatically expand a =QUERY when receiving submissions from Google Forms?

The Query below 'works' on a line by line basis (when manually filled down), but it does not 'autofill' down when new data is added to the responses sheet. The data being added to the responses sheet ...
1
vote
2answers
2k views

INDEX MATCH in array formula

I'm making a database in Google Drive. In one column I have abbreviations of a number of departments someone is in, but people can be linked to multiple departments. The abbreviations are comma-...
4
votes
2answers
12k views

Add a 'creation date' value when a new line is edited

I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's an example. People can add tasks to column B and I will assign them to the right people in the team, ...
1
vote
1answer
2k views

Script error trying to populate form drop down menu from spreadsheet

We are trying to link a list on a Google Spreadsheet to a drop down menu on a Google Form. The twist is that this Google Spreadsheet list will be continuously updated and needs to automatically ...
1
vote
2answers
7k views

Insert column across multiple sheets

I have a Google spreadsheet with multiple sheets. All the sheets have the same header row, but different data (it's a month by month look at some stats). Is there a way to insert a column on one ...
2
votes
0answers
1k views

Apps Script web app accessing Google Spreadsheets gets “#VALUE!” on every cell [closed]

I have a Google Spreadsheets with a couple of sheets that get data from other spreadsheets via IMPORTRANGE function and then calculate numeric values based on that data. Then, I assembled a code ...
1
vote
0answers
730 views

'setDataValidation' in Google Apps Script not working

Have a problem with setting data validation rules in a Google Sheets using Apps Script. Below are two functions, one is an 'onChange' function which will be triggered whenever a user changes the ...