Generating Excel keys: duplicating/inserting cells among current columns for 1500 sets of data

I have the challenge of aligning different subtypes of data from two separate sets next to each other. Currently, each set of data is in its own rows with 1500 columns. To intersperse them, I've been manually moving cells from below up in between the columns of the above dataset (and copying the title over both columns). However, I'm exploring using the "record" functionality to create a script/macro that can automate this process. The script needs to iterate over the entire sheet, which currently extends up until column BHT (but will grow as more columns are added). I'm struggling with looping through ranges based on letter values and instructing the computer to "repeat for the next column."

Here's the current script:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Insert copied cells from EC17:EC25 on selectedSheet to DW2:DW10 on selectedSheet.
    selectedSheet.getRange("DW2:DW10").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("DW2:DW10").copyFrom(selectedSheet.getRange("EC17:EC25"));
    // Paste to range DV1:DW1 on selectedSheet from range EC16 on selectedSheet
    selectedSheet.getRange("EC16").moveTo(selectedSheet.getRange("DV1:DW1"));
    // Paste to range DW1 on selectedSheet from range DV1 on selectedSheet
    selectedSheet.getRange("DW1").copyFrom(selectedSheet.getRange("DV1"), ExcelScript.RangeCopyType.all, false, false);
    // Set range DV1:DW1 on selectedSheet
    selectedSheet.getRange("DV1:DW1").setValues([["YMEL1,1","YMEL1,2"]]);
    // Clear ExcelScript.ClearApplyTo.contents from range EC17:EC25 on selectedSheet
    selectedSheet.getRange("EC17:EC25").clear(ExcelScript.ClearApplyTo.contents);

}

I also came across code on this site that uses the following formula:

=IF(RIGHT($A2, 1) = "Z", CHAR(CODE(LEFT(A2, 1)) + 1), LEFT(A2, 1)) & CHAR(65 + MOD(CODE(RIGHT(A2, 1)) + 1 - 65, 26))

This formula updates letters, but I am unsure how to incorporate this letter iteration into loops that update both letters in the range for both the source and destination ranges...

javascriptexceltypescriptexcel-formulaoffice-scripts

Answer №1

  1. Issue with ExcelScript on macOS and Ubuntu: Running an ExcelScript for a Next.js application is causing issues specifically on Ubuntu while it works fine on macOS. The error message from Ubuntu is not clearly stated, but we will provide a solution based on the provided ExcelScript logic.

  2. Solution Approach: The ExcelScript appears to involve incrementing characters, such as changing "AZ" to "BA". To integrate this functionality into the ExcelScript for updating source and destination ranges, a function to determine the next column in sequence may be necessary.

  3. Updating Script: An improved script includes a function to retrieve the following column name sequentially. See the modified script below:

    function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    let sourceRangeStart = "EC17";
    let sourceRangeEnd = "EC25";
    let destinationRangeStart = getNextColumnName("DW2");
    let destinationRangeEnd = getNextColumnName("DW10");

    // Insert copied cells from source range on selectedSheet to destination range on selectedSheet.
    selectedSheet.getRange(destinationRangeStart + ":" + destinationRangeEnd).insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange(destinationRangeStart + ":" + destinationRangeEnd).copyFrom(selectedSheet.getRange(sourceRangeStart + ":" + sourceRangeEnd));

    // Additional steps based on your logic
    // ...

}

// Helper function to get next column name
function getNextColumnName(column: string): string {
    let lastChar = column.slice(-1);
    let nextChar = String.fromCharCode(lastChar.charCodeAt(0) + 1);
    if (nextChar > "Z") {
        nextChar = "A";
        let prevChar = column.slice(0, -1);
        return String.fromCharCode(prevChar.charCodeAt(0) + 1) + nextChar;
    } else {
        return column.slice(0, -1) + nextChar;
    }
}

    Note that the helper function getNextColumnName increments the column name and assumes a 2-character column name. Adjustments may be needed for names like AAA.

    If there are specific error messages on Ubuntu, providing them would aid in troubleshooting any platform-specific issues or unaddressed dependencies.

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

The connection between MongoClient and Express has been established successfully, however, an error occcurred: TypeError: Cannot read the property 'collection' of null

I am currently working with the MongoClient instead of mongoose, but I am facing an issue where I can't seem to set a new collection in my routes file. db/index.js const {MongoClient} = require('mongodb'); const MONGO_DB_NAME = 'mooo ...

javascriptnode.jsmongodbexpress

Ways to halt streaming without shutting down the Node.js server

I am currently facing an issue with closing a Twitter stream, as it causes my server to crash and requires a restart. Is there a way to close the stream without affecting the Nodejs (express) server? Here is the error message I am encountering: file:///mnt ...

javascriptnode.jsexpress

`The console is displaying incorrect results when returning a JSON object`

