+ Reply to Thread
Results 1 to 8 of 8

Automate the creation of a new column + data transposition on Google Sheets

  1. #1
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Automate the creation of a new column + data transposition on Google Sheets

    Hi,

    I'm trying to fully automate a marketing report for my company on Google Sheets.
    We have a list of metrics that we track on a monthly basis.
    Each month, new data comes in and I need to manually :
    - Create a new column
    - Paste the values from the column that contains all the formulas to the newly created column.

    Is there any way to automate these two tasks? I've never used macros before but I'm guessing I will need it.
    I attached a sample of my data so you can understand better what I'm trying to do.
    Any help would be appreciated !
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate the creation of a new column + data transposition on Google Sheets

    this Apps Script will:

    * create new column between B and C
    * copy the values from column B into the new column C
    * give the new column C a header title of the current month

    Please Login or Register  to view this content.
    Last edited by janmorris; 03-24-2022 at 05:38 PM.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate the creation of a new column + data transposition on Google Sheets

    Hi janmorris,

    Thanks a lot for your answer. I really appreciate the multiple comments to help me understand the script.
    Never used Apps Script before so it is really helpful.

    I'v tried running the script but I got stuck at "var sheet = ss.getSheetByName('MyReportName');"
    Debugger => ss: null / Sheet: Undefined / date: undefined / valuestocopy: undefined

    Do I need to specify that my Sheet is running on one specific folder of my Google Drive? Or is there some authorizations I need to grant?

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate the creation of a new column + data transposition on Google Sheets

    After you put the Apps Script, just save it, run it, authorise it.
    Then test it (run it again).
    There is no need to debug unless you have problems.
    And usually the error messages will give much better idea of the problem than the debugger.

    I tested this script on the sample file you provided so there should not be any issues.
    Last edited by janmorris; 03-25-2022 at 08:32 AM.

  5. #5
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate the creation of a new column + data transposition on Google Sheets

    For easy of use, i would recommend you run the script by either attaching it to a button… or by adding this Custom Menu script to the top of your Apps script file (above the code i already gave you):

    Please Login or Register  to view this content.
    Last edited by janmorris; 03-25-2022 at 09:02 AM.

  6. #6
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate the creation of a new column + data transposition on Google Sheets

    Hi Janmorris,

    It was actually an issue with my web browser. I'm using Brave Browser and for some reasons the script was not linked to my active spreadsheet. So spreadsheet.getactivespreadsheet() was null.
    It works fine on other browsers.
    Thank you so much for your help, it will help me save a lot of time !

    Just one last thing on your latest comment: I try to add the Admin Menu on the top of the script but it didn't work.
    I got the message "Script function not found: addColumn"

    But don't worry about it, it's already helping me a lot, thanks again for it !

  7. #7
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automate the creation of a new column + data transposition on Google Sheets

    Oh, sorry, change the script to:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-24-2022
    Location
    Bangkok
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Automate the creation of a new column + data transposition on Google Sheets

    Thanks a ton, it works just fine now!
    Hope you have a great week ahead.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] automate creation of sheets in excel
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 12:05 PM
  2. [SOLVED] automate creation of sheets in excel
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  3. automate creation of sheets in excel
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  4. automate creation of sheets in excel
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  5. [SOLVED] automate creation of sheets in excel
    By Daniel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. automate creation of sheets in excel
    By Daniel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. automate creation of sheets in excel
    By Daniel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. [SOLVED] HOW DO I AUTOMATE CREATION OF JOB SHEETS?
    By bobby smith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2005, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1