JavaScript: Utilizing numbers to extend a sequence

My code is saved in a Google Sheets document and here is how it looks:

ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "10 Rows", functionName: "TenRows"},
                      {name: "20 Rows", functionName: "TwentyRows"},
                      {name: "30 Rows", functionName: "ThirtyRows"},
                      {name: "40 Rows", functionName: "FortyRows"},
                      {name: "50 Rows", functionName: "FiftyRows"}]                      
   ss.addMenu("Insert Rows", menuEntries);
 }


 function TenRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,10);
 }

 function TwentyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,20);
 }

 function ThirtyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,30);
 }

 function FortyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,40);
 }

 function FiftyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,50);
 }

In column C of my spreadsheet, there are numbers in each row in descending order like this:

 C
7317
7316
7315
7314
7313

​When I execute my script to insert rows, how can I ensure that the ascending number sequence continues in column C automatically?

Thanks

javascriptgoogle-apps-scriptgoogle-sheets

Answer №1

I can provide guidance on solving the issue, although I cannot share the code without reviewing the entire context:

  1. Prior to adding new rows, store the value from the row where the new rows will be inserted in a variable, for example, 
    lastNumber = parseInt(insertRowColumnC.getValue())
    . Ensure to use parseInt to convert the string to a number.
  2. Insert the new rows.
  3. Iterate through all the newly inserted rows from top to bottom.
  4. For each row, decrement lastNumber by one, and then write its value into column C on that row.

Update

After examining the spreadsheet, I dedicated some time and developed this code snippet:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  if (r.getColumn() == 3 && r.getRow() == 2) {
    var value = parseInt(r.getValue());
    if (!isNaN(value)) {
      var next = getFirstRowNumber(sheet);
      if (value > next) {
        var count = value - next;
        sheet.insertRowsAfter(2, count);
        for(var i = 2; i < 2 + count; i++) {
          sheet.getRange(i + 1, 3).setValue(value);
          value--;
        }
        r.setValue('');

      } else {
         r.setValue('');
         Browser.msgBox('You need to enter a greater ID.');
      }
    } else if (r.getValue() !== '') {
      r.setValue('');
      Browser.msgBox('You need to enter a valid ID.');
    }
  }
}

Your inquiry was somewhat vague, but this code functions as desired.

Whenever a new ID is entered in 2/C, the missing IDs between the new ID and the ID in 3/C are automatically inserted.

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

Is it possible to make a div jump or bounce if it has a flex display?

I'm looking to add some interactive animation to an image inside a div when my mouse hovers over it. To better explain the issue I'm facing, I created a quick representation of what I want to achieve in the code below. My goal is to have the te ...

javascriptjqueryhtmlcssflexbox

The modification of HTML styles

