4

You want to update the value in Cells A1 and B4 at the same time. The solution is to update the values as an array. In this script: var values = range.getValues();: gets the entire range A1:B4 values[0][0]= "Apple" ; and values[3][1] = "Banana";: update the array for the equivalent of cells A1 and B4 respectively. range.setValues(values);: updates the ...


3

Add getValue() to var r = sheet.getRange("D11");. The resulting code line is: var r = sheet.getRange("D11").getValue(); The above because getRange returns Range object which not return directly it's value/values. Also replace r.hideRows(11); by sheet.hideRows(11); For further details checkout https://developers.google.com/apps-script/guides/sheets


3

You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string is in A1): =SPLIT(REGEXREPLACE(INDEX(REGEXEXTRACT(A1,"(\[(\n|\s|""|\w|,)+\])"),1),"(\[|\]|\n|\s|"")",""),",") You can adapt this formula to suit whatever ...


2

If the Tools --> Script Editor and the Add-ons menu options are missing when you open the file, it probably isn't actually a Google Sheets file. Make sure to navigate to File --> Save As Google Sheets. Then the Script Editor option in the Tools menu and the Add-ons menu will be available.


2

Inspired from the accepted answer, here is the version if you want to find in notes in all sheets: function searchNotes() { var allSheets = SpreadsheetApp.getActive().getSheets(); var sheet = SpreadsheetApp.getActiveSheet(); var cell = sheet.getActiveCell(); var searchTerm = cell.getValue().toString().toLowerCase(); var results = []; for(var ...


2

I expect you have already solved this, but still wanted to post my solution for others who might have the same issue. I edited user0's answer to allow for dynamic criteria function onEdit(event){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheets()[0]; // 0 == first sheet. var cellRange = SpreadsheetApp.getActiveSpreadsheet()....


2

getValue is a function definition in the range object. It'll never be empty. Invoke your function by using () like getValue(). Logger.log("getValue is a"+ cell1.getValue) Logger.log("result of executing function getValue is"+ cell1.getValue())


2

You want to convert the values of above image to the values of below image. If my understanding is correct and you use Google Apps Script, how about this sample script? I think that there are several solutions for your situation. So please think of this as just one of them. In this sample script, it supposes that the initial values are put to the cells of "...


2

You could use Google Apps Script to reorder the sheets. First you should activate the sheet to move, either manually or by the script, then use the moveActiveSheet(position) method of the Class Spreadsheet. Example This moves the active sheet to the position 1 function moveSheetToPos1(){ SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(1); } ...


2

Yes it's possible. Your script should iterate over the content to move. From this answer to Moving Files In Google Drive Using Google Script: function moveFiles(sourceFileId, targetFolderId) { var file = DriveApp.getFileById(sourceFileId); file.getParents().next().removeFile(file); DriveApp.getFolderById(targetFolderId).addFile(file); } Related ...


2

Say I'm forgetful, but I don't want to pester my coworker by calling her more than every two hours. I might remind myself just by jotting down when I call, so I can glance at that note next time I'm once again reaching for the phone. A script could do the same, scribbling down when it needs to run again, and checking it later. function onOpen() { var ...


2

You're missing the text "function" in front of oneditfx1 and oneditfx1. Also, your huge if/else if statement could be simplified by using a switch statement and nesting furthers ifs within that.


2

Yes there is a way and many people already accomplished this (including me) by publishing the now called G Suite Add-ons. For details please read https://developers.google.com/gsuite/add-ons I already mentioned two of my add-ons here on Web Applications, Record Viewer and Spreadsheet Freezer. Record Viewer In Google Sheets: How do I merge a range of a row ...


2

Use batch operations instead of unit operations. In other words, make an effort to avoid or reduce reading / writing of single cells specially in loops. When it is possible read all the values that you will need on a single operation, i.e. instead of using a for loop and getValue() to read a single value on each iteration use getValues() to read all the ...


2

Based off the accepted answer I created utility functions to work with ranges (and also named ranges since that's how I'm specifying the cells): This function setNamedRangeValues takes the sheet containing the named ranges, an array of strings which are the names of the ranges, and an array of values that you want to put into each range. The rangeName and ...


2

Try this Code function onEdit(e) { if(e.range.columnStart === 5 && e.range.rowStart > 2) { if(e.source.getActiveSheet().getName() === "Trends") { var oSet = e.range.offset(0, 6); if(!e.value || typeof e.value === 'object') { oSet.clear({validationsOnly: true}); } else { oSet.clearContent(); ...


2

Thanks to Rubén's comment, I looked at the execution transcript, and found this error: [19-11-28 15:11:32:379 PST] Starting execution [19-11-28 15:11:32:498 PST] SlidesApp.getActivePresentation() [0.114 seconds] [19-11-28 15:11:32:570 PST] Execution failed: Authorization is required to perform that action. (line 6, file "progress") [0 seconds total runtime] ...


2

Use on edit / on change triggers and the corresponding event object. Example function onEdit(e){ var a1Notation = e.range.getA1Notation(); e.source.toast(a1Notation + ' was clicked'); } Related How to trigger a script in Google sheets when a value changes References https://developers.google.com/apps-script/guides/sheets https://developers.google....


2

Working with date, time and specifically duration is a pain in JavaScript, and Google Sheets does not make it much easier. A JavaScript Date object (which is really a datetime object) is not suited for working with durations. A datetime is a lot more complex than it seems at first. So when you ask Where did the "days" part of the duration go? ... ...


2

Google Sheets doesn't have a way to stop the automatic data type assignation. Instead of using the built-in form response submission to Google Sheets you could use the Google Forms on submit trigger to prepare the response data to be stored properly in your spreadsheet. If you want to keep the data as was wrote by the form respondent your code should ...


1

How about this modification? I think that you can use getValues() and setValues() in your situation, because contentsOnly: true is used to copyTo(). So please modify as follows. From: var lastRow = destSheet.getLastRow(); destSheet.insertRowAfter(lastRow); source.copyTo(destSheet.getRange(lastRow + 1,1), {contentsOnly: true}); To: var values = source....


1

cell1.getValue and the other similar expressions are using the incorrect syntax. The correct syntax is cell1.getValue(). Reference https://developers.google.com/apps-script/reference/spreadsheet/range#getvalue


1

Publish a Google Add-on to the G Suite Marketplace Basic Concepts: You must use the G Suite Marketplace SDK in order to get to the “Publish” section for publishing an add-on, and you must first open the Google Cloud Platform for your specific project (apps script file) The G Suite Marketplace SDK must be enabled in the Google Cloud Platform The two ...


1

Yes it can. From https://developers.google.com/admin-sdk/directory/v1/guides/delegation In enterprise applications you may want to programmatically access a user's data without any manual authorization on their part. In G Suite domains, the domain administrator can grant third-party applications with domain-wide access to its users' data — this is ...


Only top voted, non community-wiki answers of a minimum length are eligible