Executing a Google Script will result in a newly inserted column automatically adopting the

My coding issue involves a simple scenario where cell A1 contains a date, for example 06/06/2017.

What I aim to do is add three columns and apply formulas to them. I insert columns using the following code:

ss.insertColumns(1,3);

Subsequently, I set formulas in the three new columns:

ss.getRange(1,1).setFormula('=MONTH(D1);
ss.getRange(1,2).setFormula('=WEEKNUM(D1);
ss.getRange(1,3).setFormula('=CHOOSE(weekday(D1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")');

However, I encounter an issue where the new columns inherit the formatting from the original A columns (based on my limited understanding of Google Script). This results in the new A1 cell displaying 05/01/1900. When I choose Format > Number > Number, it displays 6. The number is correct, but the formatting is incorrect.

How can I resolve this problem? It's important to note that the actual file contains hundreds of rows of dates, and I utilize loops to assign the formulas.

javascriptgoogle-apps-scriptgoogle-sheets

Answer №1

When working with Google Sheets, you have the ability to not only set the content or formula of a cell, but also the number format. For example, instead of just setting a formula like 

ss.getRange(1,1).setFormula('=MONTH(D1)');

you can enhance it by also specifying the number format, like this:

ss.getRange(1,1).setFormula('=MONTH(D1)').setNumberFormat("0")

The formatting options available with .setNumberFormat(numberFormat) correspond directly with those you would find in Sheets itself. For more details, you can refer to the official documentation.

As a more efficient approach, especially considering your other related tasks, instead of setting each formula individually, you can set multiple formulas at once with a single call:

ss.getRange(1,1,1,3)
.setFormulas([
    '=MONTH(D1)',
    '=WEEKNUM(D1)',
    '=CHOOSE( weekday(D1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")'])
.setNumberFormats(["0","0","text"]);

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 for me to transform this code into a useful helper function?

I am looking to optimize this conditional code by converting it into a helper function using a switch statement instead of multiple if statements. How can I achieve this in a separate file and then import it back into my component seamlessly? import { ...

javascriptreactjsvue.jshelper

The HTML image is not updating, the function does not appear to be triggering

My attempt to add a source to an image using JavaScript and some jQuery code I found online isn't working as expected: <html> <head> <script src = "http://www.example.com/images/"> var count=1; var initnumber=100 ...

javascriptjqueryhtml

"Drag and drop elements that automatically snap to a grid and move with

I'm trying to create a small, square div that follows the mouse cursor, but I want it to snap to a 3x3 pixel grid. Here is what I have so far: $(document).mousemove(function(e) { window.x = e.pageX; window.y = e.pageY; if(window.x % 9 === 0 ){ ...

javascriptjquerycss

What is the best location to manage errors within a sequelize ORM query?

I am working with the Sequelize ORM within a Node/Express environment. My database has two tables: one for Users and another for Items. The Item table includes a foreign key that is linked to the UserId in the User table. Whenever I attempt to create an ...

javascriptexpresssequelize.js

Leveraging React useEffect for retrieving data and managing component behavior

Having trouble querying data, setting it to state with useEffect, and rendering the results? I've tried a few things but only see changes when state is updated. Any advice would be greatly appreciated. Thank you in advance. useEffect // fetchCamp ...

javascriptreactjsnext.jsaxios

How can we convert unpredictable-length JSON user input into well-structured HTML code?

Welcome to the world of web development! I am currently embarking on a project where I aim to transform JSON data into HTML structures. Specifically, I am working on creating a dynamic menu for a restaurant that can be easily updated using a JSON file. The ...

javascriptjqueryhtmlarraysjson

Exploring the capabilities of the meteor autocomplete package by mizzao

I recently started working with Javascript and Meteor and I'm facing some issues with the Meteor autocomplete package from Mizzau. While I am able to get the form auto complete feature to work, I'm struggling to filter my todos accordingly. My ma ...

javascriptmeteorautocomplete

How can we use Mongoose .find to search with an array stored in req.params and respond with an array containing each value along with its

It would be great if a user could input multiple tags in a search field, and have them separated client-side (before making the .get call) to send over ajax with each keypress. While testing the API with Postman on the server-side, if the .get method retu ...

javascriptarraysnode.jsexpressmongoose

Converting a variety of form fields containing dynamic values into floating point numbers

Trying to parse the input fields with a specific class has presented a challenge. Only the value of the first field is being parsed and copied to the other fields. https://i.stack.imgur.com/QE9mP.png <?php foreach($income as $inc): ?> <input ty ...

javascriptjquery

When using selenium-python, every attempt to click a button consistently results in an error message

I've been trying to use Python-Selenium binding to click a button, but I haven't had any luck with various selectors so far. I'm currently using Chromedriver. Although I can select an element using elem = driver.find_element(by='xpath& ...

javascriptpythonseleniumgwt

A guide on utilizing a function import within an exported function within ReactJS

As a beginner in React, I have been exploring Named and Default Exports, but I encountered a problem that I am having trouble articulating. Below is the code that is causing confusion: namedExport.js const name = "xyz"; const age = 20; const my ...

javascriptreactjs

Tips for searching an array in mongodb

Trying to implement a filtering mechanism on an array to query my MongoDB database Experimented with the elemMatch method to match exactly with the query, but encountered issues. Below is the schema for my shipment: const mongoose = require('mongoo ...

javascriptnode.jsmongodbmongodb-query

Unable to find module reference "three" at 137

Returning to an older project, I realized that nothing was loading. When I checked the console log, this is what I found: Uncaught TypeError: Failed to resolve module specifier "three". Relative references must start with either "/", "./", or "../". In my ...

javascriptthree.jsskypack-cdn

Oops! An error occurred while trying to find the _mongodb._tcp.blog-cluster-0hb5z.mongodb.net. Please check your query settings and try again

My free Mongo Atlas cluster suddenly stopped connecting, even though everything was working fine before. Strangely, I can see that data has been collected on the MongoDB website. It's puzzling why this issue occurred and now my entire site won't ...

javascriptnode.jsdatabasemongodbmongodb-atlas

React.js - keeping old array intact while using array map

I am currently experiencing an issue where I have two arrays, and I need to map through one of the arrays when navigating the page. However, instead of replacing the old array with the new one, it is just keeping the old array and adding the new one. Here ...

javascriptreactjsnext.js

When there is an expensive calculation following a Vue virtual DOM update, the update may not be

Currently, I am facing an issue with adding a loading screen to my app during some heavy data hashing and deciphering processes that take around 2-3 seconds to complete. Interestingly, when I removed the resource-intensive parts, the screen loads immediate ...

javascriptasynchronousvue.js

Is your blockui overlay failing to cover the entire page?

I have implemented blockui to display a "Wait ... loading" popup on my webpage. It is mostly working fine, but I am facing a small issue where the overlay does not cover the entire width of the scroll window when I scroll right (although it covers the full ...

javascriptjqueryasp.net

Finding the initial occurrence of duplicates within an array of objects using JavaScript

I am working with a data structure retrieved from an API, and it looks like this: [ { id: '10000844', text_id: '10000844-01', }, { id: '10000844', text_id: '10000844-02', }, { id: ' ...

javascriptarraysdictionaryobjectunique

Warning message regarding unhandled promise rejection in NestJS websockets

I've encountered a puzzling issue while attempting to integrate an 'events' module into my application to utilize websockets. Upon adding the module, an unexpected error surfaced: (node:59905) UnhandledPromiseRejectionWarning: Unhandled pro ...

javascriptnode.jstypescriptwebsocketnestjs

Received undefined instead of a Promise or value from the function in Nodemailer

I'm currently exploring cloud functions and trying to implement email notifications for document creation triggers in Firestore. I found a helpful tutorial that guided me through the process, but I encountered an error while analyzing the cloud functi ...

javascriptfirebasegoogle-cloud-platformgoogle-cloud-firestoregoogle-cloud-functions