2022 Developer Survey is open! Take survey.

# Tag Info

Accepted

### Text input if cell > than 1

Use filter(), like this: =filter(A57:A66, B57:B66 > 1)
Accepted

### Formula for matrix (dates in descending order)

3 changes, those in red/bold :
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 ...

### 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 ...

### 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"...

### 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....

### 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 ...

### 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()}`...
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
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) ) )
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, ...
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 ...
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:...
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 ...
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/",""),"([^\/]*)"))
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 ...
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 ...
1 vote