google-sheets

[Solved] Script setValue based on the values of column based on value of other column matching reference

by

I believe your goal is as follows. You want to retrieve the value of cell “B3” of “Config” sheet. You want to search the retrieved value from the column “A” of “Relação” sheet. When the value is found, you want to put the value of cell “D9” of “Config” sheet to the column “L” of … Read more

[Solved] Google Scripts / Sheets Add prefix to data once it has been entered into the cell

by

Use the following: An onEdit trigger to check when someone has updated a cell padStart() to add the leading zeros replace() the first 4 zeros with “CSC1” (since only the first occurrence will be replaced if passing a string instead of regular expression) setValue() to update the edited cell function onEdit(e) { if (e.range.columnStart == … Read more

[Solved] Google Sheets – Create Data Validation

by

You can use this sample code: function createDataValidation() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var s1 = ss.getSheetByName(“Sheet1”); var s2 = ss.getSheetByName(“Sheet2”); var s2_lastRow = s2.getLastRow(); //create data validation per row for (var row = 1; row <= s2_lastRow; row++){ //create an a1Notation to select a complete row sample: “A1:1”, “A2:2”, and so on. var a1Notation … Read more

[Solved] How to display, using a filter, only cells that contain Japanese in Google Sheets? [closed]

by

note that DETECTLANGUAGE does not work with array/range so only: =IF(DETECTLANGUAGE(A1)=”ja”, “Japanese”, ) but you could use a script: function NIPPON(input) { var output = []; for (i = 0 ; i < input.length; i++){ try { output[i] = LanguageApp.translate(input[i], ”, ‘ja’); } catch(err) { output[i] = err.message; } } return output; } =ARRAYFORMULA(FILTER(A1:A, IF(LEN(A1:A)=LEN(NIPPON(A1:A)), … Read more

[Solved] How can I properly call a function with a custom UI button with a growing Google Sheet?

by

The onOpen trigger in your script is only for adding the Custom Menu in your Sheet. The function will only get executed when the user selected an Item in the menu that is associated with the function. In your example, clicking Get place info will execute the COMBINED2 function. Also, executing the script only when … Read more

[Solved] How can I properly call a function with a custom UI button with a growing Google Sheet?

by

Introduction Creating a custom UI button with a growing Google Sheet can be a great way to automate certain tasks and make your workflow more efficient. However, it can be difficult to know how to properly call a function with such a button. In this article, we will discuss the steps necessary to properly call … Read more

[Solved] Is there a code for Google Sheets that will move every tab of a specific color to the end of my list of tabs?

by

Moving sheets/tabs of a color to end of list of tabs using Google Sheets API version 4 function moveSheetsOfAColorToEnd(color) { var color=color || ‘#ff0000’; if(color) { var ss=SpreadsheetApp.getActive(); var shts=ss.getSheets(); for(var i=0;i<shts.length;i++) { if(shts[i].getTabColor()==color) { Sheets.Spreadsheets.batchUpdate( { requests:[ { “updateSheetProperties”: { “properties”: { “sheetId”:shts[i].getSheetId(), “index”: shts.length }, “fields”: “index” } } ] }, ss.getId()); } … Read more