Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
0
votes
1answer
11 views
Email non-empty cells in tabular format
I'm trying to trigger an email to send all data on a sheet in columns B:F. I've found this tutorial quite helpful but can't quite get my head round it.
/**
* Sends emails with data from the current ...
0
votes
2answers
11 views
Cannot Find Function 'getRange' in Google Script
I'm working on setting up a series of scripts for a google sheet where I want to be able to auto sort based off of when something gets entered into the third column, sorting first by the first column (...
0
votes
0answers
3 views
Apps Script to skip files that have no blank rows
I created a script to delete blank rows from many spreadsheet files.
the script I created like this:
function delRow(id, name, startRow, blankRow){
SpreadsheetApp.openById(id).getSheetByName(name)....
1
vote
1answer
12 views
ArrayFormula stops working after shuffling rows
I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets.
The problem I have is when I need to rearrange rows then what ...
0
votes
0answers
12 views
How to keep “HH:mm:ss” as “HH:mm:ss” when archiving to new sheet
I am trying to script a function that pastes values into a new google spread sheet, one of the rows is a time in CST and in the archive sheet gets pasted as "12/30/1899" with the regular time. e.g 11:...
0
votes
0answers
15 views
Google Sheets - pull static value from cell that will change later
I have a sheet where I calculate the average of the daily value for the past 30 days with a rolling average. I'd like to be able to pull the daily values and store them in another sheet. I want to ...
1
vote
0answers
9 views
How can I add in a date macro to a Google doc?
Really grateful for this answer from Thomas Wiersema, but am keen to find out what the issues might be if the Utilities menu isn't showing up after you've followed his instructions via Google scripts.
...
0
votes
1answer
14 views
Displays data arrays with appendrow where other columns contain data
I wrote a script to display a list of names and folder IDs.
The script that I wrote like this:
function listFolders() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A2:B').clear();...
0
votes
0answers
12 views
Trying to figure out a Google Script
Looking for help on a script. To pull new rows from one sheet to update my report without duplicates or erasing manually made changes to current rows.
I was originally using =ARRAY_CONSTRAINT() at the ...
0
votes
1answer
8 views
Script to copy range transposed to another sheet
I need to copy a range from the sheet 'Source' to the sheet 'Database' but transposed. It should be the range B1:T9, not the whole sheet. I need to do it with a script as it is going to be a database ...
0
votes
1answer
13 views
Show each n-th column, hide others in Google Sheets
I have several big tables, and I know that each 4th column is interesting for me, i.e B,F,J,N, etc (The total number of columns is different in different documents)
Is there an easy way (some sort of ...
1
vote
1answer
11 views
How to go to the first blank cell directly in a column of Google Sheets?
I use column A mainly. A1 is the name of data type, and A2, A3, A4, ... are data. When I open this file, It shows the screen containing the first cell, A1. If I fill data from A2 to A250, then I ...
0
votes
2answers
16 views
inputBox in onEdit does not work for users
I have a strange problem...
Simple function like:
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getActiveSheet()
var ui = SpreadsheetApp.getUi()
var response = ...
1
vote
1answer
21 views
Copy Contents of Cell When Checkbox is Checked - Google Scripts
Preamble: never used javascript before, but trying to learn through google sheets since I work with tons of data.
Hello, I'm attempting to write a google script that will take the contents of a cell ...
0
votes
0answers
18 views
Hide rows in google sheets if cell contains text [closed]
What I am trying to do is if column P contains any type of text other then "None" or "Not Completed" then the row will hide using a google s
0
votes
1answer
5 views
Create named Versions of Google Sheets (or other documents) automatically on a schedule
I'm trying to create named versions of a file on a schedule, say once per week. Refer here for an example. The named version should be something like the file name + date so that it can be ...
0
votes
1answer
20 views
dynamic borders for columns
I have this script that sets borders for range if first cell is not empty
function borders() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetmon = ss.getActiveSheet()
var rows = ...
1
vote
1answer
27 views
How to do permutation of characters and print it horizontally along that row in Google Sheets?
I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are.
Is it possible? Because I searched it all over and only found vertical ...
-1
votes
1answer
7 views
How to display a text after a choice is selected in google form?
So I have my drop down connected to spreadsheet. There are two column which is ID and Name. In my google form, I want the user to select the ID but not the name. The name should be shown according to ...
0
votes
2answers
12 views
optimize onEdit managing set of many checkboxes
I have onEdit script that hides or shows rows by checking/unchecking checkboxes (yes/no values).
Looks like this
if(orpscscchbx.getValue() == 'no') {
var rowsHided = 0;
for (var i = 0; i ...
0
votes
0answers
8 views
Is there a way to link to highlighted sections in a Google Doc?
I have a large document that has multiple highlighted sections. The highlights are color-coded such that all blue highlights belong to one topic, while green highlights belong to another topic, etc. I ...
0
votes
2answers
30 views
Is there a way to protect/copy only some formatting in Google Sheets?
I have a spreadsheet that I use to track customer orders. It has the customer details on a row, their order number and a line of check boxes for the items that they want for that particular order. For ...
1
vote
1answer
19 views
Set link in email body
I have script that sends an email from google sheet and want to have link in the email's body
but code like
var html_link = "google.com";
var body = "<a href=" + html_link + ">Google</a>"...
0
votes
0answers
17 views
Set boders to a range if first cell in row is not empty
I need to set borders to a range in row if first cell in this row is not empty.
I have this code
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheetmon = ss....
0
votes
1answer
37 views
Why don't work function onEdit(e) when using Zapier
I created a chatbot that using Zapier (through Webhook and JSON) for sending to my Google Sheet a name and a phone of user and Apps Script must generate a coupon code automatically. I used function ...
-1
votes
0answers
31 views
Advanced sorting in Google Sheets
I have a random list of students in different rooms, different gender, and different categories. A dynamic solution would help since the list might grow and students might be added in new rooms or ...
0
votes
1answer
21 views
High performance Google Apps Script version? [closed]
I am planning a Web app tightly integrated with the Google platform. Calendar, Drive, Docs, you name it.
My only concern is GAS processing time, as it is well known that script execution is slow. ...
0
votes
1answer
18 views
Transfer a row to another sheet then clear the original row
I saw this problem and the answer was perfect except that I do not want to delete the row. I only need the cells cleared of existing values.
How to move a row into another sheet?
0
votes
0answers
16 views
Issue with SUMIFS pulling data from QUERY across multiple sheets
I would like to sum values from a QUERY generated list in the sheet I/S/N/W to the sheet BUDGET for determining a value based on the date in BUDGET!B8. The function in BUDGET!B8 is =MONTH(TODAY()).
...
0
votes
0answers
25 views
Loop through all sheets with macro
I have a macro that I want applied to all my sheets. I tried applying different codes I've found online but it only goes through the first sheet before stopping.
function LoopThrough() {
var ...
2
votes
2answers
30 views
How can I have onEdit trigger when specific cells are selected?
I can't seem to figure out how to get my script to trigger only on the edit of specific cells and on the current sheet. I've tried just about everything and it just refuses to run the script at all if ...
0
votes
1answer
16 views
Google Sheets moving to a new column if cell not blank
I have to scan barcodes of inventory for techs to install I wanted to find a way to skip to the next column after I scan a certain number of meters for example: John needs 90 units his name is on A1 I ...
0
votes
1answer
17 views
Why would I be getting the error: Too many simultaneous invocations on a simple once a day trigger?
I've searched around and still can't find anything directly related to the failure message I'm receiving. The script hasn't changed and it used to run just fine, but now in the past week or so it's ...
0
votes
1answer
23 views
Scitpt to duplicate template sheet into new workbook, then rename
I am looking for a script that will create a menu item that will allow me to copy a template sheet that I have created (multiple times) into a new workbook, and rename the copies at the same time.
...
0
votes
1answer
15 views
Function OnEdit run slow and timeout error
I have a short timestamp script for my google spreadsheet. But it run quite slow and make a lots
function onEdit(e) {
var TIMERECIEVE = [0,-29];
var DATETRECIEVE = [0,-28];
var DATEDONE = [0,-...
0
votes
0answers
15 views
Looking to open a timesheet in Google sheets and auto seek the current date [duplicate]
My employer has recently transitioned from excel to Google sheets as our timesheet platform, and we're finding that every time the doc is opened or recovered from suspension, it resumes at the top.
...
1
vote
1answer
47 views
Google Sheet Query smarter way to get multiple criteria
I use a spreadsheet where I want a list of items which contains multiple criteria.
(I need all cells in a range which contains the letter 'a - g'
The formula I use now is
=query(Planning!G5:G37; "...
1
vote
1answer
42 views
Auto update of cells with timestamp
I'm new to writing/using scripts. I want to use something to allow for multiple cells to be updated and tracked for the last time they were modified. I've tried various different scripts on here but ...
2
votes
1answer
42 views
Apps Script to search for names and file paths in folders by name
I'm making apps script to find files that don't contain the names "Done" and "InProgress".
The script that I have created is like this:
function testSearch(){
var sheet = SpreadsheetApp....
0
votes
1answer
15 views
Timestamp format with carriage return
I have this time stamp format:
var timestamp_format = "yyyy-MM-dd HH:mm:ss";
Which produced a result like:
5/25/2019 10:52:43
but I would like to have a carriage return between the date and time ...
-1
votes
0answers
25 views
Google Apps Script GetUI, disable buttons
I'd like to show a popup while the script is running with no buttons so the user cannot dismiss it until the script finish running. Tried with GetUI and could not disable the buttons.
Any ...
0
votes
2answers
29 views
Export PDF link sheet ID auto change
I have the link for export as PDF a specific range on a sheet
=HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
1
vote
1answer
35 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
18 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
1answer
60 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
28 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
27 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
22 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
1answer
35 views
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
19 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(...
