New answers tagged google-sheets
1
If you have Google Drawings, as well as Google Sheets, you can add a square bullet by going to:
Insert, selecting Drawing, and then click on the Shape icon.
Select Shapes in the drop-down window and create the desired shape.
Select Save and Close, and then position and resize the image as needed.
The downside to this feature, however, is that you will ...
0
One solution is to use datetime in the select vs. separate date and time specs.
See linked sheets.
Revised query is:
="select A, B where A > datetime '"&text(datevalue($A$6), "yyyy-mm-dd")&" " &text(timevalue($A$6), "hh:mm:ss")&"' limit 1"
Could not find documentation for the query argument spec of date, timeofday, datetime. Just guessed ...
1
EDIT (following your comment)
All duplicate values highlighted in both columns is achieved using:
In range D3:D27: =COUNTIF($A$3:$A,D3)
In range A3:A27: =COUNTIF($D$3:$D,A3)
First approach
I believe the formula you need is as simple as: =$A1="Roman"
1
First thing to have in mind is that Google Sheets (as well as other spreadsheet apps) use serial numbers to handle dates, so when a value looks like 8/15/2019 (if it's handled as date and not as text) Google Sheets internally handle this value as 43692.
Recarding COUNTIF, this function requires two arguments and if you will use a comparison operator like &...
0
You do not need to use AND.
You just need to create 2 rules for your range (or maybe more?) using the following formulas:
for the checkbox: =COUNTIF($D16, "=TRUE")
for the "HIGH": =$A16="HIGH"
You must also note the order these rules are applied on the attached image.
As you see you get to highlight the whole row.
If, on the other hand, want to have ...
0
Not sure what you trying to achieve but here's a try
={if(B3<>"",max(Backend!$A$3:$A)+1,);ArrayFormula(if(B4:B100<>"",A3:A100+1,))}
Try to provide us a sample with expected results.
0
You can set the permissions for link access in a similar way to the previous answer.
From the sharing popup - click advanced. This adds a row for public access, set to off by default. You can click it to access the Link Sharing popup in which you can set it to be publicly accessible to those with the link. Once you've turned link sharing on a drop-down ...
0
The documentation says this:
The NOW Function and Worksheet Recalculation The NOW() function is a
member of Google Sheet's group of volatile functions, which, by
default, recalculate or update every time the worksheet in which they
are located recalculates.
For example, worksheets recalculate each time they are opened or when
certain events ...
0
Just have the hours summed up in each member's sheet and import the sums which you can again sum up for the grand totals.
Please have a look at this sample sheet I prepared for you.
1
Use IMPORTRANGE() to import data and VLOOKUP() to find match between Product Section.
Here's a sample :
First you need to import data from another sheet with IMPORTRANGE()
You need to accept the access between sheets when you use IMPORTANGE: The best is to do this with a sample of every sheets to be sure you allow access for each one.
To find match ...
1
On IMPORTRANGE the apostrophes aren't required. Instead of
("""'"&B5&"'!$F15"&"""")
use
B5&"!$F15"
0
Assuming your row of 300 names starts in cell A1:
Insert this formula into cell A2:
=INDEX($1:$1,INT(COLUMN()+(ROWS(A$2:A2)-1)*10))
Drag the results shown in A2 across to J2.
Drag the resulting row down to row 31.
0
You can use an array like this :
={A2;A4:A8;C1:C4}
It will return an array like this :
A2
A4
A5
A6
A7
A8
C1
C2
C3
C4
You are just limited by the number of column, every range must have the same number of columns.
For a single row it's the same deal except you need to replace ; with ,:
={A1:D1,I1:K1}
It returns :
A1 | B1 | ...
0
The functions you're looking for are REGEXMATCH combined with OR and IF.
So IF will test three conditions (with OR) and then if OR returns a TRUE boolean, copy the cell's content, else do whatever.
=IF(or(REGEXMATCH(A1,"XL"),REGEXMATCH(A1,"XLT"),REGEXMATCH(A1,"STX")),A1,"no match")
Here's a way to do it with only one formula
ArrayFormula(IFS(...
1
To preserve the order the selections are made without resorting to another app or creating your own web form (as stated at Rubén's correct answer), another (easier maybe?) alternative would be to:
Add a "Title and description" above the question
Change the type of your question from "Checkboxes" to "Paragraph"
1
There's a way without dragging a cell with Arrayformula :
=ArrayFormula(ROW(INDIRECT("A1:A"&A2))) Where A2 is the n value
There's no incrementation in Google spreadsheet so the only way is to find a build-in counter : the rows/columns.
ROW() and COLUMN() return the adress of a cell or an array with Arrayformula. Now your case is using a dynamic ...
0
Try using Zapier :
You connect it with your spreadsheet then if there's a change, Zapier will send you a mail with the content of the row.
Lookup Spreadsheet Row - Finds a row by a column and value. Returns the entire row if one is found.
1
You must have "Dynamic Email" enabled (gif file from googleblog.com).
Have you checked under your Gmail Settings the following?
As you see one must first enable the Always display external images option.
The actions you can take with "Dynamic Email" are mentioned at: https://support.google.com/mail/answer/9266768
You can use dynamic email to:
...
1
Please try adding the following:
eventCal.createEvent(volunteer, startTime, endTime, {description: des});
https://developers.google.com/apps-script/reference/calendar/calendar#createEvent(String,Date,Date,Object)
1
Google Apps Script doesn't have any objects/methods to handle Google Forms view (respondent view) and the order of selection isn't an attribute that is included on the response submission.
If that is a must, then you should look for another app or build your own web form.
0
So how exactly does one reference another cell with an array of values in it?
Assuming that the above statement should be read literally, that isn't possible. The alternative is to use the reference of the cells occupied by {43;12;98;2}, so if A1 has the following formula:
={43;12;98;2}
then the sparkline formula to use is
=sparkline(A1:A4)
NOTES: ...
2
After continuing to play around with functions, I was able to find a solution! Here is the solution for those trying to attempt something similar:
=IF((AND((MOD(A:A,4) = 0),(NOT(ISBLANK(A:A))))), true, false)
1
If I understood the question you want that clicking on a edit response link on your spreadsheet the target be opened on the sidebar. This isn't possible.
Several possible workarounds imply to read the link from a selected cell and use that link to set the iframe src attibute. To read the active cell value you could use
var value = SpreadsheetApp....
1
(Similar questions have been asked before but I believe you will get confused trying to modify the code.)
What you want to achieve is possible by writing some code (a script) in the spreadsheet linked to the form.
Step 1
The first thing to do is to create your form. For the script to work you have to remember just one thing. The first question MUST be the ...
0
Google Apps Script events doesn't include an event for changes made by an API. If your WordPress Contact Form 7 is open source and already using Google Apps Script or the Google Sheets API add to it an adaptation of the code that you planned to use on you onChange / onEdit functions.
1
Google Apps Script on edit triggers (simple and installable), as well as other similar triggers, only are fired by user actions made by using the Google Sheets user interface, but the on form submit installable triggers are fired when a form response is submitted.
References
https://developers.google.com/apps-script/guides/sheets
https://developers.google....
0
Not possible in Google sheets. There is no such "Event" in Google sheets.
You should read about the various Google Sheets-specific triggers that let scripts respond to a user's actions in a spreadsheet. Event Objects on GAS (Google Apps Script).
The type of change (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, ...
0
Please have a read on how to use triggers on Apps Scripts:
Simple Triggers
Class trigger
Do pay attention on the restrictions
Following your comment you could also try the function onFormSubmit(event)
Follow this link to find out how to test a trigger function in GAS
1
You can copy paste the data in a tab free of data in column A.
Then in column B1 you can use this :
=REGEXREPLACE(formulatext(A1),"=",)
Copy the cell for every data you have in column A
Copy Column B then paste with the option values only
Explanation :
FORMULATEXT() is self-explanatory : it returns a formula based on a cell I.E. the string =+/- x here....
0
Here's the formula you need
=SUMIF(ARRAYFORMULA(ISTEXT(B1:B3)),TRUE,A1:A3)
First parameter :
Range (required) - the range of cells that should be evaluated by criterion.
The formula expects a range. This is why ISTEXT(B1:B3) alone doesn't work because it returns a single Boolean FALSE.
To return a range, you need to use ARRAYFORMULA()
I.E : ...
0
You have a few problems in your examples: 24 hours is 1440 minutes.
Also you say 1% but then your example shows that you are calculating a 10% reduction.
If you want a 10% reduction just multiply x*0.9
if it is a 1% reduction just multiply x*0.99
3) There is a minimum (or maximum?): Once 1 minute > 1%, it reduces by
1 minute instead.
you need to ...
1
I added a blank column before my numbers and made my numbers BOLD so that my eyes are drawn there and not to the default google sheet numbers.
1
In cell A2, put your integer seed value - this can be anything, but a small number is fine (something like "34"). In cell A1, paste the following formula which auto-seeds the first cell of the pseudorandom sequence:
=MOD(ROUND(2147483647*16807*MOD(ROUND(MOD(A2*EXP(1),1)*2147483647*16807,0),2147483647)/2147483647,0),2147483647)/2147483647
In cell A3, paste ...
0
Try using image instead of emojis?
here's a sample
=IMAGE("https://s3.amazonaws.com/pix.iemoji.com/images/emoji/apple/ios-12/256/face-with-tears-of-joy.png")
Links of images come from emojipedia.
0
Let's say you want to search for "foobar" in the A1:B10 range.
Use a IF formula with a condition based on =REGEXMATCH :
IF the range contains "foobar", return the cell, else we do nothing. You can return the cell data but also coordonnate with =ROW() and =COLUMN()
You want to test every cells so I made an array with ArrayFormula.
=ArrayFormula(if(...
0
Turns out I can check "Anyone with the link can edit" in the Sharing menu on Google Sheets and anonymous edits do in fact trigger the scripts successfully. The only thing that doesn't work in my case are toast messages (they don't show up for anonymous users) and the executions don't get tracked in the dashboard, which makes it impossible to accurately track ...
2
Here's a way to auto fill dates without erasing border format :
Cell A1
1/1/2016
Cell A2
=ArrayFormula(ADD(A1,row(INDIRECT("A1:A"&30))))
Credit goes to Rubén
For the 'name' column you can copy a the cell Claire for example then select the range where you need the name then paste values only.
Shortcuts are CTRL+C -> CTRL+SHIFT+V
...
2
That's a classic mistake when using Group By in a =QUERY
check this https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/
The GROUP BY clause is used with aggregate functions to summarize data into groups, in the same way a pivot table does. {...} Note, every column in the SELECT clause (i.e. before the GROUP BY) must either be aggregated (e....
1
Only users with edit access to the spreadsheet are able to fire through their actions on edit / on change triggers.
The alternative is to create a web application setting it to run as the script owner by anyone. The overview about how to create such web application are on https://developers.google.com/apps-script/guides/web.
-1
Command + Option + Shift + V works
0
For those who are reading in 2019 or later, I found a simple way to do it using HOUR and MINUTE functions.
Supposing that the duration is at cell A1, you can use:
=HOUR(A1)*60+MINUTE(A1)
1
In order to mark as checked a checkbox when other checkbox is checked but still able to uncheck/check the first checkbox directly you will have to use an on edit / on change Google Apps Script trigger.
For a general overview about extending Google Sheets through Google Apps Script, please read https://developers.google.com/apps-script/guides/sheets
0
You can stock the data in several var then return them in your function with an array;
Here's a dummy function to illustrate :
function returndata(data){
var email = "user@" + data;
var confidence = 90;
var type = "personnal";
return ([email,confidence, type]);
}
Here's what you get:
In your case instead of return (data1.data.emails[0]["...
1
Method 1: Using Find and Replace
Select C column or the range where you want to find and replace.
Then CTRL+H for Windows or CMD+H on MacOs
In Find: -
In Replace: Nothing
Then click on Replace all.
Method 2: Using a formula
You can also use =ARRAYFORMULA(REGEXREPLACE(C:c,"-",)) on cell D1 for example to obtain a column with phone numbers without -...
0
Here's a sample where I tried to solve the issue in tab "Result"
There's some additionnal columns for each quarter (A:A, B:B, C:C) then with some QUERY you can check if there has been an attendance or nah.
The formula in A3 :
=index(transpose(query({transpose(INDIRECT(A2))},"Select "&textjoin(",",TRUE,ArrayFormula(if(len($D$3:$D$100),"Max(Col"&...
-3
Seems to be a limit. Working on a 69 column doc with 5906 row entries. I can only populate 5500 rows in the new spreadsheet.
Not sure why...
1
This is not possible but this could be done by using an on edit / on change Google Apps Script's trigger.
Example:
function onEdit(e){
var sheet = range.getParent();
if(range.columnStart === 2 && range.rowStart === 1) {
sheet.range('A1').setValue(sheet.range('A1').getValue() + e.value);
}
0
After some trial and error, I've figured out a solution to this problem. It seems as though the Google Sheet recalculation settings are set to occur only when a change to the sheet occurs, or you can change these settings to occur on a change to the sheet and on specific time intervals. For my purposes, I left the settings to on change only and then in my ...
0
Prayers answered on Mac: To switch between tabs/sheets, you can just use:
Option+↑ and Option+↓
Or Option+Shift+K for the dropdown selector
1
Nobody answered, so here's a simple solution using arrays, importrange, query in Gsheet.
With =IMPORTRANGE you can import data from your master sheet : don't forget to link the sheets between themselves.
On my example, i importedrange from the same sheet but it'll works with any sheet you own.
There are multiples ways to do this (here's an example) :
...
Top 50 recent answers are included
