These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet [sic] using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.
In SpreadsheetApp there is the .deleteRow() and .deleteRows(), but when you have a lot of data or non-consecutive rows it can really eat into your Apps Script runtime limit. In searching for a better way of doing this I found this great solution from Kanshi Tanaike on Stack Overflow which uses the Google Sheets Advanced Service to delete multiple rows using one API call!
Using Sheets.Spreadsheets.batchUpdate isn’t just limited to deleting rows and for more examples see the source post which includes examples for hiding and deleting both rows and columns.
Since Google Apps Script was released on August 19th, 2009, it is used by a lot of users. By this, now there are a lot of useful libraries of Google Apps Script (GAS) in all over the world. But when I want to search a GAS library, I always use Google search engine. Unfortunately, in the current stage, the libraries cannot be directly searched by a database. On January 11th, 2020, a proposal for the database of Google Apps Script Library has been proposed by Andrew Roberts. When I have discussing about this with him, I thought that I tried to think of a sample database. So I prepared this…
Google Apps Script turns 13 years old today and in celebration we are highlighting this combined community contribution. As you will see from the source repo commit history this searchable database of Google Apps Script libraries has been around for a while, but given what it represents we thought worth celebrating.
This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.
A common Google Apps Script use case is taking data from a webform and adding it to a Google Sheet. This can sometimes be quite painful has you have to handle the various input types and also write the data to the correct columns. The HtmlFormApp library makes this very straight forward and to illustrate once the library is added to your Apps Script project you can start appending data in a couple of lines of code:
// These are all options.
const obj = {
formData: formData,
spreadsheetId: "###",
sheetName: "###",
sheetId: "###",
folderId: "###",
headerConversion: {"header value of Spreadsheet": "name of HTML input tag",,,},
ignoreHeader: true,
choiceFormat: true,
delimiterOfMultipleAnswers: "\n",
valueAsRaw: true
};
const res = HtmlFormApp.appendFormData(obj);
console.log(res)
Click through to the source link for more details 👇
Redeploying Web Apps without Changing URL of Web Apps for new IDE
A ‘feature’ of the new Google Apps Script editor is managing your script project the deployments. A issue some developers are encountering when updating the published version of their web app is the published url changes. In this gist Kanshi Tanaike describes the problem and highlights how developers can update their published Web App without changing the URL.
Year vs. number of Questioners and Answerers. These all questions include the tag of “google-apps-script” in the tags
Trend of google-apps-script Tag on Stackoverflow 2021
Kanshi Tanaike returns with an updated look at trends in the [google-apps-script] tag on Stackoverflow. A highlight from the report is the continued growth in new questioners, indicating that the Google Apps Script community is continuing to grow.
This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.
The purpose of this contribution from Tanaike is to extend Google Apps Script to interact with certain types of Google Drive files using methods not included in the existing core or advanced services. Features worth noting are:
Google Docs:
Retrieve table width and column width from the table. The tables inserted with the default width are included.
Google Sheets:
Retrieve all images in Google Spreadsheet as an object including the cell range and image blob.
Retrieve all comments in Google Spreadsheet as an object including the cell range and comments.
Insert images in cells of Google Spreadsheet using the image blob.
Create new Google Spreadsheet by setting the custom header and footer.
Microsoft Word:
Retrieve table width and column width.
Microsoft Excel:
Retrieve all values and formulas of the cells.
Retrieve all sheet names.
Retrieve all images as an object including the cell range and image blob.
Retrieve all comments as an object including the cell range and comments.
For more details about this library visit the source on Github
This is a GAS library for copying the rich text with the text styles from Google Document to Google Spreadsheet or from Google Spreadsheet to Google Document using Google Apps Script (GAS). And, also the rich texts in the cells can be converted to HTML format.
Nice little helper library for copying rich text formatting from Google Docs and Sheets. You can view the project README for information on the formats that are currently supported. Personally, the inclusion of the RichTextToHTMLForSpreadsheet method looks particularly useful for converting rich text formatting in Google Sheets cells to HTML for mail merge applications.
This is a sample script for disabling the buttons put on Google Spreadsheet using Google Apps Script. When a script is run by clicking a button on Google Spreadsheet, there is the case that you don’t want to make users run the script in duplicate.
Nice little Google Apps Script snippet that prevents a function assigned to an image or drawing in Google Sheets from being run simultaneously. The clever bit is the code uses Google Apps Script to modify the function assigned to the button/image when it is running.
Kanshi Tanaike is a prolific Google Apps Script developer and we’ve often featured solutions are reports that Tanaike has shared. With this in mind Tanaike’s list of Google Apps Script resources is well worth a browse.
