Questions tagged [google-apps-script]

For questions about using Google Apps Script to extend Google applications

Filter by
Sorted by
Tagged with
0
votes
1answer
1k views

What's wrong with this Google Apps Script code?

Forewarning: I'm a noob at JavaScript, with varying experience and less knowledge. I don't get why this code fails with TypeError: can't call method "getSheetName"/undefined. (line 7, file "Koodi"). ...
7
votes
3answers
69k views

How to format the date in this Google Apps Script

I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding ...
3
votes
2answers
930 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 ...
1
vote
2answers
827 views

How can I select a sheet with a variable-supplied suffix in a script?

Here is the code I'm attempting: var day=new Date().getDay()-1; var month=new Date().getMonth(); var sheet=ss.getSheetByName((/^.\s/+month+'/'+day)); I'm new to regex, but essentially I'm writing a ...
1
vote
2answers
137 views

Is it possible to prevent people from entering data into my Google Spreadsheet?

Is it possible that I fix the order of user's entries with a script? For example, the user must fill cell A1 first, only after that he can fill cell A2. Maybe my explanation is better with an ...
1
vote
1answer
390 views

Set Selection throughout all sheets

I have a spreadsheet with multiple sheets. I want them all to scroll in sync. For example if I scroll to J18 in Sheet1, all others sheets will have the cursor at J18 and same row/column at the top ...
3
votes
1answer
2k 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 ...
14
votes
3answers
31k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
1
vote
1answer
594 views

Add a sheet from a template

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.
0
votes
1answer
232 views

Google FormsApp: Change item based on answer

Is it possible to change item, in my case - checkbox, based on user answer? For example, I have Date item, where user have to put his/her birthday, and then if user is born on 2001 or later, then add ...
0
votes
2answers
969 views

How to calculate a value based on the row/col number of another cell value?

I am trying to build a spreadsheet where one sheet holds a list of names divided into groups and a second sheet should list all the names, sorted, with a number next to each name according to a ...
2
votes
3answers
42k views

Send an email with attachment using Google Apps Script [duplicate]

I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach a PDF to this email. The PDF file is located ...
1
vote
2answers
635 views

How can I merge into one cell, all the non-zero values in a row each concatenated with the column header?

I hope this example is clear. I have played with join and filter but can't get the Summary field to display as below: Row 1 is a list of subjects. Row 2 is a teacher's subject allocation (calculated ...
2
votes
1answer
185 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
3answers
1k views

Counting number of cells in a range that contain certain text characters

My question simply: Is it possible to generate the data in A3:A5 using a formula? A B C D E F 1 PASSED/TOTAL | EXAMS ATTEMPTED 2 ------------...
2
votes
1answer
3k views

How to send cell as argument for function?

I am trying to write a custom function for Google spreadsheets that checks the formatting of a given cell and acts accordingly. I know that I can give a string as argument and get it to work but then ...
2
votes
1answer
1k 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 ...
1
vote
1answer
247 views

Pull data from one sheet (has a locked cell) to another sheet (has a locked cell) every day automatically?

I have a dynamic Google spreadsheet to which I add one more sheet every day. I want to take the data from sheet1 cell D96 (a protect cell) to sheet2 cell D3 (a protect cell) every day. I found a ...
2
votes
1answer
197 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 this?...
1
vote
0answers
2k views

Google apps script for text documents: regexp replace to footnote / link

Using Google apps script, in a google text document (i.e. just a regular documenet), I would like to convert the following string { some text | footnote | link } to some text with a footnote ...
1
vote
1answer
2k views

How do I use a variable to reference another variable in Google Apps Script?

I'm trying to create a loop that sets values of a set of variables, and I want to use the incremented loop variable to define which variable I'm setting the value of, like this: var box1, box2, box3, ...
2
votes
1answer
2k views

Create help pop-up in Google Document using Google Apps Script

I am trying to create a Google Document that has complex questions we ask clients. I want to add a link at the end of each question that will launch a pop-up for additional information. I currently ...
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
7k views

How can I solve error “Exceeded maximum execution time” in Google Sheets?

I found a script which I really need (link): function getTotalSum(cell) { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); var sum = 0; for (var i = 0; i < sheets.length ...
3
votes
2answers
1k views

Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

I am using the New Google Sheets, and I have created a "Master Document" that contains a multitude of sheets, with each expressing different kinds of data. I want a "Last modified" time stamp to ...
2
votes
1answer
207 views

Google Apps Script with forms to check if a doc url is 'shared'

Does anyone know "IF" it is possible to use Google Apps Script with a Google form to auto check "IF" a document URL pasted into the form is a "shared" document? I'm a teacher and I use Google forms ...
2
votes
4answers
2k views

Return name of range as string in Google Sheets

I have a range named apple in Google Sheets. 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 of a new sheet....
1
vote
0answers
319 views

How to make script slow down in GoogleSheets Script Editor?

I have a script thats attached below, and the problem I'm facing is that after it has keyed in "ticker" to Raw Data in b1, my entire sheet is never done loading and thus when its time to check if it's ...
8
votes
1answer
9k 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 ?
2
votes
1answer
2k views

Changing cell color in a range

i have 4 columns of values in each cell, the cells are different colors : Cell A1 = 12 (color - Green) Cell B1 = 10 (color - Pink) Cell C1 = 11 (color - Blue) Cell D1 = 13 (color - Red) Cell ...
2
votes
2answers
478 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. Instead,...
1
vote
1answer
2k views

Button in column C in each row

I have a spreadsheet with three columns and lots of rows. Column A has a numerical ID Column B is empty Column C is empty I want to have a button in each row in column C. When pressed, it should ...
2
votes
2answers
330 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 ...
5
votes
2answers
2k views

Can a Google Spreadsheet be recalculated if it's not manually opened?

We have some Google Spreadsheets that use the ImportHtml() to pull in some real messy tables. Then on on an alternate page we access the above data and pull it into a nice cleanly formatted table via ...
3
votes
1answer
10k views

Does IMPORTDATA() refresh when an Apps Script runs even if the sheet isn't open?

We have a Google Spreadsheet that pulls data from an external source via an IMPORTDATA() function and then a Google Apps Script that is on a timed trigger that performs other things with the imported ...
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
1k 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
21k 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
118 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
8k 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
556 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
478 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
10k 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
120 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
27k 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
393 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
5k 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
1k 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
8k 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 21 22 23 24 25 27