Yes you can!

If only Google updated the API inline with the UI release.

@@yagisanatode Indeed. I opened a feature request. Until this happens I use a workaround to get what I need: SpreadsheetApp.getActiveSpreadsheet().getRangeByName(namedRangeTemplateCell).copyTo(sheet.getRange(rowIndex, headerMap["targetColName"]), SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);

While your at the Google Issue Tracker consider give this one on People Chips a +1 : issuetracker.google.com/issues/227926015

I would set the Format > Number > Custom date and time in Google Sheets. Alternatively, you could look at the JavaScript docs on getDate() getMonth() getYear() then generate a new Date with just these values. If you want to display the date as a text string with toDateString(). Give it a crack!

@@yagisanatode Thank you for the response. I will try your suggestions. I have another question, I am on this part of your tutorial. //Validate if all cells are filled const anyEmptyCell = sourceVals.findIndex(cell => cell == ""); if(anyEmptyCell !== -1){ const ui = SpreadsheetApp.getUi(); ui.alert( "Input Incomplete", "Please enter a value in ALL input cells before submitting", ui.ButtonSet.OK ); I have a cell that can have a value of 0. On Data validation it allows 0 or more but after I click the button it prompts the missing warning since the value of that cell is 0. Does the 0 not counted as a value ?

This is due to how equality works in JavaScript. Because I didn't want zero distance, I knew that any zero will be considered false in the same way an empty string would also be considered falsy. The double equals comparison (==) is called loose equality. If you want a string one-for-one comparison, you can use the triple equals (===) Give this little test code a run to better see how they work: ``` function test_zeroAsEmpty(){ console.log("Single value: (0 to '' compare)", " Double equals type conversion:", 0 == "", " Triple equals type conversion:", 0 === "") const sampleArray = [ 'EV002', 0, 0.01, "" ] const findRespDbl = sampleArray.findIndex(cell => cell =="") console.log(`Find in array double equals ${sampleArray}`, "Position:",findRespDbl) const findRespTpl = sampleArray.findIndex(cell => cell ==="") console.log(`Find in array double equals ${sampleArray}`, "Position:",findRespTpl) } ``` More on Equality comparison and sameness: developer.mozilla.org/en-US/docs/Web/JavaScript/Equality_comparisons_and_sameness

You could filter out blanks using the FILTER function, but I think you are looking to add data from the top down, do this Google Apps Script approach might be more useful for you. kzbin.info/www/bejne/bKHYmH-uYrSFi5Y

@yagisanatode I have tried adding filter function , either I made an error or the formula is not working

@yagisanatode =Sort(Ratios!C5:H66, Row(Ratios!C5:H66), FALSE) This is the formula I am using for now, it is giving the last row on the very top of the result. But so for my data is around row 31, the rest are empty for now. I am using manual filter over the result to filter empty rows... I want to be done automatically

* Edit. This response contained errors. This is the correct approach: QUERY will be your best approach for a range that has more than one column. =QUERY(SORT(Ratios!C5:H66,ROW(Ratios!C5:H66), FALSE), "SELECT * WHERE Col1 is not null")

I tried the first formula, but got the error which says Filters Range must be a Single row or a Single Column..

Yes. Use `const ssNew = SpreadsheetApp.create("My new sheet");` then reference `ssNew` when copying the tab over. Check out the link to the playlist in the description on how to copy sheet tabs.

That depends on the type and size of data you are appending and how you are retrieving the data.

@@yagisanatode Thank you so much. all I had to do was remove all formatting from the source and destination sheets and it went down to 4 seconds!

Great to hear.

Thanks for letting me know. This is a weird one. The checkout page works directly from the link but when you click it from the main page it doesn't 🤔. I've sent a message to Teachable about this to see what they can do. checkout.teachable.com/secure/925477/checkout/order_d247xb29

Wonderful to hear. Thank you.

Two approaches here. 1. If you only need the data and no underlying formulas, you could select the flipped range and copy it (ctrl c), then paste (ctrl shift v) over it as values. 2. With a Google Apps Script macro, you could copy the values and reverse the column or row with the `reverse()` method, then set it in the location that you need.

Hey there. I am not 100% sure what you are asking for here.

