When a row is deleted from a sheet, the rows below it get renumbered even as the script continues to run. If the script subsequently tries to also delete those rows, the result is unpredictable. For this reason, when deleting rows one should proceed from bottom to top. In your case, like so: for (var … Read more
There are 2 patterns for retrieving submitted values. For both patterns, the function for retrieving the values from form submission has to be installed as a trigger. The detail information of Installable Triggers is https://developers.google.com/apps-script/guides/triggers/installable. 1. Script is opened on spreadsheet. In this case, by installing a trigger, you can retrieve the submitted values by … Read more
I found the solution that partially works, but it may not be applicable to other software. The steps below are for Visual Studio Code: Install the NPM package containing type definitions for GAS using https://www.npmjs.com/package/@types/google-apps-script In your locally-saved script, create a ‘.js’ file and type import ‘google-apps-script’;
Starting point: function openDialog() { var html = HtmlService.createHtmlOutputFromFile(‘export’); SpreadsheetApp.getUi() .setWidth(640) .setHeight(400) .showModalDialog(html, ‘Export’); } Docs: https://developers.google.com/apps-script/guides/html/
The other answer, to set the format to ‘plain text’ in javascript, doesn’t work. However, this does: sheet.getRange(1,n).setNumberFormat(‘@STRING@’); So the magic value for formatting text programmatically is ‘@STRING@’!
I thought that I would throw in a similar issue that I had which brought me to this question, where I received the error You don’t have permission to call by openById. In my case I was trying to call functions from translate.gs which I copied from this example: https://developers.google.com/apps-script/quickstart/docs Note that at the top … Read more
This is done using the setFormula for a selected cell. Below is an example of how to do this. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(“B5”); cell.setFormula(“=SUM(B3:B4)”); You can also use setFormulaR1C1 to create R1C1 notation formulas. Example below. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = … Read more
Found these docu on the google docu pages: row — int — top row of the range column — int— leftmost column of the range optNumRows — int — number of rows in the range. optNumColumns — int — number of columns in the range In your example, you would get (if you picked the … Read more
//Sets the row color depending on the value in the “Status” column. function setRowColors() { var range = SpreadsheetApp.getActiveSheet().getDataRange(); var statusColumnOffset = getStatusColumnOffset(); for (var i = range.getRow(); i < range.getLastRow(); i++) { rowRange = range.offset(i, 0, 1); status = rowRange.offset(0, statusColumnOffset).getValue(); if (status == ‘Completed’) { rowRange.setBackgroundColor(“#99CC99”); } else if (status == ‘In Progress’) … Read more
Unfortunately changing the owner of a file isn’t supported in Apps Script. Issue 74 is a feature request to add this ability, and if you star the issue you’ll show your support for the feature and get notified of updates. ——EDITED—— Now there is a handy method called setOwner, which can be found here: https://developers.google.com/apps-script/reference/drive/file#setOwner(User) … Read more