Google Appscript allowing for the return of a Range where certain rows contain merged columns

I need help creating a custom function for Google Sheets using Google AppScript. My goal is to summarize data and display the summary on a different sheet.

I have already developed an algorithm for the summary that returns a range, similar to the following:

Original

https://i.sstatic.net/OpM3G.png

However, I am facing a challenge in merging cells based on specific criteria, such as months. I want to achieve a layout like this:

Desired Outcome

https://i.sstatic.net/fUwnZ.png

In the example above, the range returned by my custom function will look like this:

[
    ['January', ''],
    ['Project 1', '10 Hours'],
    ['Project 2', '20 Hours'],
    ['Project 3', '30 Hours'],
    ['Project 4', '40 Hours'],
    ['Project 5', '50 Hours'],
    ['February', ''],
    ['Project 1', '10 Hours'],
    ['Project 2', '20 Hours'],
    ['Project 3', '30 Hours'],
    ['Project 4', '40 Hours'],
    ['Project 5', '50 Hours']
]

I am unsure how to instruct AppScript to merge cells like ['January', ''], and ['February', ''],. Any suggestions or alternative methods, possibly from Excel functions, are welcome! :)

Thank you!

javascriptgoogle-apps-scriptgoogle-sheets

Answer №1

This code snippet demonstrates how to transfer data from Sheet1 to Sheet2, specifically copying 3 rows and 4 columns.

function copyData() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sourceSheet = ss.getSheets()[0];
  var data = sourceSheet.getDataRange().getValues();   
  var mergeRows = [];  
  for(var i = 0; i < data.length; i++) {    
    if (data[i][1] == "") {
      mergeRows.push(i);      
    }
  }  
  var startRow = 3;  
  var startCol = 4;  
  var targetSheet = ss.getSheets()[1];  
  targetSheet.getRange(startRow, startCol, data.length, data[0].length).setValues(data);  
  for(var i = 0; i < mergeRows.length; i++) {    
    targetSheet.getRange(startRow + mergeRows[i], startCol, 1, 2).merge().setHorizontalAlignment('center');
  }  
}

Answer №2

To combine cells in a spreadsheet, you can utilize the merge() function or the mergeAcross() function. An example would be

sheet.getRange('A1:B1').merge();
sheet.getRange('A7:B7').merge();

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Error TS2307: Module 'calculator' could not be located

Running a Sharepoint Framework project in Visual Studio Code: This is the project structure: https://i.stack.imgur.com/GAlsX.png The files are organized as follows: ComplexCalculator.ts export class ComplexCalculator { public sqr(v1: number): number ...

javascriptjsontypescriptgulpvisual-studio-code

Angular processes the expression as though it were part of the HTML

I need help finding a feature in Angular that can achieve the following task: var data = { name: 'Jane Smith', age: 30 } someFunction('Hey there, {{name}}, you are {{age}} years old', data) // should give output 'Hey there ...

javascriptangularjs

Retrieve the previous URL and showcase it on a webpage using Javascript

I am currently working on a jobs website that has multiple job description pages and one application page with a form. For example: /admin-clark -> /application /sale-rep -> /application My goal is to automatically populate an input field in the f ...

javascriptjquerylocal-storage

How can you switch the property of an object in VueJS?

I am currently working with an array called cars, which contains names of cars as well as a property called starred. My goal is to toggle the value of starred between true and false for each car, ensuring that only one car can have starred set to true at a ...

javascriptvue.jsvuetify.js

Tips for providing support to a website without an internet connection

I am in the process of creating a sales platform for a grocery store that utilizes PHP/MySQL. I have come across some websites that are able to fully reload and function even without internet access. For instance, when I initially visited abc.com, everyth ...

javascriptphp

List of objectives for the JavaScript function