Yes. Have a look at my series on find and replace here: kzbin.info/aero/PLSDEbLgMgqvo8LJmCbgjBOgmXBVS7tAKY With a bit of experimentation, you should be able to combine what you have learned from this tutorial with the Find and Replace tutorials to get what you need. Happy coding! 🐐

I'm not sure what you are asking for here, mate.

Give it a try. 👍

Check out the written version of the tutorial from the link in the description. This should answer most questions.

What have you tried so far?

Thanks for the kind words. You can filter out blank rows with `[grade_school-Number-range]<>""`. So you can this to the filter.

Glad I could help!

Glad it helped!

What does your `const ss` variable look like?

Hey there, you can't use buttons and AppScript-generated menu items on the mobile app, unforunately. One workaround is to trigger your script from a checkbox.

You're welcome!

Unfortunately, not with the mobile app. You can, however, user a checkbox to do the same. Here are two examples of checkboxes and the onEdit() simple trigger in action: yagisanatode.com/google-apps-script-create-a-select-all-checkbox-to-tick-checkboxes-in-any-desired-range-in-google-sheets/ yagisanatode.com/add-the-editors-email-when-they-tick-the-check-box-in-google-sheets-with-apps-script/

I cover this here: kzbin.info/www/bejne/oX6WimSQftZmjNU This tutorial is more for editors who wish to allow some edits to a tab, but prevent users from moving certain columns or rows.

You can check out this playlist for moving data in Google Sheets with Apps Script: kzbin.info/aero/PLSDEbLgMgqvqP0GQkiYOsH71yUeWBUi-Q

@@yagisanatode Thank you!

Thanks for the kind words. This is a Google Sheets tutorial, but good to know about MS Office 365.

Fantastic to hear! Happy coding!

Sure, you could create an array of column values within the formulas and then reverse that range. I'm curious, how would you use this?

@@yagisanatode haha no i just messes up the format of the spreadsheet and water to rearrange it without making another table

In the menu bar, you will see `.0` icons. One to increase decimal places and one to decrease decimal places. Try increasing decimal places to see if the value appears.

Unfortunately, not with the Google Sheets chart. Your best bet here is to roll your own with Google Drawing and embed it into your sheet or use Google Slides.

It's difficult to understand what is causing your issue without seeing what your formula looks like.

Hmm, that doesn't typically occur for me. It could be browser based perhaps. Maybe something like safari might do this 🤷‍♂️. You could try changing the font color to match the background.

@@yagisanatode using Chrome version 129.0.6668.71 on a MacBook and even changing the font colour to match the background does not remove it. I am using the HYPERLINK method and no matter what image I use it adds it annoyingly.

Great to hear!

@@yagisanatode I have noticed that the script takes a long time (sometimes up to a minute) when you have over a few hundred rows already filled in your destination sheet. I am using the binary method as well which I figured would be faster. Is there something that I can do to speed this up?

@@dannysoda1 Hmm, it should't be taking that long to process. Are you drawing data from multiple locations or adding a single row at a time? Appending a range of rows will be much more peformant and if you need to collect data from multiple locations in your Spreadsheet then something like getRangeList() might be more efficient.

@@yagisanatode I am drawing data from one tab and appending in one other tab. I am using a range of columns A2:N in the source tab and appending B:0 in the destination tab. Not really sure why its hanging up. I just deleted everything from the destination tab to try from scratch and its just as slow with a clean slate. Just looking through my executions the times range from about 3.5seconds to about 255seconds. Not sure why.

@@dannysoda1 Hmm. The data that you are drawing from, are you retrieving a single range of data, for example the last 5 rows, or are you drawing from multiple rows within the range? Whend you are appending the data, are you appending a range or a single row each time?

You're looking at cron jobs or cloud equivalents here. Sticking with the language used in this video, you can set up a clock trigger using the Google Apps Script trigger menu or programmatically using the API. kzbin.info/www/bejne/g5q6l5R5g7etqaM developers.google.com/apps-script/reference/script/clock-trigger-builder

Great to hear.

Check out my series on this here: kzbin.info/aero/PLSDEbLgMgqvre1M8SpQd7SR9V7Dn9M2H_&si=974__RZjE1DzKSoT