2022 Developer Survey is open! Take survey.
4 votes
Accepted

Text input if cell > than 1

Use filter(), like this: =filter(A57:A66, B57:B66 > 1)
user avatar
  • 5,914
4 votes
Accepted

Formula for matrix (dates in descending order)

3 changes, those in red/bold :
user avatar
  • 688
3 votes
Accepted

Formula for matrix (dates in ascending order)

I know you are looking for an Google App Script answer, but I have a formula: =IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") This formula goes ...
user avatar
  • 365
2 votes

Appscript: Compare a cell Value pre & post update to populate another cell

Self answering this, may help someone else. Firstly, including the formula to work in app script is better than formula based query on the generated sheet (daily tab) . It does not involve manual ...
user avatar
  • 199
2 votes

Making a graph from data of one out every n lines

You can generate the table to feed into the chart editor in a single formula: =arrayformula(query({{"";"Entry "&mod(sequence(count(A:A))-1,countif(A:A,0))+1},{{"Index"...
user avatar
2 votes

choose format for new Date() in Google Apps Script

This should do the trick: const values = [[new Date(), null, text]]; const firstBlankCell = sheet.getRange(sheet.getLastRow() + 1, 1); firstBlankCell .offset(0, 0, values.length, values[0]....
user avatar
  • 5,914
2 votes

Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage

Update: I have found the answer. I split the above formula into two conditions as such: =AND($C6=100%,I$4>=$E6,I$4<=$G6) then dark gray =AND($C6<100%,I$4>=$E6,I$4<=$G6) then light gray ...
user avatar
2 votes

Formula for matrix (dates in ascending order)

For completeness ,building up On David's answer, since I wanted to use it in App script as asked in the question, here's the declaration that works fine const cell = sh.getRange(`C:C${sh.getLastRow()}`...
user avatar
  • 199
2 votes
Accepted

Cell specifying the running total length

I edited a copy of your spreadsheet to get what you wanted. I hope it fits you well
user avatar
  • 688
1 vote

Creating a dynamic 2d array

You can create a matrix like this: =arrayformula( if( sequence(1, max(A1:A)) <= A1:A, sequence(1, max(A1:A)), iferror(1/0) ) )
user avatar
  • 5,914
1 vote

Cell specifying the running total length

The accepted answer by Daniele uses indirect() which is a volatile function that hurts spreadsheet performance. It would seem simpler to use filter(), like this in cell C6: =sum( filter( B$2:B, ...
user avatar
  • 5,914
1 vote

How do I keep exact text formatting when using a data validation dropdown list in Google Sheets?

It would not be possible to have a drop-down list with a mixture of plain and bold text ... but using a particular encoding it will be possible. The formula is quite complex, take it apart function by ...
user avatar
  • 688
1 vote

How do I put the if function inside the lookup? - Daily Routine sheet

I'd use Index and Filter (Filter () creates a list of upcoming tasks, Index () takes only the first of those) =index(Filter(A2:A,timevalue(B2:B )>=timevalue(now())),1,1) to get something like this:...
user avatar
  • 688
1 vote
Accepted

Google sheet, from 2 lists to flattened table

See my comment to your post above for a tip regarding future posts. That said, suppose that your "A" from the first image is in cell A2 and your "1" from the first image is in cell ...
user avatar
  • 4,442
1 vote

How to write a Google Sheets formula to extra a substring from a URL?

Try with this formula in C2: =index( regexextract(regexreplace(B2:B,"https://www.reddit.com/r/",""),"([^\/]*)"))
user avatar
  • 688
1 vote

"Copy to" confusion

I couldn't find a solution but here's a workaround: Instead of appending rows after last row, I prepended rows by sh.insertRowAfter(1) and declared the statements to populate columns , as before. At ...
user avatar
  • 199
1 vote

I need to update the app script to also capture Gmail attachments' name along with other details

As noted in the comments, getAttachments() returns an array. So you need to loop through to get any/all of the attachment details. for (var k = 0; k < attachments.length; k++) { is taken from the ...
user avatar
  • 4,624
1 vote

Copy image from Google Sheets to Google Docs

I have just found a simple way (It worked today May 17, 2022): Shift key + Right click on the image It opens a menu. Choose "Copy image" Paste it normally (Right click on the destination ...
user avatar
  • 11

Only top scored, non community-wiki answers of a minimum length are eligible