Questions tagged [google-sheets]
For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.
5,844
questions
-1
votes
0answers
5 views
Adding Photos into a Google script emails as text-body not attachment
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // Start at second row because the first row contains the data labels
var numRows = 3; // Put in here the ...
1
vote
1answer
15 views
Is there a way to do a “filter”, “if” function across multiple columns to only pull information based on certain criteria and headers?
I do not know how to explain this perfectly without a picture so please take a look at the image before reading.
I am trying to filter the data in Fig 1 (Above). based on the headers and then if the ...
0
votes
1answer
15 views
Matching multiple criteria efficiently in Google Sheets
I'm not quite sure how to word this but will try my best.
Below is a snapshot of a sheet I am working on to determine the global supply of a certain item.
I would like the last column to display it'...
0
votes
0answers
9 views
How to get the row and column number on Google Sheets?
I have a DataEntry Form and I need to get the row and column number where is a value given by the form.
For example, from the form I get "test" and that value is on the column 5(E) and row (10), so ...
0
votes
0answers
9 views
Prevent a Google Script from Modifying Date
I have a Google Sheet that I am applying an onEdit script to. That script (borrowed from here) capitalizes all new entries in every field of all ranges of all sheets.
function onEdit(e) {
if (...
0
votes
1answer
13 views
Conditionally convert formula range into static values
I have a Google sheet where I'd like to conditionally convert a formula range into its values (effectively locking them). I've searched for a function I could embed in a IF statement but don't think ...
0
votes
0answers
6 views
Trying to change sharing access of team drive files(more than 2,000 files) and generate link on google sheet via google script
I have a lot of files on a team/shared drive that I want to generate links of. I managed to do it, but I also want to make the links viewable by anyone with the link. When I added the script to change ...
0
votes
0answers
7 views
Find cell with highest cumilative value of a cell range
I have a row of count. In the last of the row(Column E) I want the text to appear, that is the cumulative lead of current leader:
Date Foo Bar Test Foobar
-----------------------------------
...
0
votes
0answers
20 views
Populate Last Cell With Query Function in Google Sheet
Looking for a way to populate an additional column in a QUERY function. Example being my data is
A | B
ID | Active
001 | FALSE
002 | FALSE
and using something like =(query(A:A, "Select A, B[=...
0
votes
0answers
11 views
Filter rows by value in column that doesn't exist yet
I'm working on a shared spreadsheet for a music camp. Multiple people in charge of the event (some with limited technical skills) will be using this spreadsheet. Last year, they wanted to look at just ...
0
votes
0answers
26 views
Google Sheets IMPORTXML's XPath Query for Siblings
What XPATH syntax works with Google Sheet's IMPORTXML function that will retrieve multiple siblings and place them in separate cells?
In this XML output, I'm trying to retrieve all elements within &...
0
votes
0answers
10 views
How to create chart until today's date, automatically, every day?
On a row from left to write there are dates (daily). Manually written, but automatically populated.
On another row, there are formulated numbers (formulated for each date).
The chart gets those ...
0
votes
0answers
10 views
OnEdit Trigger Limit
Is there a limit per day to trigger my script? I am running a trigger onEdit and the sheet may have hundreds of edit in a 24 hour cycle, I am just wondering if this will stop after certain number of ...
0
votes
0answers
9 views
date in google doc
I have two columns as a date, and I want to create a Gantt chart, so I had to change them to numeric variable, but I want those dates showing on my chart as a axis. does any one now how I can change ...
0
votes
0answers
15 views
Auto Categorization of Expenses
I am looking for a way to automate the categorization of a running list of expenditures.
So in column B I have expenses listed (auto imported directly from financial institutions) and in column C I ...
0
votes
0answers
23 views
Chart a graph from data until today
I chart a graph. Data is from Jan to Dec.
X axis is dates
Y axis is numbers.
I want the chart to show only until today without having to keep modifying the chart every single day.
Dates are in ...
0
votes
0answers
8 views
Remove text from all cells in a column after matching the criteria using conditional formatting
Can someone help me with the formula that can be used in conditional formatting.
Let say I have a Cell R2=HOL and I want all the content from column 'R7:R12' to be deleted after matching condition ...
0
votes
0answers
37 views
Script to copy a formula to the next row
I have a script that will capture data from the email and saved it to the next available row in a google sheet.
function RevisionHistory() {
// Use sheet
var ss = SpreadsheetApp....
0
votes
0answers
9 views
How to combine IFS and ISBLANK
I'm trying to create a function that measures ages and returns adult, youth, or nothing. I have some cells that don't contain ages, and I need them to return a blank value.
This function sorts the ...
0
votes
1answer
18 views
Google Script Negative Offset ERROR (The starting column of the range is too small.)
Below is a script I got online, this sends an email if anything was changed in column O, part of the subject line is the value on column D, so I am using offset(0, -12).getValue() to get the value, ...
0
votes
0answers
14 views
How to create a formula that permanently records the output of another cell on a specific date
I have a Google sheet to track my investments over time. The sheet has one tab that tracks my current position as of today.
I'm trying to build a separate sheet to track my investments historically.
...
0
votes
0answers
25 views
Split multiple line cells from various column and duplicate the surrounding rows
may I know if there is a way that I could split multiline cells from various columns in google sheet then duplicate the surrounding rows. I am new in this kind of environment so I am really confused.
...
1
vote
1answer
23 views
How to use IF THAN statements on date but converting it to time
On B2, I'm trying to put in an IF timevalue statement on a datetimevalue statement with this formula
=IF (A2 < TIME (7,30,0), "PREM", IF (A2 >= TIME (7,30,0), "INTRA", "0"))
However it doesn'...
0
votes
1answer
43 views
Help with Google Script Editor and checking the value of all cells in a certain range
I am using google sheets to record our printer cartridge inventory, and using script editor to check the cells C2:C50, and send out an alert email if one cell falls below 1 after editing. I finally ...
1
vote
2answers
27 views
Conditionally format if date is same/greater or smaller than current date
I'm trying to get my Google sheet to automatically colour a cell red if the current date is the same or greater than the date in the cell.
Example: today is 21/02/2020.
The date in the cell is 25/...
0
votes
0answers
18 views
How do I make a bar chart that parses comma separated arrays in Google sheets?
I have a Google form with a multiple choice question. In the Google sheet, it places all of the checked responses in one cell separated by commas.
I'd like to generate a chart that counts the ...
1
vote
1answer
53 views
How to extract time out of text in Google Sheets
I have the following issue and I would appreciate your help. I need to get the time values out of this string:
SomeText 02/02/2020 9:00 AM-02/02/2020 9:15 AM;"Text" 02/02/2020 10:45 AM-02/02/2020 11:...
2
votes
1answer
21 views
How to calculate a sum based on a date range
I am trying to find dates within 30 days in Column B. If the date matches the range it should sum all those that match in column I.
I have tried this formula:
=SUMIF(B:B,">="&TODAY()-30,I:I)...
1
vote
1answer
13 views
How do I run multiple onEdit functions in the same spreadsheet in Google Sheets?
all!
I hope I can count on your expertise to help me with a project I'm working on.
I want to call different functions based on which spreadsheet the event happened.
Ex. Typing 1 in B6 on "Room ...
1
vote
1answer
15 views
How do I display the first 40 rows from one sheet into another sheet?
Simple Question
How do I display the content of the first 40 rows from one sheet into another sheet?
1
vote
1answer
24 views
How to get LOOKUP to do an exact match only?
I have a column of form response numbers, and on a separate sheet a column of a random (but sorted) subset of those response numbers with another column next to it that has some scores.
I need to ...
-1
votes
0answers
16 views
How to protect different row sizes following sort in google sheets?
My rows differ in size depending on the differing aspect ratios of images I have inserted into an "image" column. Following sort, many of the row sizes no longer match the pre-sorted sheet. Do I ...
0
votes
0answers
8 views
Why does a shared Google sheet arbitrarily revert to a previous version of the sheet?
I've been working on various shared Google sheets with teams of 1/2 dozen to 20+ over 2 years now. Every so often a shared sheet will arbitrarily revert to a previous version and override all the work ...
0
votes
0answers
8 views
Download PDF fro Google spreadsheet gets weird
I'm downloading PDF from my spreadsheet, and it's working grate with the formatting. But now suddenly it stopped working. The formatting is totally off.
I have tried to change the formatting, the ...
0
votes
0answers
16 views
Creating a Table in Google Sheets from XML Output with Multiple Children Levels
How to create a table in Google Sheets from an XML output with multiple children levels?
I'm trying to create a table in Google Sheets with data sourced from an XML output. The XML source is here.
...
0
votes
0answers
18 views
How to sort by drop down option and secondary date range
I have a data validation range (drop down) list. I'm using a query to bring back info from the choice of that drop down.
I would like to then filter the information further with a date range that will ...
-1
votes
0answers
22 views
moving script between google sheets files
I have a script I have written for a file.
I would like to move it to different files so I can use it in them. I do not mind re-authorization. Is there a simple way to do this? Like having an addon ...
1
vote
1answer
29 views
How to select everything from two columns that have matching rows on google sheets
My mobile teams rely on a spreadsheet for localization. With time I realized that each team are using their own keys rather than using the same one. I need to start from scratch and have both teams ...
0
votes
2answers
26 views
How can I calculate a future date in google sheets when I know the number of months?
I have a date (TODAY()), and I have a number of months in a google sheet. How can I get a future date from this? I tried =DATE(TODAY()+MONTH(B63)). But the problem is that the months in B63 are not in ...
0
votes
0answers
15 views
Gdoc Sheet - Custom number format - How to change the color of a number within a text, from a formula?
I've looked a bit in all the questions we already have here, and after half an hour I haven't found what I wanted (maybe I'm a dumbdumb but I'll leave you the opportunity to tell me), so here it is :
...
0
votes
0answers
13 views
Creating a horizontal decision tree in Google Sheets - Can the one flip the default one?
I want to create a horizontal decision tree in Google Sheets - the default one is vertical - moving from top to down, I want mine to be like a decision tree from left to right branching out.
How can ...
1
vote
0answers
16 views
Auto Complete Data - Google Spreadsheet Script
I have data management system in which the data is kept at multiple google sheets. Now there is primary sheet where the data gets updated. Is there a way to populate the same data on multiple sheets ...
2
votes
2answers
41 views
Extracting range from of an array
From the following single cell array:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34
I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell.
The Challenge is ...
2
votes
0answers
21 views
How to make a cell autopopulate the date dependent on another cell
I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically ...
2
votes
1answer
39 views
Using dates as criterion for COUNTIFS in Google Sheets
So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it.
How can I make a COUNTIF(or similar) ...
0
votes
1answer
49 views
Embed Google Spreadsheet table Withing Google Document when data contains dates
I found a great script here that will add a Google Sheet table to my Google Doc.
function onOpen() {
DocumentApp.getUi()
.createMenu('Custom')
.addItem('Update Data', '...
1
vote
1answer
25 views
Look for value then print corresponding cell
This is my test sheet:
https://docs.google.com/spreadsheets/d/11oiKLq6EWk9grdrtIKHT0NQ4naTX_tSPl7o8zAQP3Tc/edit?usp=sharing
My core information is in the sheet labeled "Information." In the sheet ...
0
votes
0answers
31 views
Splitting checkbox response into multiple rows on another sheet
I am trying to take a Google Form response page, and change the way the data is displayed. Currently, I have the raw data on the first sheet, and I'm trying to format a second sheet that will split ...
0
votes
1answer
25 views
IF statement checking multiple columns
I would like my IF statement to check all rows and output a "check tips" if even one row qualifies.
Does that make sense?
=if(G3:I3=0, "check tips")
0
votes
0answers
18 views
Google Sheets Button 'Read Only accounts'
So basically, I created a button that is assigned to a function within google scripts, however when I tested the button on a 'view only' permission account, it has not response. Just wondering whether ...
