Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1
vote
1answer
21 views
How to get and use a value of a cell in the PDF file name exporting using that script
How can I get and use a value of a cell to the PDF file name, when exporting/saving using that script. I would like to use a string written in a cell, I mean, put that information in a variable and ...
1
vote
1answer
14 views
Convert a text of a specific cell or exclude one cell from all text convertion to UPPERCASE
How to adapt this function to exclude a specific cell or how to apply just to one cell?
function onEdit(e) {
if (typeof e.value != 'object') {
e.range.setValue(e.value.toUpperCase());
}
}
0
votes
0answers
13 views
Failed to send email: no recipient
I have a google apps script developed for when a user submits a form. Its an app script from the spreadsheet attached to form responses.
In the script I send back an acknowledgement email.
Just ...
0
votes
1answer
22 views
How do you get current date to be added to a cell when a check box is checked in another cell
I want to get the current date added into a cell when a tick box is checked in another cell.
I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
1
vote
1answer
20 views
Copy Sheet To New Spreadsheet
I want to copy one sheet in the Spreadsheet file into a new Spreadsheet file.
The sheet that I want to copy contains formulas, I want to just copy the value to a new Spreadsheet file.
I have made the ...
0
votes
1answer
17 views
Create 'bookmarks' in google sheets [on hold]
I'm pushing the edge of google sheets, doing with a spreadsheet what ought to be done in a database.
As an example: Cust_Orders is a sheet broken into blocks of 15 rows. Each block is one order ...
0
votes
1answer
18 views
Sending pdf with Google sheet email
Trying to send a pdf attachment to an email using Google sheets. I have checked and tried to use the replies in previous responses but my code fails after sending the first email.
This is the code I ...
0
votes
0answers
22 views
Google Sheets Script - check if value exists before copy-paste
Can someone help please as I am new to Google Scripts.
I have this simple code that copies values from one sheet to another
function copyrevision() {
var ss = SpreadsheetApp.getActiveSpreadsheet ()...
1
vote
1answer
15 views
Google Apps Script, MimeType.MICROSOFT_WORD will list only .docx
I am using a Google Apps Script to list Word files in a folder, however this will work only for .docx files, not .doc.
Any idea?
var folderID = "ID#"
var sourceFolder = DriveApp.getFolderById(...
0
votes
1answer
25 views
Get the name and link file based on Folder ID
I created a script to get the name and link file based on Folder ID. The file name and link are displayed on sheet 'Get_Link'.
File spreadsheet
The script that I wrote like this:
function ...
0
votes
1answer
18 views
Running script to replace “null” with nothing in Google Sheets
I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
0
votes
1answer
19 views
How to trigger a script in Google sheets when a value changes
I try to fire a script when a value in a certain cell or range of cells changes.
(it's not changed by the user, so I can't use onEdit)
I've tried to use onChange, but this doesn't fire my script at ...
0
votes
0answers
17 views
Script to copy a sheet and create a new sheet from a form submission [on hold]
The goal is to have the process go -
Form Submission
Data goes in to a "main data" Google sheet (This is done automatically)
(This is where I need help creating the script)
A copy is made of an ...
1
vote
2answers
67 views
Send a google form result to an email
I need help with the google form.
The problem is this: I want to receive an email with its results for every user who completes the form.
For example, if you had a form that required email, name and ...
0
votes
2answers
16 views
Google Sheets API call creates scientific numbers that I cannot then run calculations on
I am using API in Google Sheets to call prices of cryptocurrencies vrs Bitcoin
from URL: https://api.coinpaprika.com/v1/tickers?quotes=BTC
In some cases I am getting scientific notation instead of ...
0
votes
0answers
19 views
Two timestamps referencing different columns in one google sheet? [duplicate]
I'd like to have a timestamp occur in column 6 when an edit is made to column 1. But another timestamp to occur in column 6 when an edit is made to column 8. I would be open to having the 2nd ...
0
votes
0answers
7 views
Google Apps script to read through folder of slide presentations, compare to template, and import updated slides into a presentation
I teach high school using Google Classroom. I have students used Google Slides to keep track of their projects. I create one template Slides Presentation and GC automatically creates an identical ...
0
votes
0answers
26 views
Automatically populate cells but still be able to type in those cells
I would like to automatically put the word "Artwork" in a cell in column M when another cell on the row returns TRUE, however i also would like to be able to type values into column M if the other ...
1
vote
1answer
7 views
Bulk permissioning of Team Drives
I've been looking through the documentation but am coming up empty handed.
How can I assign permissions in bulk across multiple team drives? For instance, a new hire needs to be given Commenter ...
0
votes
1answer
28 views
How to automatically add new row in Google Spreadsheets
I want to automatically add row in Google Spreadsheets when I type information in row 5 by Google script.
]1
0
votes
0answers
11 views
Missing Google Apps Script trigger
Receiving the below email notification from google apps script for a trigger I used recently.
I'm done with the trigger now and want to stop it from firing.
When I open my scripts dashboard to ...
1
vote
1answer
31 views
Google Sheets count columns in a merge
Is there a formula function in Google Sheets to count the number of columns in a merge? I could assign a named range to the merge to make it easier....
For instance: see the highlighted merged cells ...
0
votes
1answer
31 views
Re-using a google form sent data, avoiding entering all data
I am building a Google form to manage summer camp subscriptions. Each request must enclose parents and child data.
The problem is, if a family wants to subscribe more than one child, the process must ...
3
votes
1answer
28 views
Can I limit how frequently an 'on open' script runs?
Basically I have a bunch of functions that run on Open, but if possible I'd like to limit them to only run once per 60 minute period. Just so that if a user accidentally refreshes or closes the sheet, ...
0
votes
0answers
10 views
Is there a way to check a user's permission in Google Sheets?
I am trying to find information on this but haven't really been able to find an answer.
Is there a way in Google Sheets to, through scripting, check a user's permissions for the sheet?
My ...
0
votes
2answers
31 views
Custom SHEETNAME function not working in Arrayformula
I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information ...
1
vote
1answer
43 views
Inserting date automatically when a tick box is ticked
I have a simple sheet that has two columns with tick boxes in them (columns 5 and 8) The goal is when the box is ticked the date and time are inserted in the column next to it. I have got it working ...
0
votes
1answer
18 views
Adding numbers to a cells formula?
Let's say A1= X+Y
Now I'd like to add +Z with a script so I end up with A1= x+y+z. I know how to copy it, or overwrite it, but not sure exactly how I would add to it with a changing formula in A1.
...
0
votes
1answer
39 views
ImportXML not stable in Google Sheets, how can I improve that?
I have ImportXML in my spreadsheet.
I am tracking the Alexa ranking of a list of websites.
Here is the formula:
=HYPERLINK(CONCAT("www.alexa.com/siteinfo/",A4), value(ImportXML(CONCATENATE("http://...
0
votes
1answer
11 views
Getting sheet to not update all at once
I'm making a sheet that imports a large amount of data from a site. I want to do this slowly so as not to clog up the site.
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ...
0
votes
1answer
51 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('...
1
vote
2answers
26 views
How to automatically scroll to bottom of Google doc when it is opened?
I'm looking for a way to automatically scroll to the bottom of a Google doc every time I open it. Something functionally equivalent to this answer would be great.
0
votes
0answers
13 views
Sort from a custom menu button in Google Sheets
I would like to sort my sheet from a custom menu item without having to highlight the entire range then choose the column to sort by, adding the sort buttons makes a wide spreadsheet even wider - i ...
0
votes
1answer
46 views
Equivalent of IMPORTRANGE with script in Google Sheets [closed]
Since the Importrange() is not working as expected, do you know how I could express e.g the following function with a script?
={IMPORTRANGE("SheetKey","SheetName1!A:J") ;
IMPORTRANGE("SheetKey","...
0
votes
0answers
10 views
Changed default cell in Sheets to today's date - How do I default it so it displays this cell at startup?
I used this script to default the cell to today's date - How to make Google Sheets jump to today's row when opened?
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet ...
0
votes
1answer
23 views
Multi-var definition statement limit in Apps Script?
My personal javascript variable definition style is a single var statement followed by one or more definitions. It's an unimportant choice, but one that some find benefits in.
Carrying this over to ...
0
votes
3answers
29 views
IF and THEN statements on spreadsheets using color
I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements:
=IF(...
0
votes
1answer
54 views
“missing ; before statement” error
I'm trying to get the number which is basically 116 into a cell on Google sheets with the following code, however I get the missing ; before statement error:
var copper = UrlFetchApp.fetch("https://...
2
votes
3answers
47 views
Is it possible to have Named Constants in Google Sheets
Excel has a notion of defined names which can be used to define constants/literals. Does GSheets allow this?
You can see this in action in the following Excel screenshot:
.
Cell B2 has the formula ...
0
votes
1answer
10 views
How to replace reference cell and have it affect the main cell if it referencing?
I asked a similar question on the Google Support Forum. I was wondering how I could reference a cell with any sort of value in it to another sheet (inside the same Google Sheets document) and then ...
0
votes
0answers
28 views
Google script to determine if value is date for email sending
Dates in Google script are driving me CRAZY!
I have looked all over the internet for something that will allow me to use dates in my google sheet cells from entries that may be dates or text (For ...
0
votes
0answers
10 views
Copy data generated by QUERY
I want to copy the values generated by a complex scripted query to another sheet, for users to work on without deleting the original query.
The query generates a variable range of data, depending on ...
0
votes
0answers
16 views
How do I circumvent protected sheets to expand or collapse groups and rows in Google Sheets?
A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns ...
0
votes
1answer
12 views
Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)
I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
0
votes
1answer
35 views
How to navigate to a certain sheet in Google Sheets?
In my previous related question, I asked how to navigate to a certain column, however, now I also want to navigate to a certain sheet.
So I need a link from a cell, and by clicking it, I want to jump ...
0
votes
0answers
15 views
PLease check my delete Function
I modified some google apps script but cant get right the delete (Hapus) function...please see the code below
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/...
0
votes
1answer
51 views
Google Spreadsheets timestamp
I'm trying to have a timestamp automatically appear in column L (12) in a Google Spreadsheet if a tick box is ticked on the same row (in column A). The app script code that I modified from this source ...
0
votes
0answers
29 views
how can i find and append a cell with script editor in google sheets
I have a script that will find the value in a lookup from column b and column a what I am trying to do is find the row and append the data in column n of that row
is there a way of doing this, the ...
-1
votes
1answer
76 views
How to run multiple OnEdit scripts in the same sheet?
I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell.
But the problem is that I ...
0
votes
0answers
26 views
One script for multiple tabs in one Google sheet
Can you please help me with this script? I've tried to edit it to apply to multiple tabs the same as the original question. But I think something's amiss. :( Here's the original code and I want this ...
