#google-apps-script

Merge and Unmerge Multiple Cells in Google Sheet

Looks like a beginners problem, but some solutions were not obvious for me.

Lets see 2 cases: (1) merge cells, (2) unmege (break apart) cells. I mean merge and unmerge multiple cells at once, with a few clicks, and without losing your data or formatting.

Merge cells Add-ons to merge cells

The add-on to merge cells by Ablebits called “Merge Values”. Interesting, there’s no alternative…

Script Magic. Create HTML-form with list from Sheet & search, like Google.

If you need to create list like this:

Please copy this sample file. And see my Git-Hub Repo for details.

The list (1) works from custom menu, (2) contains values from your sheet, or any values, (3) supports single or multiple selections, (4) supports search by letters, (5) returns selected values to the script when the “Submit” button is clicked.

👨‍🎓 Hi! I've managed to use Web-Hook to put my new WordPress posts into Spreadsheet.

👨‍🎓 Hi! I’ve managed to use Web-Hook to put my new WordPress posts into Spreadsheet.

Here’s how it looks:

One new row is added when I publish or update a post on WordPress

My Sample file is here: COPY.

Here’s how to steps:

Create new Google Sheets file and the code inside.

Publish an App

Create a Web-Hook with WordPress

All steps will take ~5 minutes.

Step 1. The code

The code…

Family Expenses App. Glideapps + Google Script. Part 2 of 2

This is the sequel, the original post is here:  Family Expenses App. Glideapps + Google Script. Part 1 of 2.

Modify Finanso.

Modification points are:

Create your own user tabs (or one tab if you’re single)

Modify categories for your expenses.

Lets’ shortly recall the scheme:

GlideApps Google-Apps-Script _______________^_______________ _________^_________ | | | | 📱Finanso App ←·→ 📗Finanso File…

I’m glad to share the app for family finances. Meet Finanso! Our family is happy using Finanso for 3 months so far.

Finanso is powered by Glideapps.

Glideapps = App+Sheets

Glideapps ensures the data is synchronized between spreadsheets and the App. When I enter the data to my phone, I see it in Google Sheet and vice verse.

GlideApps _______________^_______________ | | 📱 Mobile App ←·→ 📗 Google Sheet

Glideapps is a no-code constructor for mobile apps. It allows to create and develop a fully customizable mobile app:

#gallery-0-9 { margin: auto; } #gallery-0-9 .gallery-item { float: left; margin-top: 10px; text-align: center; width: 33%; } #gallery-0-9 img { border: 2px solid #cfcfcf; } #gallery-0-9 .gallery-caption { margin-left: 0; } /* see gallery_shortcode() in wp-includes/media.php */

Easy to change the app

Easy to share

Easy to install

Easy to change all, even the icon

–

Their beautiful web-interface lets change every little detail of the app, even the icon. I love it! 😍

Free App with Glideapps

Anyone can create as many free apps as she wants. The limit for free usage is 500 rows per app. 

Quiz: Create a Free App Limit: 500 rows of data, according to Pricing Growth:  My real data is growing with the speed ~4 rows a day Time left: We have about 4 monthes to exceed the limit Task: Create a free app. How?

I thought of limiting the amount of data in the app. My needs were: daily expenses and the report. I wanted to copy my daily data to a separate spreadsheet. I did not know the real amount of data at the time and decided to clear the data daily. Happily, no need to copy and modify data manually, Google Script does it for me:

GlideApps Google-Apps-Script _______________^_______________ _________^__________ | | | | 📱Finanso App ←·→ 📗Finanso Sheet ←-----·→ 📗Fananso Backup Sheet

Every night the Script does his job. The script reads the data we entered, converts it into tabular form, copies it to the Backup file, clears entered data, and creates the report.

The limit was 500 rows, I’ve done with 24:

My Finanso app

I’ve spent about 5 hours on developing Finanso. You may see the result and create your own copy, of course (see how in the next section)!

Finanso has two separate tabs for my wife (Lu) and me (Max):

Here’s a sample usage of Finanso. You spend $500 and need to enter the expense. Your actions are:

go to your tab: ( Max for me, but could be your name),

tap on category: Gifts,

fill in Sum (500) and Comment (For Mum)

You’re done ✔️

There are 5 slots for Sums and Comments. We’re usually alright with 1-3 entries per category, so 5 must be enough.

You’ll need to enter every expense into Finanso. As a reward, you’ll have a report like this:

The report!

The report adjusts every 24 hours. It is always up-to-date, showing the last 30 days stats. The expenses are sorted by sum. The total is shown at the top. Each row is clickable.

You’ll also have the backup — the Spreadsheet with all your data:

Separate data table: my Backup Spreadsheet

Install Finanso

Installation is not simple yet, I’m working on it 👷‍♂️…  If you’ we read so far, then you may be still interested in your own Finanso app (I hope so). Please follow these 2 steps to setup:

