All Questions
Tagged with javascript google-apps-script
52 questions
1
vote
0
answers
41
views
How to set V Axis formatting while updating charts via Google Script
I have a Google script to automatically adjust the 'Max' Value for the Vertical Axis based on the max value from a column in the source data. This part is working, but, the number format keeps ...
- 11
1
vote
2
answers
166
views
Replace text in document using A1 notation as variable names in order to retrieve values stored in those cell references
I have a Google Doc that contains placeholders based on A1 notation like %A1%, %B3%, %A3%, and so on.
In a Google Sheet I have values stored in the cell references that match the variable names.
I ...
- 113
1
vote
2
answers
416
views
Printing the date of a cell in the Google Apps Script console
I wrote in cell D1 the function "=today()", and in google apps script I'm trying to print it in this way:
function myFunction() {
const ss = SpreadsheetApp.getActive();
const ws = ss....
-1
votes
2
answers
800
views
While Loop does not stop when it should
I'm new to Google Apps Script. I am trying to write a while loop for a spreadsheet, but the loop isn't stopping when it should.
The spreadsheet contains a formula that returns randomized Sudoku ...
- 1
1
vote
1
answer
303
views
How do you work with the Color Class array?
In Google Sheets using Apps Script, I want to read in the font color in many cells, change some values (change colors) in the array, and write them back to the cells.
I have previously used ...
- 11
0
votes
1
answer
43
views
Pulling all data from a column
tl:dr - I have a code that can work but want to make it better by pulling everything from column E into 1 message (including header)
I am trying to get the output of a singular column to post to ...
1
vote
1
answer
26
views
How do I add multiple cellvalue in a IF function?
I'm trying to add more values with "Select a name" to the script so when any of these values pop up it'll clearcontent
function onEdit(e) {
let range=e.range;
let activeRow = range....
- 15
0
votes
1
answer
154
views
How to add date stamp when criteria met in google spreadsheet?
This is the current script. When a cell in column 26 is edited, a datestamp is added to the cell beside it in column 27. Now I would like to add additional criteria. Example: When col 26 value is &...
1
vote
2
answers
68
views
Validate values before writing
In my Sheets projects I often have to write values at the end of the sheet programmatically. I use data validations, some of which don't accept invalid values. My problem is that if a write operation ...
- 243
0
votes
1
answer
54
views
Help adding checkbox to google sheets
I am a novice to google app scripts. However I need to copy selected rows (with a checkbox) to a second sheet. I am using this code below. However it copies the entire source sheet. I only need rows ...
- 11
0
votes
0
answers
12
views
Dynamically add answers [duplicate]
I want to ask questions as the following with Google Forms:
What lessons have you already completed?
(here I give a list of lesson options. Only one option can be selected)
Lesson A
Lesson B
Lesson C
...
- 1
0
votes
1
answer
314
views
App Script dynamic data validation based on list
I have a list of user and data validation mapping in column A and B
User Data Validation
Customer Jim
Customer Pam
Customer Tim
Employee Beth
Employee ...
- 1
0
votes
1
answer
7k
views
Type error: cannot read property getid of null in google script
When I run this script it show me this error. I can't understand what I do. Its function is to transfer results from html form to Google Sheet. Can anyone solve this problem?
var sheetName = 'Sheet1'
...
0
votes
1
answer
152
views
Combining Separate Scripts into One
I've been trying to figure out a solution to this for a few weeks & I think I'm at the home stretch, but having some trouble piecing it together. With great help, I have a script (below) that ...
- 120
0
votes
2
answers
260
views
Custom Function to Cross-Joining
I am looking for a flexible custom function to be able to cross-join (cartesian product) 2 columns of varying lengths in Google Sheets.
My research so far has led me to ask because:
using a ...
- 29
1
vote
1
answer
5k
views
Hide/Unhide rows based on values in particular cells
I am trying to Hide/Unhide rows based on values in particular cells, have the following code which works in isolation but not together. Below only makes B121 be active while B55 stops working. If i ...
- 11
1
vote
1
answer
270
views
Duplicate row values to next row, based off specific column
I'm trying to set up a button that will copy information from the last row and paste it into the row right underneath (based on where column D last has a value). Almost like a "duplicate" ...
- 120
1
vote
1
answer
406
views
Automatically Merge Group of Cells Below a Text Value
I'm building out a scheduling assistant; the look is great, but it is very tedious to Merge & highlight each schedule block individually, so naturally I turned to Apps Script.
I'm entering ...
- 120
-2
votes
2
answers
79
views
Allow users to make yes/no decisions using checkboxes
I'm looking for a way my users can interact with my scripts. I'd like to use checkboxes to work like a "yes/no" command, where the script does "A" if the checkbox is ticked, and &...
0
votes
1
answer
467
views
How to Jump to Column Based off of Cell Value
My expected result is very simple - I have used data validation to place a list of column titles in a cell. When a user selects a column title and clicks the button, I want them to be taken to the ...
- 120
1
vote
1
answer
2k
views
How to set all checkboxes to FALSE in a specific column without losing formulas
I'm looking to reset all TRUE checkboxes to FALSE using a script. However, this column also has formulas I want to keep in certain cells (they are NOT checkboxes).
Is it possible to alter so that ...
- 120
1
vote
1
answer
307
views
How to Hide/Show Different Rows based on MULTIPLE cell values
I've read through countless different posts that are similar to this topic, but have yet to find something that really solves the exact issue I'm looking to fix.
My script currently reads the value ...
- 120
1
vote
0
answers
311
views
Create a function to upload and import spreadsheet file to google sheets?
I have successfully created a button to execute a function that will actually grab the file off on my computer. But will not to completely import the data of that file into a new sheet tab.
My ...
- 149
-1
votes
1
answer
2k
views
Custom buttons to trigger the Import or Print in Google Sheets
I am using an HTML function (HtmlService.createHtmlOutput) to show my custom buttons in a popup or sidebar within my google sheets.
But I am simply trying to make custom buttons to invoke the already ...
- 149
2
votes
0
answers
488
views
How to Group Set of Rows & Skip One Every Time
I'm currently working in a Google Sheet that has about 12,000 rows. Because of the layout, I want to group every 5 rows, then skip one, then group the next 5.
Right now, I have as script that is ...
- 120
-1
votes
1
answer
67
views
Access values from one Google Sheet in another
I need some values from Spreadsheet 2 (in Tab 2) in Spreadsheet 1 (in Tab 1).
I don’t know the URL for each Spreadsheet; they are unknown.
How can I switch between these Tabs and get the values from ...
- 1
1
vote
1
answer
61
views
Friendly Team vs. Enemy attack math and complicated functions in Google Sheets
How would I go about writing a function in Google Sheets?
If Friendly Team Hero Power P2,P4,P6,p8 etc. is greater then Enemy Team Power "T2:T15" Else Don't match it and instead look for titan, Same ...
- 11
1
vote
3
answers
8k
views
Working With Durations In Google Apps Script
I'm trying to write a function in Google Apps Script to manipulate durations calculated in Google Sheets. The cells holding the durations look like:
18:40:00
26:42:01
When I pass a duration into my ...
- 531
0
votes
1
answer
280
views
Google Apps Script Javascript compatibility error
I have successfully created some code to extract data from a 2D array using A1 notation. Handy to avoid multiple calls to GAS services whit ordinary getRange calls.
While the code perfectly works in ...
- 589
1
vote
0
answers
67
views
Google sheets script add data in next column
I'm trying to modify a script to create a PageSpeed report that pulls in certain KPI's from Google Pagespeed API. Once the data is received, i want it to place those KPI's next to the URL and so forth....
0
votes
1
answer
2k
views
How to get correct TimeZone in googlesheet using script? [closed]
I have wrote following script in my google sheet hoping to convert a timestamp in GMT to a customized timezone.
function myFunctions(datetimeString,timeZone,format) {
var moment = new Date(...
- 105
1
vote
3
answers
3k
views
Using a script to only take only take cells with values in google sheets
tl;dr how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?
Background
I have an array in Google Sheets with date-ordered data (note ...
- 11
0
votes
1
answer
544
views
How to timestamp on new added cells in LIVE google spreadsheet?
The code below insert timestamp in google sheet by editing another column.
However, there an issue which I have no idea how to solve. When I add new row manually, it successfully adds timestamp, BUT ...
- 105
1
vote
1
answer
2k
views
How do I make this Google Sheets Script faster
function getCellValue(){
var x=2;
var y=2;
var targetSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var targetSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('...
- 11
2
votes
0
answers
644
views
How to pass a parameter allow= “geolocation” when embedding Google Apps Script in a Google site?
The target is to embed a Google Apps Script gadget into my Google site. The function of this script is to show a google map, determin the location of the client and center the map to the found ...
-1
votes
1
answer
567
views
Google Script Editor apply to all sheets except 1st, 2nd and 3rd sheet [duplicate]
Currently this code is only applied to the sheet called "Name" I wanted applied to all except for 3 sheets (Template,Summary and Count):
function EqualValue() {
var ss = SpreadsheetApp....
0
votes
2
answers
1k
views
The script does not have permission to get the active user's identity
Anyone know why this caused The script does not have permission to get the active user's identity.?
I don't have any functions that asks for the user identity. So I'm a little confused.
function ...
- 159
1
vote
0
answers
30
views
How to Get Data from Multiple Cells and Combine into a Comment in another cell
I have a spreadsheet of orders (rows) that has room for 70 products (columns). I would like for a script to find every cell that has a value (0's are blank) and then write that value (this is the qty ...
- 11
0
votes
2
answers
2k
views
Jump to next empty cell in specific column
I'm writing a script where I need to take in a number attached to a name and team, process it as # of Rolls essentially, and then paste the results one by one into a running log. Each team will have ...
- 1
1
vote
1
answer
5k
views
How to make Google Sheets jump to closest date to today when opened?
I have a Google sheet containing a list of events, with event dates listed in one column. I'd like to have my Google Sheets spreadsheet jump to the closest date to today's date on opening (i.e., ...
- 31
-1
votes
1
answer
7k
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
0
votes
0
answers
59
views
Is there a way to just pull out all of a certain index without going through the results and rebuilding a 2nd temp array?
I've got multiple sheets with anywhere from 500 - 10,000 rows each that I need to find specific values in a given column. Ex. Sheet A, B, C have information about a bunch of products. I search for ...
- 107
2
votes
2
answers
44k
views
How to format the time in this Google Apps Script
I want to format a Google Sheet cell when I read it in my script but it comes out 25 minutes later.
The cell's time is 20:00:00 but the output is 20:25. I am using this
var formattedDate = Utilities....
- 21
-1
votes
1
answer
346
views
getEditResponseUrls - hyperlinks from second linked form not inserting
I am new to scripts, but I was able to find a one that uses getEditResponseUrls to insert the edit link from a form into a Google Sheet. I've now been asked to add a second sheet to my spreadsheet ...
4
votes
2
answers
17k
views
Loop through all sheets, except for specific sheet names
I found the following code and want to use it to loop through all the sheets expect a few (that I don't want it to go through).
My question is how can I edit this code in such a way that It does not ...
- 43
0
votes
2
answers
58
views
Issues with a counter
I'm trying to create a button in Google sheets which, when pressed, imports a sheet from a separate spreadsheet into the current one and gives it a name of 'Miscellaneous' followed by the number 1 ...
- 17
-1
votes
1
answer
271
views
Awesome table function
Is there an option in awesome-table.com with the edit links such that the result of the form will be to create a new line in the spreadsheet and only alter the values instead of editing the existing?
...
-1
votes
2
answers
1k
views
Copying JS array into Google Spreasheet introduces NOT_FOUND cells
I want to create a historic sheet in Google Spreadsheets, where I take the stock data from one sheet and I fill the historic sheet using a specific date in the script.
This is how my sheet looks like:...
- 267
1
vote
1
answer
4k
views
Google Sheets casting cell value from Object to Number
One cell in a Google Sheets document holds a value given by a SUM of cells formatted as "elapsed hours":"elapsed minutes". Attempting to make calculations in a custom script on this cell will throw ...
- 589
1
vote
1
answer
2k
views
Populate Form Element Values based on existing Rows in Google Sheets
I am trying to convert an Excel UserForm into Google Sheets where the user can create, update or delete entries in existing sheet based on the input form values. I started with selecting a value and ...
- 13