I have a question: How can I incorporate an array of targets into this script? $(document).ready(function () { $('#kategorierbuttons').click(function(e) { var num = $(e.target).index() + 1; $('option:nth-child(' + num + ')& ...

javascriptjqueryarrays

Why isn't my JavaScript function working properly? It should be able to delete and add records without the need for a page refresh using

Hey, can anyone help me figure out what's going wrong with my JavaScript code? It seems to be breaking when I try to add a record without refreshing the page. I was able to completely delete records with some help from experienced users, but now I&ap ...

javascriptjqueryajax

Checking authorization with CognitoUser solely for "code" provided by CognitoUser (npm: amazon-cognito-identity-js)

Currently, I am in the process of implementing the "forget password" feature. However, I have encountered an issue where I would like to verify the "verification code" first before setting a new "password". Unfortunately, the system currently requires bo ...

javascriptamazon-web-servicesauthenticationnpmamazon-cognito

Switching a react virtualized table from JavaScript to TypeScript can uncover some type-related challenges

I have implemented the following demo in my React project: https://codesandbox.io/s/react-virtualized-table-checbox-stackoverflow-rbl0v?fontsize=14&hidenavigation=1&theme=dark&file=/src/App.js However, I am encountering issues with the code sni ...

javascriptreactjstypescript

Synchronous user input within an asynchronous function

I'm currently facing a challenge with implementing a synchronous user prompt in my electron app. Specifically, I have an object that contains a series of commands and template variables. My objective is to substitute all unknown template variables wi ...

javascriptasync-awaitelectronpromptsynchronized

Using the useState hook in a Node.js backend and React frontend: a comprehensive guide

How can I use useState in my HTML file that already uses node.js? const {useState} = React Below is the code snippet: const {useState} = React; function App(){ const {text, setText} = useState("Hello world") console.log(text); function handleClick(){ se ...

javascripthtmlnode.jsreactjsexpress

PHP allows for creating dropdown lists where the values are dynamically dependent on the selection of another dropdown list within the same form

Is there a way for me to implement this solution? The values in the dropdownlist are based on another dropdownlist within the same form. For example, a form with dropdownlists for car names and models of that car, along with a search button. Please take no ...

phpjavascripthtmlajaxdrop-down-menu

The setInterval function in Javascript seems to be malfunctioning

I've been working on this code that should display a tree made up of random symbols. I implemented setInterval to change the symbols every second, but for some reason, it's not working and I'm not getting any error messages. Could someone pl ...

javascript

Is there a restriction on the number of strings allowed in minimist?

Here is the output received from the code provided below. Question input and i are both true as intended, but why aren't project and p? They are defined in exactly the same way as input and i. $ bin/test --input -p { _: [], update: fa ...

javascriptnode.jsecmascript-6

The SimpleHTTPServer is causing Google Maps Places Autocomplete feature to malfunction

Currently, I am implementing a location search feature along with form auto-fill using the google.maps.places.Autocomplete functionality. While accessing the HTML file directly (file:///location.html), everything is functioning as expected. However, when ...

javascriptpythonhtmlgoogle-mapsgoogle-maps-api-3

Determining the existence of a file on a different domain using JavaScript

One of the key tasks I need to achieve from JavaScript on my HTML page is checking for the existence of specific files: http://www.example.com/media/files/file1.mp3 // exists http://www.example.com/media/files/file2.mp3 // doesn't exist Keep in mi ...

javascripthtmlhttp

Several attributes in the JSON object being sent to the MVC controller are missing or have a null

I am facing an issue while passing a JSON object to my MVC controller action via POST. Although the controller action is being called, some elements of the object are showing up as NULL. Specifically, the 'ArticleKey' element is present but the & ...

javascriptjqueryjsonajaxasp.net-mvc

JavaScript validation malfunctioning

Whenever I click on the submit button, nothing happens. An error is displayed as follows: Form.js:102 Uncaught TypeError: Cannot set property 'onsubmit' of null. I am having trouble understanding why this error occurs... // JavaScript Document ...

javascripthtmlcss

The setInterval() function causing unusual overlapping issues

While experimenting with creating a basic text carousel, I encountered a puzzling issue that has me stumped. The structure of the carousel is straightforward. It consists of a wrapper and the actual text to be cycled through. Unfortunately, the problem a ...

javascripthtmlcsssetinterval

Learn how to create a stunning effect by combining two half images and revealing a full image upon hover with a smooth animation

I am struggling with implementing a specific feature using jQuery. I have designed a page hero with two sections (red and black): My goal is to have the black section expand over the red section when hovering, creating a full black box. I want the same ef ...

javascriptjqueryhtmlcss