Copy the app & linked file.

Copy the backup file and script and create a trigger

Step 1. Copy Finanso App

To get the app, follow this link on your PC:

https://finanso.glideapp.io

(be sure to open the PC version) You’ll see the page like this:

↑ Click COPY THIS APP button $) You’ll need to create a new Glideapps account.

When you copy the app, you’ll be redirected to your GlideApps editor:

↑ The place where you create and modify the app.

You’ll see the newly created app, your copy of Finanso. This copy will belong to your account, and no one else will have access yet.

You may then easily modify and share it. Please see the guide here: https://www.glideapps.com/learn-to-glide

One more thing, you’ll also have a copy of the linked Google Spreadsheet. Please open it via editor:

Click Edit sheet in the left panel of the editor to see the file. This will open the spreadsheet in a new tab:

Linked Spreadsheet

↑ This file will be also a part of your Google account, so you could also find it in your Google Drive. Please copy the ID of this file, we’ll need it later. To copy the id, go to the browser URL:

You’ll need this ID in step 2.

Step 2. Copy the backup file and launch the code

To get the backup file, follow the link:

Copy Finanso Backup (Spreadsheet + Code)

As you start using the app, please delete the data from sheets: \Data/ and \Dates/. These sheets contain sample data, and you’re going to create your own!

↑ The Backup file contains dummy data. Please delete it.

Now you’ll need the ID from Step1. It was an ID of the linked file. Paste this ID to the sheet \_Jobs_/ to the cell B2:

Sheet called “_Jobs_”

Now the script is ready to go. You’ll need to launch the script to run on a daily basis. Go to menu Tools > Script Editor to see the code (or use a keyboard [Alt → T → E] in Chrome).

↑ A new tab with the code

The code is here, but it does nothing yet. You’ll need to create a trigger. Click on the timer icon (1) and create a new trigger (➕).  The function of a trigger is called test_Jobs (2). Please use these settings:

Your own app is ready now! The bad news is that your copy is currently running on the same tabs as mine. You may ensure it if you go to your linked Spreadsheet, you’ll see 2 tabs: Lu and Max. You may also notice that the app has the same 2 tabs: Lu and Max.

I assumed that your family needs the same 2 tabs and you’re ok to name them Lu and Max. It is not true of course! I also assumed that your expenses have the same categories: Food, Life, Party, Gifts, Health, Clothes, Rent, and Other. This may be true, but I doubt it.

Let’s fix this inconvenience and modify Finanso to your needs. I’ll cover the engine details and modification points in Part 2.

Cascade Dependant drop-down lists

Hello!

One of the most needed features of tables is creating dependent drop-down lists, like these:

List 1 is the main for list 2. List 2 is the main for lists 3 and 4. I’ve already supposed one possible solution. And here’s why I suggest you this one, too.

Problems solved:

Limit on 500 items for the list is not the case now: cascade lists are based on a range, not an array.

The speed of the…

How to create 30+ Google Sheet files with dummy data using Google Apps Script.

If you need 30 or more files with dummy data, with different numbers of rows, please see this solution. Think of your own set of files for tests, like this:

Import a big amount of sheets with IMPORTRANGE formula

Import a big amount of sheets with Google-Apps-Script

See what method gives the best performance.

If you…

These are the google form fields:-

1. Timestamp 2. Title 3. Venue 4. Date 5. Start Time 6. End Time 7. Participants 8. Note for this meeting 9. EventId

Reference:

https://code.google.com/p/google-apps-script-issues/issues/detail?id=574

http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/

In Script Editor:

//this is the ID of the calendar to add the event to, this is found on the calendar settings page of the calendar in question

var calendarId = "[email protected]"; var formUrl = "https://docs.google.com/forms/d/xxxxx-yyyyy/edit"; //below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed) var formTimeStampId = 1; var titleId = 2; var locId = 3; var dateId = 4; var startTimeId = 5; var endTimeId = 6; var descId = 7; var calId = 9;

function getLatestAndSubmitToCalendar(e) {  Logger.log("[METHOD] onFormSubmit" + e);

 // get the correct data set  var sheet = SpreadsheetApp.getActiveSheet();  var rows = sheet.getDataRange();  var numRows = rows.getNumRows();  var values = rows.getValues();

 // get last inserted row  var lastrow = rows.getLastRow();

 Logger.log("responses: " + responses);  createCalendarEventForRow(sheet, lastRow); }

function updateAllRow() {

 // get the correct data set  var sheet = SpreadsheetApp.getActiveSheet();  var rows = sheet.getDataRange();  var numRows = rows.getNumRows();

 for (var row = 2; row < numRows; row++) {    createCalendarEventForRow(sheet, row);  }

}

