Google-sheets – Copy one range to another column, then clear the original range

google sheetsgoogle-apps-script

I have a Google Sheets that is updated daily with new inventory data. Currently I have to insert a column, copy and paste the data from A15-50 into C15-50.

Is there any way to insert a column where column C is (shifting data to the right), copy A15-A50 to C15-50 so that yesterday's data goes into a different column (C), and (A) gets cleared out so new data can be input for the day?

Currently I have to have all data cleared from column C or it will paste the data after any data in the column. ie: C15-50 is clear, but C51 has data so the data posts in C52. I am currently copying one cell at a time using:

function copypasta() {
var sourceSheet = "Sheet1";
var sourceCell = "B15";
var destinationSheet = "Sheet1";
var destinationCell = "C15"; 
var ss = SpreadsheetApp.getActive();
var sourceValue = ss.getSheetByName(sourceSheet).getRange(sourceCell).getValue();
var destSheet = ss.getSheetByName(destinationSheet);

var destRange = destSheet.getRange(destinationCell);

var destRow = destRange.getRow();

var destCol = destRange.getColumn();

var destValues = destSheet.getRange(destRow,destCol,Math.max(1,destSheet.getLastRow()-destRow+1)).getValues();

 for (var i=destValues.length-1;i>=0;--i) {    if (destValues[i]) break;   }
 if (destValues[i]!="") i=i+1;   destSheet.getRange(destRow+i,destCol).setValue(sourceValue);   }

then:

 function clearOrder() {
  SpreadsheetApp.getActiveSheet().getRange('C15').setValue('');
}

Could I change the script to overwrite the data that is in column C?
Is there any way to work on the whole range (C15-50) as opposed to doing it one cell at a time?

I have a little scripting experience but I would consider myself an amateur.

Best Answer

var sourceRange = sourceSheet.getRange("A15:A50");
var destRange=sourceSheet.getRange("C15:C50"); 
sourceRange.copyTo(destRange);
sourceRange.clearContent();

Related Solutions

Google-sheets – Clear range of cells

You can clear only the cells with orange background (color code #ff9900) by getting the background colors of all cells with .getBackgrounds() and then checking them for being orange.

function clearOrange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  for (var i=0; i<bgColors.length; i++) {
    for (var j=0; j<bgColors[i].length; j++) {
      if (bgColors[i][j] === '#ff9900') {
        range.getCell(i+1,j+1).clearContent();
      }
    }
  }  
}

I knew the color code for orange used in Google sheets because I first ran this code with the "if" statement replaced by Logger.log(bgColors[i][j]);

The reason for (i+1,j+1) is that indices in JavaScript are 0-based while row and column numbers are 1-based.

Hypothetically speaking, if you didn't have orange background as the criterion for clearing, you'd have to specify the ranges explicitly; the script has to know what to clear. You could still shorten the script by putting the ranges in an array, as below.

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var rangesToClear = ['D5:D12', 'D15:D20', 'D22:D30'];
  for (var i=0; i<rangesToClear.length; i++) { 
    sheet.getRange(rangesToClear[i]).clearContent();
  }
}

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google-sheets – Clear range of cells

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

Related Topic