Need to extract the full address as well as the lat and long values from the provided JSON data. var place = { "address_components": [{ "long_name": "17", "short_name": "17", "types": [ "street_number" ] }, ... ...

javascriptjqueryjsongoogle-maps

React Native component that enables users to both input their own text and select options from a dropdown menu as they type

Looking to develop a component that combines Text Input and FlatList to capture user input from both manual entry and a dropdown list. Has anyone successfully created a similar setup? I'm facing challenges in making it happen. Here's the scenari ...

typescriptreact-nativeuse-statereact-native-flatlistreact-native-textinput

Trouble Deciphering JSON Array Using Eval Function

I'm facing an issue with two files in my project - one is a PHP file containing an array that is echoed using json_encode, and the other is a JavaScript file containing various functions for a webpage. One particular function in the JavaScript file is ...

javascriptajaxjsoneval

The functionality of smooth scrolling is not compatible with the overflow-y property

Looking to implement a smooth scroll feature, I came across an example online and tried to adapt it. Here's the code I'm working with: https://jsfiddle.net/4DcNH/144/ I've added specific conditions to the html and body elements (changing t ...

javascriptjqueryhtmlcss

Issue with BrowserRouter, improperly looping through the array using map

Encountering an issue with importing content in my React app project using <BrowserRouter>. Within the app, there are 3 Material-UI Tabs: /lights, /animations, /settings. <Switch> <Route path="/lights" component={LightsMenu} /> ...

javascriptreactjsmaterial-ui

Guide for accessing an array property from an observable-wrapped class in Angular

I am currently facing a challenge in filling a dropdown menu by retrieving an array property from a class that is encapsulated within an observable. Here is an example of my interface: export interface IApplicationConfigurationResponse { settings?: Set ...

javascriptangularrxjsrxjs-observablesangular15

Seeking assistance with coding a beginner-level Google Chrome extension

Currently, I am working on developing a basic Google Chrome extension with 2 or 3 browser actions. I have been using Selenium IDE to capture the necessary steps in Firefox that I need for my project. However, I am unsure of how to translate these recorde ...

javascripthtmlgoogle-chromeseleniumgoogle-chrome-extension

What could be causing my JavaScript code to not function properly in an HTML document?

Hello, I am a beginner in the world of coding and currently delving into web development. Recently, I was following a tutorial on creating a hamburger menu but I seem to be encountering some issues with the JavaScript code. I have double-checked my Visual ...

javascripthtmlcssvisual-studio

Tabulator and its convenient scrollable column feature allows for easy navigation

In case my tabulator column is exceeding its length, is there a way to enable scroll functionality for that specific column? Although the resizable rows feature permits users to resize and view all the content, can a scrollbar be implemented exclusively ...

javascriptjqueryhtmlbootstrap-4tabulator

Continuous Scrolling with Callback Function in jQuery

I have implemented the infinite-scroll plugin, which replaces traditional pagination with ajax to fetch new pages. The issue I am facing is that jQuery functions do not recognize the new posts, causing certain functions like the ones below to stop working ...

javascriptjqueryajaxinfinite-scroll

JavaScript is claiming that my class method is invalid, despite the fact that it is obviously a valid function

Implementing a genetic algorithm using node has been my latest challenge. After browsing through the existing questions on this topic, I couldn't find anything relevant to my issue. The problem arises when I attempt to call the determine_fitness metho ...

javascriptnode.js

Tips for sending a div value that is not included in the model to the backend controller action

Question: I am looking to have a single div element in the user interface that can display a value added via JQuery and then be accessible in the backend once the form is submitted. View: @model ScheduleAptMV using (Html.BeginForm()) ...

javascriptjquery.netasp.net-mvcrazor

The Google Maps API is throwing an error because it cannot access the property 'nativeElement' of an undefined object

I'm currently using Google Maps and I want to display the map only if a certain condition is true. HTML: <div *ngIf="anyboolToShowMapOrNot" #map id="map"></div> Here's my TypeScript code: @ViewChild("map",{static: true}) mapEle ...

angulartypescriptionic-frameworkmaps

provide a hyperlink to the icon located in front of the navigation bar

I'm struggling to come up with a suitable title for this issue. What I am trying to achieve is placing a small icon in front of the navbar so that visitors can click on it and be directed to another site. Initially, I attempted to place the icon using ...

javascriptjqueryhtmlcsstwitter-bootstrap

Incorporating an image prior to the "contains" term with the help of JavaScript

Seeking assistance with the code snippet below without altering the text targeted in my "a:contains" statement. The challenge lies in determining the appropriate placement of ::before in the script provided. Link: https://jsfiddle.net/onw7aqem/ ...

javascripthtmljquerycssajax

Displaying infowindow pop-ups on random markers on Google Maps every 3 seconds

Here lies the challenge. I am tasked with creating a Google map that displays multiple markers. Each marker must have a unique info window with distinct content. Upon opening the website, an info window randomly appears on one of the markers after 3 sec ...

javascriptgoogle-mapsgoogle-maps-markers

Exploring Error Handling in AngularJS and How to Use $exceptionHandler

When it comes to the documentation of Angular 1 for $exceptionHandler, it states: Any uncaught exception in angular expressions is passed to this service. https://code.angularjs.org/1.3.20/docs/api/ng/service/$exceptionHandler However, I have noticed ...

javascriptangularjsexception

Developing dynamic checkbox components using jQuery - unusual behavior in Internet Explorer

I am dynamically creating checkbox elements using jQuery and appending them to a specified node as shown below var topics = ['All','Cat1','Cat2']; var topicContainer = $('ul#someElementId'); $.each( topics, functio ...

javascriptjqueryinternet-explorer