function createCalendarEventForRow(sheet, row) {  var lastrow = row + "";  // get properties of events  var m_date = sheet.getRange(lastrow,dateId,1,1).getValue();  var _range = sheet.getRange(lastrow,startTimeId,1,1);  var m_starttime =  sheet.getRange(lastrow,startTimeId,1,1).getValue().toLocaleTimeString().split(" ")[0];  var m_endtime = sheet.getRange(lastrow,endTimeId,1,1).getValue().toLocaleTimeString().split(" ")[0];;

 // Get the date value in the spreadsheet's timezone.  var spreadsheetTimezone = sheet.getParent().getSpreadsheetTimeZone();  Logger.log("Script Timezone: " + Session.getScriptTimeZone());  Logger.log("SpreadSheet Timezone: " + spreadsheetTimezone);

 // get response link to include in the event description  var formTimeStamp = new Date(sheet.getRange(lastrow,formTimeStampId,1,1).getValue());  var responseUrl = getEditResponseUrl(formTimeStamp);

 var startTime  = joinDateAndTime_(m_date, m_starttime, spreadsheetTimezone);  var endTime = joinDateAndTime_(m_date, m_endtime, spreadsheetTimezone);  var location =  sheet.getRange(lastrow,locId,1,1).getValue();  var subOn = "Submitted on :"+sheet.getRange(lastrow,formTimeStampId,1,1).getValue();  var desc = "Added by :"+sheet.getRange(lastrow,descId,1,1).getValue()+"\n"+subOn + "\nEdit meeting: <a href=\"" + responseUrl + "\">"+responseUrl+"</a>";  var title = sheet.getRange(lastrow,titleId,1,1).getValue();

 // check if update  var eventId = sheet.getRange(lastrow,calId,1,1).getValue().split("@");

 if (eventId != "") {    updateEvent(calendarId,eventId[0], title, startTime, endTime, desc, location);  } else {    // create or update event    eventId = createEvent(calendarId,title, startTime, endTime, desc, location);    Logger.log("[METHOD] event created " + eventId);    sheet.getRange(lastrow,calId,1,1).setValue(eventId);  }   }

function updateEvent(calendarId, eventId, title,startTime,endTime,desc, loc) {    var existingEvent = Calendar.Events.get(calendarId, eventId);    Logger.log("[Object] eventId " + eventId);    Logger.log("[Object] event " +  existingEvent);    existingEvent.setSummary(title);    existingEvent.setLocation(loc);    existingEvent.start.dateTime = startTime.toISOString();    existingEvent.end.dateTime= endTime.toISOString();    existingEvent.description = desc;    try {      Calendar.Events.update(existingEvent, calendarId, eventId, existingEvent);    } catch (e) {      Logger.log(e);    }    Logger.log("[METHOD] event updated " + existingEvent.getId());    return existingEvent.getId(); }

function createEvent(calendarId,title,startTime,endTime,desc, loc) { var cal = CalendarApp.getCalendarById(calendarId); var start = new Date(startTime); var end = new Date(endTime); var event = cal.createEvent(title, start, end, {description : desc, location : loc});  return event.getId(); };

function getEditResponseUrl(formTimeStamp) {  var form = FormApp.openByUrl(formUrl);  var responses = form.getResponses(formTimeStamp);  Logger.log("Response length " + responses.length); //  var response = responses[responses.length-1];  var response = responses[0];  var lasturl =  response.getEditResponseUrl();  Logger.log("lasturl " + lasturl);  return lasturl; }

/** * Creates a single Date object from separate date and time cells. * * @param {Date} date A Date object from which to extract the date. * @param {Date} time A Date object from which to extract the time. * @return {Date} A Date object representing the combined date and time. */ function joinDateAndTime_(date, time, spreadsheetTimezone) {  var _time  = time.split(":"); // in HH:mm:ss format  var _date = new Date(date);  //_date.setHours(getValueAsHours(time, spreadsheetTimezone));  //_date.setMinutes(getValueAsMinutes(time, spreadsheetTimezone));  _date.setHours(_time[0]);  _date.setMinutes(_time[1]);  return _date; }

function onEdit(e){  // Set a comment on the edited cell to indicate when it was changed.  var range = e.range;  range.setNote('Last modified: ' + new Date()); }

function getValueAsSeconds(date,spreadsheetTimezone) { //  var dateString = Utilities.formatDate(value, spreadsheetTimezone, //      'EEE, d MMM yyyy HH:mm:ss'); //  var date = new Date(dateString);

 // Initialize the date of the epoch.  var epoch = new Date('Dec 30, 1899 00:00:00');

 // Calculate the number of milliseconds between the epoch and the value.  var diff = date.getTime() - epoch.getTime();

 // Convert the milliseconds to seconds and return.  return Math.round(diff / 1000); }

function getValueAsMinutes(range) {  return getValueAsSeconds(range) / 60; }