Questions tagged [google-apps-script]

Google Apps Script is a JavaScript cloud scripting language for automating tasks across Google products and third party services.

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

New Spreadsheet Row to YouTube Playlist

I have the following code below that takes a new row added to my Google Spreadsheet (has a header row) and extracts the video ID. If the ID does not exist in the playlist, it adds the video to the ...
2
votes
1answer
88 views

JavaScript: proper user of SJCL encryption in Google Apps Script

I'm using the minified version of SJCL to encrypt the backup files requested by the users. It is in a Google Apps Script add-on for Google Sheets. I'm mostly concerned with correct use, exception ...
3
votes
1answer
57 views

Faster way to copy rows of data to different sheets by column value

I'm trying to find a faster way to copy specific rows of a sheet to different sheets. Iterating through them as done in code below takes too much time it leads to timeout. Some information about ...
2
votes
0answers
28 views

Google Script is slow. Using arrays. Anything else I'm missing?

I'm trying to add students to Google Classrooms ('Courses') from a Google Sheet with code below (function AddStudents). It's very slow and, if I do too many, it exceeds allowed execution time. Is ...
3
votes
0answers
30 views

Rearranging data from one table to another in spreadsheets using apps script

I need some help with making my code run faster. It currently works by going through all cells in a 201x151 table. If the value in a cell is not equal to zero, then it will return the row number, the ...
1
vote
0answers
24 views

Custom Google Data Studio connector

I am using Google BigQuery API V2 for a custom Google Data Studio connector. The BigQuery.Jobs.query() calls take roughly 50 seconds which is too long, so I am ...
1
vote
0answers
52 views

Saving an Array to PropertiesService in Google Apps Script

I have the same array set up as a variable inside of each function - I want to make this easier to manage. The array contains multiple strings of values. I’ve attached code below that I've been ...
1
vote
1answer
63 views

Nesting for loops to find matches in different tables

I'm not sure if I'm even approaching this correctly (still very much learning), but this is what I'm attempting to accomplish: My script gets an array from a column (tab names of the sheet) and then ...
4
votes
1answer
41 views

Google apps script Exceeded maximum execution time issue or 29k rows

I built some code to update dates from a Dump file. I am commonly running into an "Exceeded maximum execution time" message. Otherwise the code works fine. It imports & updates statuses for 16.5 ...
1
vote
0answers
32 views

Faster Google Spreadsheet access and direct use of Named Ranges for code readability

This object structure, that I think might be useful for a number of projects, attempts to: learn the use objects (classes are not available in Google Apps Script) and inheritance minimize calls to ...
2
votes
0answers
21 views

Google Script - Calendar events not owned by me

I'm trying to write a script to delete event invites from my personal google calendar. I get invited to multiple events everyday such as "Click here to win an iPhone" etc. I have written a google ...
3
votes
1answer
75 views

Bracketing multiple onEdit functions [closed]

I have multiple onEdit app script functions in my Google Spreadsheet which work individually but I cannot work out where to put the brackets to nest them. This is my code which is a little messy and ...
6
votes
0answers
58 views

HR Data Entry Form allows addition of new employees and update of existing employees

Business Process: Data entry form allows HR employees to input data for new employees, update data for existing employees, or terminate employees. Three data storage sheets exist for existing ...
-4
votes
1answer
50 views

Minimize this IF statement [closed]

I was going to post this in code golf, but I think it may be better suited here. I'm not really interested in scoring it beyond accepting the best answer. I'm wanting to minimize this series of <...
3
votes
0answers
235 views

Eliminate all empty cells via vertical merging: Google Sheets

I am working with a spreadsheet containing a bunch of 'sections', e.g where the 'URL' column is set to 'google.com/search' in A1, and left empty while a bunch of things related to that URL are done in ...
1
vote
0answers
27 views

Adjust entries in Google Sheet for short orders

I am relatively new to scripting. My programming experience stems from a QBASIC course when I was 11, and teaching myself HTML when I was a teenager (a while ago). This script resides on what is ...
3
votes
0answers
23 views

