1

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.

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

Is there any way to insert a column where column C is (shifting data to the right) ?

Yes, there is. From https://developers.google.com/apps-script/reference/spreadsheet/sheet

  • insertColumnAfter(afterPosition)
    • Inserts a column after the given column position. insertColumnBefore(beforePosition)
    • Inserts a column before the given column position.
  • insertColumns(columnIndex)
    • Inserts a blank column in a sheet at the specified location.

Is there any way to work on the whole range (C15-50) as opposed to doing it one cell at a time?

Yes, there is. From https://developers.google.com/apps-script/reference/spreadsheet/sheet

  • getRange(row, column, numRows, numColumns)
    • Returns the range with the top left cell at the given coordinates with the given number of rows and columns.
  • getRange(a1Notation)
    • Returns the range as specified in A1 notation or R1C1 notation.

It's worth to note that getRange(a1Notation) could be a single cell reference or a "multiple cell reference", like 'C15:C50'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.