How can I change the style (width, color etc) of all 8 functions like this? function my(){document.getElementById("question1").innerHTML="THIS QUESTION"+ "<br>" +"<button onclick=answer1() id=ques1 >first answer</button>" +"<button ...

javascripthtml

When using JavaScript with React, setting a value after the onBlur event can result in users having to click

In my React form, I have an input button with an onBlur event that sets the value to a useState property. However, after the onBlur event is triggered, the user has to click the button twice to focus on it properly. It seems like the page needs time to pro ...

javascriptreactjs

"NextAuth encounters an issue while trying to fetch the API endpoint: req.body

Trying to implement authentication in my Next.js app using NextAuth.js, I've encountered an issue with the fetching process. Here's the code snippet from the documentation: authorize: async (credentials, req) => { const res = await fetch ...

javascriptreactjsnext.jsnext-auth

Any idea how to dynamically insert rows and columns into a table or div element?

Can anyone assist me with this task? I have successfully completed the visual process I intended to do. How can I achieve this structure using AngularJS? The data will be stored in Json format. When the "+" symbol is clicked in a certain direction, the fi ...

javascriptjqueryangularjsjson

Tips for fetching integer numbers in a string in JavaScript in sequential order starting from the left side

Within an input element, users are required to input the price of a product. <input size=10 type="text" id="prd_price" title="prd_price" name="prd_price"> Users have the ability to enter Currency Symbols along with other characters, and we cannot ...

javascripthtmlinputnumberssequence

Jquery: Pressing Enter will cause the input field to lose

Take a look at this fiddle I created: http://jsfiddle.net/7wp9rs2s/. This is the progress I have made on my project so far. In the fiddle above, you can double click on one of the 4 items and a textbox will appear for editing. Instead of clicking out of t ...

javascriptjquery

Ways to release a client-side script with npm?

Within my nodejs package, I have included code that can be executed on both the backend and in a single .js file for browsers. In order to utilize the browser script, it must be placed within a script element in an HTML file. My query pertains to whether t ...

javascriptnode.jsnpm

An unexpected { token error was encountered by Jest while running a React application

I am facing a persistent issue that I just can't seem to resolve. Despite trying numerous solutions found online, nothing seems to fix it. Below are the configurations: package.json { "scripts": { "test": "jest --no-cache", ...

javascriptreactjsjestjsbabeljsbabel-jest

When using node.js and express, attempting to send an email with the packages 'nodemailer' and 'nodemailer-handlebars' may result in a TypeError being thrown, specifically [ERR_INVALID_ARG_TYPE]

I am encountering an issue while attempting to send an email using an HTML template located in the 'view' folder within the same path. The HTML template is named 'index.handlebars'. Despite believing that the path is correct, I am recei ...

javascriptnode.jsexpresshandlebars.jsnodemailer

How to return a variable to Express when clicking a button?

Imagine having a list of 10 elements, each with a unique id as their name, and the user has the ability to add more items to the list at any time. If I were to click on an element, my goal is to have Express retrieve the id of that specific element. If it ...

javascriptjquerynode.jsexpress

Developing web applications using a combination of PHP, MySQL, and

I am in need of creating an HTML form that functions as CRUD. This form should be able to record inputs such as "row_number", "channel_name", and "ip_address". It will store all the data in a MySQL table using the "ORDER BY row_number" function. The form i ...

javascriptphphtml

Guide on displaying JSON information upon clicking using JavaScript

I'm having difficulty writing the logic for this code. I have extracted data from a vast API. The current code fetches all program titles (some may be repeated) and compares them with an array of late night shows, then displays them once in their own ...

javascripthtmlarraysjsonget

Leveraging ng-class with an Angular $scope attribute

My HTML structure includes: <div class="myDiv"> <div style="width:200px; height:200px;background-image:url('img/200x200/{{largeImg}}.png');" ng-class="{'magictime foolishIn': 1}"> <span> { ...

javascriptjquerycssangularjs

Passing a MySQL connection to scripts in Express

After setting up the mysql connection with all the required parameters in app.js, is there a way to make it accessible to other scripts in routes/ without having to redeclare or require the mysql parameters again, simply by using client.query(..)? ...

javascriptnode.jsexpress

Is it possible to invoke a Javascript function from a coffeescript file?

Struggling to invoke a javascript function from a Coffeescript file within my $(document).ready(), but it seems like the function is not being executed. The function I intend to call originates from an external source that I have included in the head sect ...

javascriptjqueryruby-on-railscoffeescript

Live monitor database changes with AJAX and SQL technology

Is there a way to implement real-time updating of user ratings on comments in an application with a connected database? I've explored various sources but the responses have been inconsistent and inconclusive. I am currently creating an app where user ...

phpjavascriptsqlmysqlajax

What is the best way to automatically adjust the size of this HTML Menu to match the width of its

I am in the process of converting a Drupal 6 theme to Drupal 7 and I'm encountering some difficulties with this particular section. Below is the HTML code snippet: <ul id="nav" class=" scaling-active scaling-ready"> <li><a href="/demos ...

javascriptjqueryhtmlcssdrupal

Unable to locate the <router-view> component within VueRouter

I recently completed a project using Vue.js 3.2.13 and Vue-Router 4.0.14. Despite my efforts to ensure everything was set up correctly, I encountered an error in the browser that said "[Vue warn]: Failed to resolve component: router-view". The specific lin ...

javascriptvue.jsvue-routervuejs3vue-router4

Implementing a node.js application deployment with pm2 to ensure zero downtime

While there are countless tutorials on developing chat applications using socket.io and node.js, the event-driven advantage of Node is undeniable for building chat apps. However, a recent thought crossed my mind - how can I ensure the sustainability of my ...

javascriptnode.jsexpresssocket.iopm2