Google Scripts (Add, Remove & Moving Record) stop timeout

What is it? The google sheet that the macros are on acts as a sort of "back-end" for the records of a behaviour system at a school. I build multiple google sheets that link to this one that pull the ...
3
votes
2answers
288 views

Function to format numbers with thousand separators and decimals

I've wroted this function because I was getting trouble using toLocaleString() in google-apps-script, and after some refactorings I got to a working code: ...
2
votes
1answer
209 views

Google Apps Script to create an invoice

I am using Google Execution API to create an invoice and it's a small part of a bigger system. However, my boss is pretty displeased at the total execution time before an invoice is generated. It ...
1
vote
0answers
22 views

Google App Script sheet that moves row to correct sheet

I've got this code to loop through each cell in Column A and check the cell value. If the value matches the if criteria it moves the entire row to the correct Sheet and then replaces the Column A cell ...
0
votes
1answer
37 views

Google Apps Script shifting columns containing specified substring - using array functions

The link is to the example sheet of what I have done This code solves the problem of shifting large amounts of specific data in a google spreadsheet. The data is shifted to the right by one column, ...
3
votes
1answer
438 views

Modal displayed in Google Spreadsheet from Gscript

First: I'm not really a programmer. Thus, this application may be started in a bad way, but here is my question. It's not a specific one because I have no real clue about how to improve my code. Let ...
1
vote
0answers
56 views

JavaScript loop to copy JDBC query results to a 2D array

I have a JS running in Google App Script, but for me, it's like an overall example/problem I ran into more often. The script fetches via JDBC the results of a MySQL query. The result has about 60.000 ...
1
vote
1answer
138 views

Hide rows where column F is not empty

