Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,797
questions
0
votes
0
answers
13
views
Google Sheets / IMPORTjson(): How to Resolve "Invalid Argument" Error [closed]
Using the Google Sheets IMPORTjson AppScript function, I'm try to pass this long URL and keep getting the error "Exception: Invalid argument:" as a response.
The formula I'm using is =...
- 43.1k
2
votes
1
answer
1k
views
Put the timestamp of last edit on master sheet, next to edited sheet's name
I'm trying to come up with a script that will print a timestamp of the last edit made to a specific tab within a google sheet. Imagine you have 17 retail stores, each one having its own tab with a set ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Create a custom DateTimePicker in Google Sheets
In a sheet I have a column to enter a datetime value that would later be checked by a script to create a trigger.
I would like to set a picker in that column to ease selection and avoid errors.
I ...
CommunityBot
- 1
0
votes
1
answer
10
views
I can not remove data validation with 2 criterias - Function onEdit(e)
I would like to run function onEdit(e) to remove Data validation with these 2 criterias as below:
Remove for columns G,H,I,K if L is "no need".
Remove for columns M,N,O,Q if R is "no ...
CommunityBot
- 1
0
votes
1
answer
307
views
Run a script via the share link (without login) [closed]
There is a script for my Google spreadsheet so that every change in a cell is indicated by a red color marker. When users with a Google account that I have shared to the spreadsheet make changes, the ...
- 43.1k
0
votes
0
answers
11
views
How to Automatically Format a Google Sheet from Google Form data?
I have a Google Form which is linked to a Google Sheet that is submitting data for warranty exchanges for a client. At the end of each month they download the sheet as an Excel file to enter into ...
- 1
0
votes
2
answers
2k
views
Getting "Exceeded maximum execution time (line 0)" error with custom code when data input increased in "History" sheet
I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively.
I have a custom function that takes data in the "History" sheet and arrange ...
1
vote
1
answer
42
views
Automate auto-reply based on specific Google Calendar Events
I'm currently trying to automate an auto-reply with Google Apps Script for whenever I am out of office based on my Google calendar events named 'OOF'. Even if everytime I'm getting closer, there's ...
CommunityBot
- 1
0
votes
1
answer
5
views
Exception: The parameters (String) don't match the method signature for SpreadsheetApp.Spreadsheet.getSheetNam [closed]
I am trying to create a google sheets form that would automatically populate the entered data from one sheet to another within the same document. However, I am getting this error when running them. ...
- 43.1k
0
votes
1
answer
5k
views
How to reference a Google Sheet by filename?
How to reference a Google Sheet by filename?
I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename.
Example filename:
2018-11.xlsx
2018-12.xlsx
- 43.1k
1
vote
1
answer
31
views
Reference one of my own spreadsheets, within another, by its name
I would like to reference a spreadsheet that is within my own Google Drive, owned by me. The catch is, the spreadsheet changes. It will be a different spreadsheet ever day, with the date as it's ...
- 43.1k
0
votes
1
answer
347
views
How to integrate the real OpenAI ChatGPT with Google Sheets?
Nowadays there are a lot of posts using the word ChatGPT but many of them use it as a common noun to refer to GPT-3 and some chatbots, not to specically make refence to https://chat.openai.com/chat / ...
1
vote
1
answer
28
views
How do I print a specific number of A based on a value from a cell in Google Sheets
There's a cell that would contain the number of times a letter would be printed in a cell.
i.e.
cell a1 = 3. b1 should look as "A A A"
my rough script is:
for(var arow=0; arow<4; arow++) {...
CommunityBot
- 1
1
vote
1
answer
21
views
Cannot find active sheet: TypeError: read properties of undefined (reading 'source')
I'm trying to add the current date to a cell in column K when any cell in that respective row is changed.
I found this script and altered it for my situation so I can deploy it as a trigger:
function ...
- 10.5k
0
votes
1
answer
2k
views
How to include VLOOKUP with a loop for a list of data on Google Scripts?
I have a list of data on Sheet1 columns A-N, and a list of updates on Sheet2, columns A-N. I have a code that imports data to Sheet2, and I would like to add to my code a similar function to VLOOKUP. ...
CommunityBot
- 1
0
votes
1
answer
7
views
Creating google forms that can be submitted only if the person selected correct answer (Google form is not a quiz) [closed]
Is it possible to insert an "If/Else" function in Google Forms using a Google Apps Script?
- 43.1k
1
vote
1
answer
3k
views
How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?
I currently have a column, e.g. ColB, containing SUMIF functions with its data range from other sheets and a column, e.g. ColC, with dates that I want to automatically update to the last date ColB's ...
CommunityBot
- 1
3
votes
2
answers
5k
views
How do I get Google forms edit url to work
I want to generate an edit url link in the google sheet for a google form which is linked to it.
There are already instructions on how to do it. However they do not work for me.
I have followed the ...
- 248
2
votes
1
answer
2k
views
How to automatically forward Gmail messages when a label is applied?
I have found this question that is answered:
How can I automatically forward Gmail emails when a label is applied?
However I'm using that exact script and have replaced the label and recipient with ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Auto hide row based on cell text (formula based)
My plan is to hide an entire row if a specific cell in the row showed specific value. I already found the script and the script work amazing. But the problem now, since the script use onEdit function, ...
CommunityBot
- 1
0
votes
1
answer
351
views
Grant permissions on a shared google drive with script
I have google drive folder with subfolders, each subfolder has several google sheets and each sheet has a set of functions that run when users click on buttons, the issue is that for every time user ...
CommunityBot
- 1
2
votes
1
answer
3k
views
Need to get a list of Drive files several subfolders deep
I need to get a list of all the files in my Drive with all of the nested folder names where the files reside.
I found this very helpful answer which is almost exactly what I need:
Get a list of Drive ...
CommunityBot
- 1
0
votes
2
answers
238
views
Converting Google Sheet to JSON format while rows are vertical
I am trying to convert the Google Sheets into JSON format using Google App Script.
It worked but I am not getting the right format and I am little struck.
My sheet looks something like this in the ...
CommunityBot
- 1
0
votes
1
answer
14
views
Insert formula, process and replace by raw value
What is the best approach to achieve the following:
If a cell in column A has content, then place a formula into the cell in column B in the same row, referencing the cell in column A.
Once the ...
- 10.5k
1
vote
1
answer
47
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" ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Embed an interactive calculator in my website using Google Sheets
I have implemented a calculator in a Google Sheet, and would like to embed it in my website. There will be multiple users. They should be able to enter numbers and see the result, perhaps with a chart,...
- 10.5k
-1
votes
0
answers
16
views
Need "Reset Button" in Google Sheets to revert drop down lists to one of the options ("None") instead of clearing cells completely [closed]
thanks for reading.
I am attempting to create a fee calculator for a small business which uses the INDIRECT and SUM functions along with drop down lists to find the total fees if a family enrolls in ...
- 1
0
votes
1
answer
105
views
How to get a notification when a column in google spreedsheets changes
I need help with a code.
I need to get a notification on my email or Slack when for example, column D changes. But I would love to received what column changed.
Here is what I got right now.
function ...
CommunityBot
- 1
0
votes
1
answer
95
views
Script to Copy multiple sheets from an Excel file and convert it to a Google Sheets file
I have an Excel file with 3 tabs (Sheet1, Sheet2, Sheet3) attached to a Gmail message and I would like these to copy to a Google Sheet file with 3 tabs (SheetA, SheetB, SheetC). The problem I am ...
CommunityBot
- 1
1
vote
1
answer
205
views
Macro to move down and separate data
I wish to use a macro in Google Sheets to
find the number 1 in column A
then move that corresponding row and all following rows down to start at A9 row
then find the number 2 in column A
then move ...
CommunityBot
- 1
2
votes
1
answer
98
views
Removing Last Updated value when No data was selected from the column to check
I need help with my Google Sheet script. It is working fine when showing the timestamp of "Last Updated" at col B every time a new Call Status was set at col A; but when I delete the status ...
- 2,167
0
votes
1
answer
1k
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(...
CommunityBot
- 1
1
vote
1
answer
982
views
Google Sheet jump to current date on Open (very large sheet)
I understand that this question has been asked before but most users seem to only have the dates in one column or a limited number. The sheet I am trying to work with has the schedule for one entire ...
CommunityBot
- 1
0
votes
1
answer
31
views
Protecting and unprotecting identical ranges across multiple sheets
I teach a middle school robotics class and we do an inventory check every six weeks to make sure that all the pieces are there. I would like to track over different class periods how the inventory is ...
CommunityBot
- 1
0
votes
1
answer
166
views
Macro to copy and paste values from multiple cells as text into a Note
I'm new here and struggling a bit with the javascript in macros. I just need a steer as to how to get a macro to copy from a range relative to an active cell.
I'm a showcaller, that is, during live ...
CommunityBot
- 1
4
votes
2
answers
12k
views
Adding a Month to a Date in a Google Spreadsheet with a Script
I'm trying to create a Google App Script that adds one month to a date already on a Google Spreadsheet. In the example below I was trying to take the date from cell B2 (1/1/16) and add one month and ...
- 2,167
1
vote
1
answer
482
views
How to Add RTL Tables to a Google Document
As I'm trying to format google doc tables and in reference to this thread I was able to add some formatting to the header row, to the table data and was able to create a doc in a landscape layout.
...
CommunityBot
- 1
1
vote
1
answer
207
views
Send email notification when a data in column matches a certain value
I am trying to make Google Sheets send a notification to a particular person/s when one of the fields on column Q is equal to the word "High". I want the email to be sent when it is high ...
CommunityBot
- 1
2
votes
1
answer
311
views
Apply custom formula across all columns using Array Formula
I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
CommunityBot
- 1
1
vote
2
answers
1k
views
Google App Script - changeType "REMOVE_GRID"
I'm using a Google spreadsheet. I'm attempting to create an onChange trigger that will activate a script when any sheet is added or deleted. I was able to get the script to work when a new sheet is ...
CommunityBot
- 1
1
vote
1
answer
180
views
Automatically create a calendar document using Google Drive and Google Apps Script
Is it possible to use Google Apps Script to 'automatically' create a calendar document in Google Drive with the following document features.
Traditional calendar table
Accurate month and year ...
CommunityBot
- 1
0
votes
3
answers
39
views
Assign a default name if the cell value is null
I have a booking form, shown.
I also have a template form, hidden.
This script is assigned to a button to create completed bookings by copying the booking form data to a copy of the template and again ...
1
vote
2
answers
145
views
copying the Data to the next available Row in Another Sheet
I have run into a small issue with this script, it does copy the values in the range but pastes them under the 1500 cell in column A on the destination Sheet. I can't seem to get it to paste to the ...
CommunityBot
- 1
0
votes
1
answer
269
views
Sort sheet tabs name alphabetically
This is a script I located to sort my sheet tab names alphabetically. Is it safe for me to use in my free version of Google Sheets?
function sortSheets () {
var ss = SpreadsheetApp....
CommunityBot
- 1
1
vote
1
answer
472
views
Google Sheets script server error on activeSpreadsheet.getNamedRanges()
I have a script to get a list of Named Ranges on a google spreadsheet. I was working fine until yesterday, where the execution is failing with:
We're sorry, a server error occurred. Please wait a ...
CommunityBot
- 1
0
votes
1
answer
110
views
How to update multiple google sheet files on google drive from a template without changing the current values of certain cells
I created a google sheet template and then we created almost 200 sheets from that template, now we updated the original template, and I am trying to figure out a way to updated the 200 files without ...
CommunityBot
- 1
1
vote
1
answer
150
views
How can I get the information from a column next to the active cell to be included in the email?
I have this script running which works fine. However as you see, it only copies the information of the active cell in Column 10 toString. How can I get the information next to it in Column 11 to be ...
CommunityBot
- 1
0
votes
1
answer
20
views
How to combine cells of data in Googlesheet to a single/consolidate row and remove duplicates?
I'm trying to consolidate all the invoices that we get from the same contractor to a single row that will have the business name, all the memos/invoices for the week in one cell along with the added ...
CommunityBot
- 1
1
vote
1
answer
137
views
Google Sheet Script for automatically entering date/time stamp when the adjacent column
I've found a script on here for entering a timestamp in a column based on edits to the adjacent column:
Date and time stamp automatically added in column
if(e.source.getActiveSheet().getName() == &...
CommunityBot
- 1
0
votes
1
answer
145
views
Google App Scrips onEdit function timeout
I have an onEdit function running fine into googlesheets, it is basically a countdown timer and displays the countdown into a cell. However after around 30 seconds of running, the display no longer ...
CommunityBot
- 1