New answers tagged

0

I used some regex : It needs to match lines like xxxx First name last name ^.\d{3} If there's a match, then take this line and the previous line. Else take the line + no visitor. Then special case when there's a single digit : Same idea, I used a regex ^\d$ Finally : =ArrayFormula( IF( REGEXMATCH(TO_TEXT(A2:A100),"^\d$"),A2:A100, if( REGEXMATCH(A2:...


0

Final solution (with great help of @Tedinoz): function copyrow(){ var ss = SpreadsheetApp.getActiveSheet(); var Dvals = ss.getRange("D1:D").getValues(); var Dlast = Dvals.filter(String).length; var lCol = ss.getLastColumn(), range = ss.getRange(Dlast,4,1,lCol); range.copyTo(ss.getRange(Dlast+1, 4, 1,lCol-4+1), {contentsOnly:false}); }


0

You have a spreadsheet with dates for the entire year in various columns in the range "F:EU" and rows 6-2900. When the spreadsheet is opened, you wish to automatically move the cursor to the column corresponding to "todays" date. Enter =Today() in Cell A3. The date should be in the same format at the dates on the sheet. Enter the ...


0

If all the intervening cells have data then as per many answers it's as simple: ⌘ ↓ (Command + Down arrow) = go to bottom-most cell in contiguous data range But as you may have found it doesn't work if you have gaps in your data. The best solution I've found is clunky, needs three steps and uses all the arrows except down, but it gets the ...


0

A really crappy way to do this would be to make all cells on one sheet equal their sister cell in the other sheet, and then add a formula in sheet 2 to do the same thing. It might be recursive, but if someone inputs information it would overwrite the formula and copy it into the other sheet. Then, people wouldn't type over the information in the other sheet ...


2

Oh geez, the error message totally misled me. The problem was with the column.setNumberFormat("@"); on the other because that tab was protected.


1

If you are getting the values from a spreadsheet set the formatting that you want in Google Sheets, then on Google Apps Script instead of getValues() use getDisplayValues(). Related Embed Google Spreadsheet table Withing Google Document when data contains dates


0

You have transaction data including three currency/value fields. The total number of rows, and the number of transactions for each user/customer is unknown and can vary. This key facets of this script are: create a temporary array (temparray) to capture row values create a second array (targetarray) to accumulate row values, and to enable a single "...


0

Use Bulk Sheet Manager. It also allows you to hide sheets, which may be useful if sheets have some dependency.


1

You want to create a Hanging Indent in a Google Document by Google App Script. It is important to note that the process to identify these methods can be easily established by trial-and-error testing. The method for a Hanging Indent is setIndentStart(indentStart) Doc ref getParaDetails() uses a same document to show some indent settings. Paragraph#1: Index ...


0

Try: =vlookup($E$2,$A$2:$B$6,2,false) Source data: cells A2:B6 Lookup field: cell E2. You can just enter a "name" in here, or (much easier) create a dropdown list from the range "A2:A6". Output cell: E3. The cell contains the formula shown above. The returned column is "2" = "City" Example


0

On the client-side code (the Google Apps Script HTMLOutput object, HTML/CSS/JavaScript) you could use the JavaScript global object Promise to handle the sequence of asynchronous tasks (like calling google.script.run) Another option might be to change the logic of your web app so instead calling different functions call only one that call the server-side ...


0

This answer is based on the work of user79865. You have a Google Form which exports results to a Google Sheet; you have embeded the sheet in a Google Doc and you are looking for a way to update the Google Doc automatically whenever there is a new form response. Your question is substantially answered in webapps question: Embed Google Spreadsheet table ...


0

Try this query: =query(A1:B4,"select A, (B*B) label (B*B) ''")


1

This was a REALLY confusing issue to solve, as the errors reported by the debugger did not point to the true culprit. In another cell elsewhere on the sheet, I had this formula: =SUMPRODUCT(LEN($A$5:$A)>0) But since my inserted cells started with A5, this was causing the error to be thrown when I would attempt the insert, if it tried to insert ahead of A5....


Top 50 recent answers are included