I'm not a scripting expert by any means, but I put together some code that I needed for Google Sheets. The good news is that it actually works! However, it's noticeably slow to run (about 15 ...
2
votes
1answer
95 views

Returns Google Sheet cells from a named range

I've got this function that's called in several places in some of my Google Spreadsheets. It accepts a Range object, a string to ...
1
vote
1answer
21 views

Copying contents of cells in a vehicle-tracking Google Sheet

This is really my first shot at scripting anything. What I have below works, it does exactly what I need it to, but this is only a quarter of the total function. Clearly, I need to cut down the length ...
6
votes
1answer
746 views

Bottlenecks in asynchronous JavaScript code for Google Map

I'm working on a pothole map web app in Google Apps Script. The map gets its data from a Google spreadsheet (from manual surveying), which is then transformed via hits to the Geolocation and Google ...
1
vote
0answers
195 views

Using a Config class to manage Google Apps Script's PropertiesService

I created a Config class to manage the PropertiesService in Google Apps script. Right now it only uses the DocumentProperties, but it can easily be expanded to support the User and Script properties. ...
2
votes
1answer
59 views

Extract a number from a consecutive range in a Google Sheet

I'm new in coding and I use google script in my spreadsheet to add a list of consecutive numbers automatically by giving the first number to add and the last one (with an arrival date and an option "A"...
1
vote
0answers
21 views

Compare manufacturers' production report with internal inventory data file

I wrote the below javascript at the company that I work for. Basically what it does is it compares production report given by manufacturers and internal inventory datafile to perform various tasks. I ...
1
vote
0answers
48 views

Remove file and folders sharing down to the tree

I have a program solution for situation described at https://webapps.stackexchange.com/q/108660/86039 This code removes editors and viewers from all files and folders from desired folder in Google ...
4
votes
0answers
655 views

Generating Bitly links in a Google Sheet

I have this set of functions in my Google Sheet to create a Bitly bitlink whenever data in a row is changed. While this does work, it seems really slow to me. Looking at the Execution transcript, it ...
2
votes
0answers
44 views

Identifying spreadsheet clashes in Google Apps script

This is an attempt at porting some of my boss's code from VBA to Google Apps. Are there more elegant ways of doing this? I know that later versions of ECMAScript can do this more nicely but Google ...
1
vote
1answer
689 views

Lock-range code for Google Scripts/Sheets

This code locks a range and adds a timestamp. More details below. I'd like to learn how to make this code more efficient (minimize code/variables and reduce redundancy). Any thoughts on the areas I ...
0
votes
1answer
73 views

Setting values in a spreadsheet

I have put together the following script to automate a process I have to perform at my work. The script solves some basic math functions and then inputs text values in certain cells. I have several of ...
3
votes
1answer
443 views

Spreadsheet generated through Google Drive file iteration

I put together a script which goes through all of the files in Google Drive, and writes a spreadsheet with all of the files with their name, containing folder, URL, and date last updated. ...
1
vote
1answer
331 views

Dependent dropdown

I've created this code (used the validation creation section from here) which creates dependent dropdown lists based on data which is in a pivot table style arrangement. The idea is that picking a ...
4
votes
1answer
222 views

Exporting unread mail details for further processing

As the result of some expert procrastination I have over 4000 unread mail in my gmail box. While that does not hold a candle to what some people likely have I wanted to try and do something about it. ...
4
votes
0answers
89 views

Protection for cells in Google Sheets

Is there some way to speed this up, since it keeps timing out while running? The spreadsheet is shared with a Google Group with editing permissions. I've loaded what I can into memory but each ...
1
vote
0answers
99 views

Iterative Calculation

This Google Sheets script performs an iterative (circular) calculation to find values based on a gross percentage of project cost. Range names are used so that users can add/delete rows/columns and ...
1
vote
0answers
79 views

Join several columns with a single formula returning an array in Google Sheets

The title was taken from Join several columns with a single formula returning an array a Q&A hosted at http://webapps.stackexchange.com. The content is based on the original version of the answer ...
8
votes
2answers
3k views

Creates a menu and sidebar in Google Spreadsheets

In an effort to make my code more testable, I'm trying to make use of constructors and prototypes. Here is a working Google Apps Script (GAS) for creating a new submenu under the Addon main menu in a ...
1
vote
1answer
503 views

Google App Script to calculate score card for a certain person, create PDF file, store to drive and email to person

I'd appreciate if someone could cast an eye over my dodgey coding and give me some feedback. The use / purpose of the sheet/script is to quality grade calls, emails and chats with customers. A form ...
8
votes
1answer
108 views

Google Sheet with a column of names merged with Google Doc

I wrote my first script for google apps, but I am just learning how this all works. The script works fine, but I am sure there are things that are not done properly. Any advice is appreciated. The ...
2
votes
1answer
82 views

Google Drive folders properties caching to file

I have the recursive function below to cache all the folders of my company Drive into a file. It works like a charm but takes too long, specifically >30 secs to read and save the 300 folders. What I ...
6
votes
1answer
4k views

A1Notation conversion to row & column index

This set of Google Apps Script helper functions are published in this gist, along with a bunch of other utilities. Background info is in this SO question. Since no Google services are used, this is ...
2
votes
1answer
92 views

Combining 3 forms into 1, then writting data to 3 different sheets, with different layouts

Where I work, we have 3 forms for leavers: HR, IT and rota. Lots of questions are duplicated across all 3 forms. So I thought, why not fill ONE form out, remove all duplicate questions, and simply ...
4
votes
2answers
1k views

Parse and format data on Google Sheets

This is a script that I use on a Google sheet for parsing data that has been pasted from various other software and getting it into the correct format and columns on another sheet. Data is pasted into ...
0
votes
1answer
56 views

Automating project budgets [closed]

I use this code in a GAS (Google Apps Script) to automate my project budgets. Since every project has its own spreadsheet but all functions are in the same script file, being called as a library, I ...
5
votes
1answer
409 views

Transferring file from Google to Parse.com

I have created a system where user selects files from their Google Drive to be uploaded into Parse. I have done so separately though, where I have one code that allows user to select an item from ...