Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,709
questions
0
votes
1
answer
241
views
Google Sheets copy & paste special values script
I use Google Sheets as our main tool to dispatch construction trucks and communicate with our scale clerks the "who and what they will be picking up". I have researched for days on numerous ...
0
votes
1
answer
57
views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
- 191
2
votes
2
answers
67
views
How to insert timestamp when using either of the Fill Color tool or the Paint Format tool
I have been using the code below to insert a timestamp in column L when a cell fill color in column C is changed to white.
The code is working well, but while it works when I choose the white color ...
- 23
0
votes
2
answers
147
views
Insert timestamp when cell background color is changed
I have been using this below code which was working perfectly:
function colorChangeCheck(e) {
var sh = SpreadsheetApp.getActive().getActiveSheet();
var color = sh.getActiveRange().getBackground();...
- 23
0
votes
0
answers
40
views
Locking Google Sheet Cells Monthly
I am using Google Sheets, and my columns are set up for each month (Jan, Feb, March, etc). I have several rows that are pulling data from other tabs in the sheet to create an at a glance master list. ...
- 1
0
votes
1
answer
111
views
Google sheet: run a script via the share link (without login)
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 ...
-1
votes
1
answer
45
views
QueryByName() function from another sheet in google spreadsheet
Is it possible to use the QueryByName() function using another sheet from the same spreadsheet?
=query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0"))
I ...
- 1
0
votes
1
answer
30
views
Is it possible to write a script that automatically triggers some function within a Google Sheets add-on?
I'm using some Google Sheets add-on which pulls information from API databases, and it has both a free subscription and paid subscriptions which add many additional abilities which I don't really need,...
- 5
0
votes
1
answer
882
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 ...
0
votes
2
answers
31
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 ...
0
votes
1
answer
36
views
How to enable macros
Looking for some insight. In Google Sheets I added a series of buttons linked to macros. Works just fine for me and another user. Other users are telling me that they click the button and nothing ...
0
votes
1
answer
510
views
Update a sheet row by matching a value from another sheet
I'm new to App Script and I'm trying to update an existing row by matching value coming from another sheet. Basically record all the data from the "receipt" to the "record" by ...
0
votes
0
answers
34
views
Creating a Button that allows you to Print a select area of cells
I have a document here that I needed some help on!
https://docs.google.com/spreadsheets/d/1AFcHk-5JxMxj1ma-vTmCHIwBVn81Mgmxl001k__IMT4/edit#gid=0
I'd like to create multiple buttons in one tab that, ...
0
votes
2
answers
113
views
How do I add an Apps Script function to a Google Sheets cell?
I'm trying to figure out how to count the amount of times a group has clicked on a specific cell containing a cell.
I've gotten it to work with having a separate drawing next to it with the function ...
0
votes
1
answer
47
views
End of day data with trigger in google sheets
I want to copy/paste the values of a specific column into the next column using a trigger at a certain time of the day. The script works when I run it manually, it runs as per 'last run' via trigger ...
0
votes
1
answer
13
views
Google Sheets. I'm trying to insert a row of CheckBoxes IF B2 Cell has text [closed]
The trick part is... i can't figure out a way to do it, the formulas don't let you add checkboxes. I tried using the code from Google Script to insert a Checkbox but nonetheless i get nowhere. Please, ...
1
vote
0
answers
473
views
MMS/SMS Autoreply using Google Voice/Gmail Apps sends reply in different SMS thread
I am using Google Voice, Gmail and Google App Script to implement an 'SMS autoreply' system using the code described here:
How do I auto-reply to text messages received through Google Voice?
Very cool....
- 111
0
votes
1
answer
219
views
Google Sheets getRangeByName isn't working after duplicating sheet
I use the following function to duplicate a sheet, and it works great.
function duplicateDataSheet() {
const date = getTodayDateAsString();
const newDataName = `${date}_data`;
const ...
- 612
0
votes
0
answers
13
views
Adding the contents of another cell then clearing it [duplicate]
I'm trying to get a cell from any row in column J to update the value of the E cell in the same row but cant quite figure it out. How i want it to work is someone can come along and add a quantity i....
0
votes
1
answer
33
views
Keep copy of all data when the data updates periodically
My sheet gets updated automatically every five minutes. How can I save the previous data, i.e., data just before each update, so that I can keep all data. I also want to make a line graph which ...
0
votes
1
answer
28
views
How can I link/stick a range from Sheet1 and a column from a Sheet2?
I have a spreadsheet test here.
My project is a bit long (1000+ rows) but I created something that works in a similar way.
My hypothetical situation:
I receive grades from my students via Google Forms ...
0
votes
1
answer
17
views
Google Sheets - Preserving Data after Importing Range
I work in an organization where we give people jobs across 3 of our programs. I am trying to create a spreadsheet that can have a master list of all of our participants and which program they are in (...
- 1
0
votes
1
answer
29
views
How to return the next month from current month?
I'm trying to return the nextMonth(), but currently both of the following functions I've made don't work.
//this one returns a string of "October1" instead of incrementing by 1
function ...
- 17
0
votes
1
answer
658
views
How to append a cell value to a certain column ("D2:D")?
Currently the function only allows me to get a range and append it to the "Data" sheet. But I have no way of appending that value from "Sheet3" to another column for instance "...
- 17
0
votes
1
answer
32
views
How can I insert multiple empty cells, if a certain criteria is met?
I'm currently stuck trying to figure this out. Basically, I'm trying to insert an amount of empty cells only when cells from A2:B2 return a value. I got it to work where if both cells are empty one ...
- 17
0
votes
2
answers
86
views
Two scripts running simultaneously in the same spreadsheet disturbs each other?
Script 1:
Scraping data from a website and puts the new collected data into Column A and Column B.
function scraping() {
var sheet = SpreadsheetApp.getActive().getSheetByName('PageA');
var url = '...
- 149
0
votes
1
answer
2k
views
How to restore an old version in Google Apps Script's new editor?
Google Apps Script's old editor can have old versions of its scripts restored by using Google's standard File=>See version history (standard as in Google Drive).
However, the new editor doesn't ...
- 1,008
0
votes
1
answer
826
views
Get list of Google Drive folders into Google Sheet
I am trying to be a little organized, and want a way to get a list of all folders in my Google Drive in Google Sheet.
It should not have the Files, only folders.
I was able to find following code, but ...
0
votes
0
answers
35
views
Colour scale for many rows independently once
I found a solution to colour scale many rows independently using the below script that I found in an answer from Oren Pinsky (thank you!), but can't figure out how to add the selected ranges in the ...
0
votes
0
answers
26
views
change sharing permissions in google web app code
So I have a bot on google drive that among other things sorts files in folders.
In the partial code below they are looped and placed in PASS or FAIL folders.
The problem I have is that the files get ...
- 3
0
votes
0
answers
50
views
Fill in one cell if another has had a date selected and move the row when the date is selected and subjected to an onEdit script
I have built a Google Sheet with a lot of data. I have also put in a script to sort data automatically, by date, when new information is input. The new information can relate to past, present or ...
- 1
0
votes
1
answer
175
views
Script in Google Sheet to automatically run after importing CSV files to replace current sheet
I'm going to admit I'm a newbie at scripting. I've managed to write the script that I want but I want it to automatically run after importing the CSV file onto Google Sheets.
I have tried a simple ...
- 3
0
votes
1
answer
100
views
Google form save responses in multiple files based on condition on submit
I have this problem:
A Google Form that saves data into one file (spreadsheet)
Multiple users (admins) that access this file (all of them can see all rows)
Let's say first column is called Department ...
- 103
0
votes
1
answer
54
views
How can I make this custom formula work with multiple columns?
I came across this script that is a custom formula:
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s =...
0
votes
0
answers
40
views
Automatically sort 1 sheet on edit
I have a google spreadsheet with multiple sheets.
On one of the sheets, we use Zapier to populate new rows of data when a meeting is scheduled with our team. I'd like the sheet "Meetings" ...
- 13
0
votes
1
answer
27
views
Replacing part of a formula using Script
So I usually use template formulas (like - =IMPORTRANGE(LINK,Sheet1!A1) where link is the variable I replace using find and replace).
So, now I wanted to automate this task, i.e. for example the ...
- 3
0
votes
1
answer
157
views
How to speedup Google Sheets script?
I have a script adapted to my needs which was created following this.
I think my script is getting slower over time after adding more entry point in my sheet form.
// Function to submit the data to ...
- 3
0
votes
1
answer
50
views
Adding HTML into sendEmail [duplicate]
I've got an email that populates when the sheet is edited. But the body of the mail displays strange line breaks that aren't in my code.
My code:
function processEdit(e) {
MailApp.sendEmail({
to:...
0
votes
1
answer
95
views
Remove All Protections on Selected Range
I am trying to remove all protections on a range. I created a script that adds a protection when a user pushes a button. If the user pushes the button twice, it adds the protection twice. I came up ...
- 1
0
votes
1
answer
1k
views
Loop through each row on one sheet coping specific cells of each row to a second sheet until an empty row(last row) is found
I'm new to sheets script. I want to Loop through each row on one sheet (Driver List) while copying specific cells of each row to a second sheet (Driver List2) until an empty row (last row) is found. I'...
1
vote
1
answer
207
views
Where is the Apps Script located which Google Account is referring to?
Scenario
Under myaccount.google.com > security > Third party apps page, there are a few App Scripts apps listed, like so:
But there is no link or project ID or any way to actually reach or ...
- 133
1
vote
1
answer
36
views
Google doc access to anyone but keeping mastercopy as it is
I have one google document - which i like to keep as a master copy/template. I want to allow users without any kind of email/account to access this master copy - edit something and save it to doc file ...
1
vote
1
answer
71
views
How to Set Values for Discontinuous Cell Locations All at Once?
I am trying to populate a form template on a sheet. Say the a1 locations of the fields on the form to populate are:
[B7, B9, D8, D11]
(In actuality, I have 65 different fields.)
I want to populate ...
- 11
0
votes
1
answer
47
views
Double Sorting on Google Sheets
My sheet has a drop-down menu with options 1.-4. in column C.
I want it to then further sort each option segment using column A. So that option 1. is sorted A-Z separately from option 2. etc. How can ...
0
votes
0
answers
20
views
I used Google Apps Script as a text editor, and now the file will not load
Basically just had one long open comment to use as a journal. Now I'm getting "unexpected error occurred" and a spinning loader when attempting to open the project. Is it lost?
- 1
0
votes
2
answers
61
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 ...
- 21
-1
votes
1
answer
33
views
How to get the newest value of column E and display the value in A1?
I need help to make Google spreadsheet like this.
You can input values in column E, and the value of the newest input is displayed in A1.
I tried the below code, but it didn't work.
function ...
- 1
0
votes
1
answer
124
views
How do I correct the automatic borders that are scripted into this Google Sheet?
I have this Google Sheet document that already has functionality built in which applies automatic borders. I don't know how it is doing this. I've tried looking in the Script Editor, but I don't see ...
- 77
0
votes
2
answers
99
views
Getting the ThumbnailLink of a freshly created File in GAS
I've been trying create a file and then access its ThumbnailLink in GAS.
this works well -
let _file = DriveApp.createFile("TEMPFILE.svg", svg);
let file = Drive.Files.get(_file.getId());
(...
- 1
0
votes
2
answers
139
views
Can Google Sheets macros respect protected ranges?
I have developed a Google Sheets spreadsheet with loads of macros, but I want to have a protected version of it, a blank "master copy" that I then duplicate every time I want to use it.
I ...
- 161