CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kbee
Kbee

Reputation: 27

Google Appscript: Copy Data from a Form Sheet to Another Sheet in Last Row and Clear Form

Trying to get an Appscript to run but I can't seem to figure out the last few steps.

Background: I have a Google Sheet with two tabs, #1 is called "Data Form" which hosts a fillable form to capture Transaction information to then be input onto tab #2 called "Posted Transactions". This is a personal budget spreadsheet..

Anyways, The script below is intended to take the information input on the "Data Form", verify what the last row with data is on the "Posted Transactions" tab based on whether or not Column A has any data. (To further clarify, the "Posted Transactions" tab has formulas in columns G-I which prohibits me from using a simple "Find last Row" script.)

As it is written now, I receive an error "Exception: The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 1. RecordNewTransaction @ Code.gs:24"

Any suggestions to make this work properly?

UPDATE:

var dataRange = datasheet.getRange(lastRow+1,1,1,datasheet.getLastColumn()-3);

After many attempts at trial and error, I needed to edit the line shown above. Current & full script is performing as expected now and is shown below. Image snips of what I was trying to accomplish are also shown for reference.

function RecordNewTransaction() {
var ss        = SpreadsheetApp.getActiveSpreadsheet();
var formSS    = ss.getSheetByName("Data Form"); //Form Sheet
var datasheet = ss.getSheetByName("Posted Transactions"); //Data 
//Input Values
var values = [[formSS.getRange("B4").getValue(),
               formSS.getRange("B6").getValue(),
               formSS.getRange("B8").getValue(),
               formSS.getRange("B10").getValue(),
               formSS.getRange("B12").getValue(),
               formSS.getRange("B14").getValue()]];

var columnToCheck = datasheet.getRange("A:A").getValues();
var lastRow = getLastRowSpecial(columnToCheck);
Logger.log(lastRow);

var dataRange = datasheet.getRange(lastRow+1,1,1,datasheet.getLastColumn()-3);
Logger.log(dataRange);
var dataValues = dataRange.getValues();

Logger.log(dataValues);

dataRange.setValues(values);
Logger.log(dataRange.setValues(values))

formSS.getRange('B4:B14').clearContent();

};

function getLastRowSpecial(range){
var rowNum = 0;
var blank = false;
for(var row = 0; row < range.length; row++){

  if(range[row][0] === "" && !blank){
    rowNum = row;
    blank = true;
  }else if(range[row][0] !== ""){
    blank = false;
  };
};
return rowNum;
  };

Data Entry Form

Results from Data Entry Form Post on Assigned Sheet

Upvotes: 0

Views: 126

Answers (2)

Kbee
Kbee

Reputation: 27

See edited question above which contains the revised script needed.

The problem here was that columns G-I of my results sheet contain formulas (shown in grey) which required this line below to be modified to grab the last column -3, otherwise the script was looking at too many columns that it didn't need to. Also had to modify "lastRow" to "lastRow+1" because this kept overriding the very last line of data I already had input and the +1 will make new data go to the next available row.

var dataRange = datasheet.getRange(lastRow+1,1,1,datasheet.getLastColumn()-3);

Upvotes: 0

Mike Steelson
Mike Steelson

Reputation: 15308

Try

var dataRange = datasheet.getRange(lastRow,1,values.length,values[0].length)

replace 1 as necessary (this is the firts column where the data will be stored

Reference:

Upvotes: 1

Related Questions