Скачать или смотреть Apps Script Simple Triggers onOpen Bound Script UI menu creator lesson

Learn more about Google Apps Script
Complete course for beginners to Professional 26.5hrs of HD video
https://www.udemy.com/course/course-a...
Google Apps Script Complete Course New IDE 100+ Examples
Google Apps Script New Version Create custom functions within G Suite Docs Sheets GMail Drive Calendar and more

UI Menu Maker
Apps Script Simple Triggers onOpen Bound Script UI menu creator lesson
Bound scripts can use a trigger like the special onOpen() function, which runs automatically whenever a file is opened by a user who has edit access. Using this with the ui menu maker will automatically add the menu item to the spreadsheet and allow anyone who has edit permissions access the functions.

How to add a custom UI menu to your spreadsheet.

Using the onOpen() method will add the UI menu buttons to the spreadsheet menu when the application opens. If you need additional functions to run within the sheet when it opens you can add all the functions within this one onOpen().

How to send a custom message to the UI alert.

Create a function that will send a message string to be output into the spreadsheet alert window. This can now be used to create a popup alert with a custom message within your script.

How to select the values of the selection range cells.

Create a function that will get the selected values and output them into the alert menu content. You can use the getActiveRange() method to select the range of values to be used. Use getValues() to return the array of cell contents in a nested array of rows nested into a main array.

How to search the selected range cells for a value and then determine which cell matched the value and update the found cell properties.

Create values in your spreadsheet that you want to check for, if found that the cell array row item is equal to this value you can get the cell range and update the cell properties.

Within the function select the values of the active range selection when the button is pressed and the function runs.

Loop through the contents of the data, as you loop through the rows using indexOf check to see if the value of any of the items in the row array is equal to the value we are searching for. In the example I use the value “Laurence” The cell value must be equal to the value we are looking for, if there are any extra characters it will not return an index value within the row array.

If the value is found, the indexOf will return the index value, if no match is found the response will be -1. We can use this in a condition to check if the returned value is -1 or not.

Using the getRow() and getColumn() methods from the selection range, you can find the starting cell for the selection. The ind of the row array, and the indexOf of the matching column value, can then be used to add to the starting cell coordinates and calculate the current cell coordinates in the spreadsheet.

Use the calculated values of the row and column for the match, and get it as a range. Once you have the range you can then update the cell properties like setBackground().

Update the background color to yellow of the found cell.

How to update the selection cell values to new values.

Getting the selected range value, the dimensions of the array will need to match to the new value array in order to be able to update the range with the new values.

The array method map() allows us to return updated values of the array into a response that can then be used to create a new array.

Select the active data cell values.

Create a holding array that will be used to add the updated value to, as well as a starting value to a counter that will be added to the cell values.

Loop the data values for each row. Using map update and returning a new array for the row values. Add this newly created array for the row values into the holder array that will then be used to update all the cell content.

Within the map method, check if the cell value contains a dot. If it does then break the content at the dot removing the values up to the dot. This is needed so that once the cell has the value and the dot placed, we can remove the previous value and add a new one to the cell contents. First determine if the cell value has a dot already, this can be done using the indexOf which will return the dot index value or -1 if no dot is found.

Check out my Website for more details at https://basescripts.com/
Subscribe    / @laurencesvekiscourses  

Tags and Keywords

#DiscountPrice #GoogleAppsScript #CustomFunction #SpreadsheetFormula #DollarFormat #USCurrency #ColumnHeaders #OriginalCost #DiscountValue #NewTotal #Formatting #CalculatePrice #CalculateDiscount #CalculateTotal #PriceReduction #PercentageDiscount #ApplyDiscount #SpreadsheetCalculations #AutomateCalculations #IncreaseEfficiency