This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.
When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround. In this workaround, Google Apps Script is used.
When the following sample script of Google Apps Script is run, a dialog is opened. And, when the CSV file is selected from your local PC on the dialog, the CSV file is loaded, and then, the CSV data is put to the active cell with keeping the number formats of cells.
The quoted post description explains everything you need about this solution and worth clicking through to see how the code works.
Automatically move a row of data from one Google Sheet tab to another when a tickbox is selected.
Archive a row of data by ticking a box
The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.
The tool uses an ‘onEdit(e) simple trigger’ to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
This post introduces a library that can summarize selected columns from multiple sheets across multiple spreadsheets into 1 spreadsheet, as well as to optionally apply a public/private key data encryption scheme to selected columns, all via a simple JSON definition file.
Bruce Mcpherson continues to be one of my favourite Google Apps Script authors and often you get to gain an insight into his development process. This latest post is the latest in a series that follows a post on Super simple cipher library for Apps Script encryption and decryption. The source post shared here continues this journey, in particular, highlighting the use of Google Sheets Developer Metadata to store data bound to the spreadsheet.
🤯 The fact that this was done in Sheets, without Apps Script, blew my mind. Studying the formulas taught me things I never knew, like recursive formulas and using checkboxes to force recalculations. https://t.co/kPIWsQgUEn
Wordle clones are causing a bit of a ruckus lately, so I tried my hand at making one in #GoogleSheets!
Well if you count Google Sheet formulas as code, not quite ‘no code’ but never the less very impressive. You can find more work from the creator Tyler Robertson in this portfolio of work which are all “created entirely within Sheets’s built-in features (meaning there’s no Apps Script or Extensions)”
An Add-on to perform a Google Drive file name search from within a Google Sheet to keep the file information saved for future use.
Search Drive file names in Google Sheets
It is easy to search for the names of files within Google Drive, but what happens when there are a lot of results that you need to go through?! Scrolling continuously down the page becomes quite clunky and frustrating, so with this tool you can perform that same search in a Google Sheet but have the results collated into the Sheet also, so they are much easier to then work with.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
The team at MacMillan Search has generated a lot of value by combining automated STAT reports with Google Sheets through the script they share in this post. From adding ranking details to other tool’s outputs to giving the content teams up-to-date “People Also Ask” reports, the end result has proven to be a great time-saver in our week-to-week SEO workflows by reducing manual work and providing standard outputs that easily integrate with any spreadsheet.
I always enjoy dipping into content from the SEO community as it has some really creative and innovative solutions. This example from MacMillian Search is a use case I’m familiar with, getting data out of a system as an email report with .CSV attachment and using Google Apps Script to do all the automation to update a Google Sheet. If you are not in SEO still worth a read as the pattern used here may come in handy.
Take control of your inbox by unsubscribing from emails you never have time to read, or have lost interest in. … I opted to use Google Sheets as a centralized place to store emails and their unsubscribe links to make it as user-friendly as possible. This also provided the added benefit of working with Google Apps Script to extract email metadata from Gmail to Google Sheets.
Interesting little Apps Script project for those interested in tidying up your inbox and also an opportunity to try a bit of python. Alternatively you might want to have a go at modifying this solution to also extract unsubscribe links with Google Apps Script
The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.
Display Apps Script code directly in a HTML popup
This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself – such as an error message from a try/catch.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Move/archive a row of data from one Google Sheet (tab) to another, within the same file, using this Google Workspace Add-on. It will also remember your individual preferences for each file so it is even quicker to use next time around.
New Features January 2022
A new ‘Settings’ page allowing you to select more than just cell values when archiving row data. You can now also select:
> Text formatting: bold, italics, colour, size and style.
> Cell: background colour, Notes and wrapping.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Create a new Sheet tab within an existing Google Sheet file and format a header row (font size, colour, alignment, etc) for appending data to.
Header row
The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:
Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
Reduce the overall number of columns within the Sheet.
Insert 1 row of data to use as the Header row.
Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
Set the Header row background colour, height and column widths.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
