Google Script to copy the values of a column and insert a new column in spreadsheet

I have started creating a spreadsheet to monitor some results from facebook, youtube etc… A lot of things I did I was able to learn from previous answers on this site.

Now I am stuck with trying to find a script which can help me do the following:

Column A contains the description of the metric
Column B contains the formula which pulls the metric from extrenal sources (e.g. number of likes of my facbeook page, number of video views of my YT channel)

I would like to have a script which runs every Monday and performs the following actions:

Insert a new column between column B and column C (moving all columns after B down by 1 place)
Copy column B and paste only the values (not the formulas) in the new empty column C
Write something like Week Commencing YYYY/MM/DD on top of Column C (C1) where YYYY/MM/DD is the date of the previous week’s monday

I have tried a modified version of this (from http://www.kraukoblog.com/marketing/tutorial-watch-your-competitors-facebook-pages-with-google-docs/):

function UpdateFblikes() {
  var sheet =SpreadsheetApp.getActiveSheet(); //define spreadsheet name
  var column = sheet.getRange("O1").getValue();
  var row = 4;
   Utilities.sleep(4000);  // Slowdown the script (4 second)
   var nblike = sheet.getRange("E4:E60"); // Select and copy the column
   nblike.copyValuesToRange(sheet, column+5, column+5, row, row+56); // Paste the data
  sheet.getRange(3, column+5).setValue(new Date()); // add date in column title
   sheet.getRange("O1").setValue(column+1);  // Incremente variable
}

JavaScript
​x
 
function UpdateFblikes() {
  var sheet =SpreadsheetApp.getActiveSheet(); //define spreadsheet name
  var column = sheet.getRange("O1").getValue();
  var row = 4;
   Utilities.sleep(4000);  // Slowdown the script (4 second)
   var nblike = sheet.getRange("E4:E60"); // Select and copy the column
   nblike.copyValuesToRange(sheet, column+5, column+5, row, row+56); // Paste the data
  sheet.getRange(3, column+5).setValue(new Date()); // add date in column title
   sheet.getRange("O1").setValue(column+1);  // Incremente variable
}
​

But it only writes the date in the column.

This would enable me to compare the weekly changes in those metrics.

Is this possible at all?

Answer

I have been able to find the solution by navigating through different tutorials on google’s developers page. This is the function I have created

function insertcolumn() {
    // The code below will insert 1 column after column 5 in sheet 4
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[4];
    sheet.insertColumns(5, 1); 
    
      
    // Get the range of cells that store data to be copied having created a named range for the column in the spreadsheet.
      var valuestocopy = ss.getRangeByName("valuestocopy")
      
    // The code below copies the range valuestocopy cells to column five up to 100 rows
    
    valuestocopy.copyValuesToRange(sheet, 5, 5, 1, 100); 
}

JavaScript
 
function insertcolumn() {
    // The code below will insert 1 column after column 5 in sheet 4
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[4];
    sheet.insertColumns(5, 1); 
    
      
    // Get the range of cells that store data to be copied having created a named range for the column in the spreadsheet.
      var valuestocopy = ss.getRangeByName("valuestocopy")
      
    // The code below copies the range valuestocopy cells to column five up to 100 rows
    
    valuestocopy.copyValuesToRange(sheet, 5, 5, 1, 100); 
}
​

Advertisement

Answer