My Spreadsheet has two Sub Sheets "Sheet 1" and "Sheet 2". Column A on "Sheet 1" receives new data every month. (old data is overwritten) I need script to automatically copy Column A data once a month and move it to "sheet 2" Of course on "sheet 2" Column A would be the landing spot for this month and next month the landing spot would be Column B and so on, so that a history is preserved.

I thought I could to this... but didn't realize just how much I didn't know. Any help is appreciated

`function storeData(){

  var ss = SpreadsheetApp.openById('ID'); // Select Spreadsheet by ID
  var sheet = ss.getSheetByName('Sheet 1'); // Select your Sheet by its Name
  var datarange = sheet.getDataRange(A1:A150);`

this is as far I can get

CodePudding user response：

Suggestions:

• var ss = SpreadsheetApp.openById('ID')
  • you don't need to reference by ID if everything is happening in the active spreadsheet
• var sheet = ss.getSheetByName('Sheet 1')
  • maybe you sheet name is "Sheet 1", but the default sheet names don't contain a space. Just make sure of the name of your sheet.
• var datarange = sheet.getDataRange(A1:A150);
  • you don't add a specific range when you use getDataRange. BUT if you put ANY data in any column other than Column A OR if the data in Column A isn't contiguous, then you should use a different method to declare the source range. For example: var targetRange = target.getRange(1,targetLC,sourceValues.length)
• the Logger.log statements have been left in to help with debugging, but once your script is working OK you can escape them.
• Google is your friend. Use StackOverflow (and other resources) to find answers to questions like your own.

function storeData(){

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var source = ss.getSheetByName('Sheet1')
  var sourceRange = source.getDataRange()
  var sourceValues = sourceRange.getValues()
  Logger.log(sourceValues)
  Logger.log("the number of rows of source data = " sourceValues.length)

  var target = ss.getSheetByName("Sheet2")
  // get the last Column on target sheet
  // note: if column A is blank, the last column will = 1
  // BUT if column A has data and column B is blanbk, then the last column will still = 1
  // so need to test whether Column A on the target contains any data
  // we'll use ISBLANK - https://developers.google.com/apps-script/reference/spreadsheet/range#isblank

  var targetA1 = target.getDataRange()
  Logger.log(targetA1.isBlank())
  
  if (targetA1.isBlank() == true){
    // range is blank, so Last Column = 1
    var targetLC = 1
    Logger.log("Column A is blank")
  }
  else{
    // range is not blank, so last column <>1
    var targetLC = target.getLastColumn() 1
    Logger.log("the last column on the target sheet = " targetLC)
  }
  var targetRange = target.getRange(1,targetLC,sourceValues.length)
  Logger.log("The target range = " targetRange.getA1Notation())
  targetRange.setValues(sourceValues)
  Logger.log("copies the data")

}