CODEWITHSUNDEEP
javascriptgoogle-apps-script
Karan Saraogi
Karan Saraogi

Reputation: 15

Transfer Data from one sheet to another and deleting old data in google sheets

I have 3 sheets. https://docs.google.com/spreadsheets/d/1jObb1jHIOV6LDXMrCHriSP2ywlOi9rDhwwNw5QYPcDk/edit#gid=2082210284

Audit: Filled via a google form and would be the master sheet In Progress: Only Repair cars are inputted and would be accessed. Completed: All Repair Cars that are completed.

If a car has been repaired, the in progress sheet would say "completed". Which should then remove this car from the In Progress and move it to the Completed. I have currently added a helper sheet which identifies the cells of the "completed value", edits it in the audit form and accordingly filters the value.

But I'm facing a few issues.

  1. Google Script Error: Exception: This action would increase the number of cells in the workbook above the limit of 5000000 cells. removecompleted @ Code.gs:35

  2. Automating the google script to run everytime the column D in Sheet "in Progress" is changed.

    function removecompleted() {
      var SHEET_NAME = "Audit";
      var SHEET_NAME_2 = "In Progress";
      var row_delete = SpreadsheetApp.getActiveSheet().getRange(5, 2).getValue();
      var row_update = SpreadsheetApp.getActiveSheet().getRange(6, 2).getValue();
      var col_delete = SpreadsheetApp.getActiveSheet().getRange(5, 3).getValue();
      var col_update = SpreadsheetApp.getActiveSheet().getRange(6, 3).getValue();
      if (row_delete !== 0) {
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME_2).getRange(row_delete, col_delete).clear();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getRange(row_update, col_update).setValue("Completed");
       }
     }

Upvotes: 0

Views: 265

Answers (1)

iansedano
iansedano

Reputation: 6481

The reason for the error:

These lines:

  var row_delete = SpreadsheetApp.getActiveSheet().getRange(5, 2).getValue();
  var row_update = SpreadsheetApp.getActiveSheet().getRange(6, 2).getValue();
  var col_delete = SpreadsheetApp.getActiveSheet().getRange(5, 3).getValue();
  var col_update = SpreadsheetApp.getActiveSheet().getRange(6, 3).getValue();

End up with these values:

row_delete = Wed Jun 02 2021 00:00:00 GMT+0530 (India Standard Time)
row_update = Fri Jan 01 2021 00:00:00 GMT+0530 (India Standard Time)
col_delete = 1452
col_update = 1531

The first two are Date objects.

In case you are not aware, getRange(), if you use it with two arguments, takes the row and then the column. So you are getting:

  • B5
  • B6
  • C5
  • C6

So when you use these values to getRange() again. You will get an error.

SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME_2).getRange(row_delete,col_delete).clear();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getRange(row_update,col_update).setValue("Completed");

Specifically, since you are trying to give getRange a Date object, I believe it is being converted to a number, which will end up with NaN (Not a number) which sheets seems to interpret as being infinity. This is why you are getting the error:

This action would increase the number of cells in the workbook above the limit of 5000000 cells

I would recommend using Logger.log() or console.log() to debug your script to find out the variables contain the values you think they do.

var row_delete = SpreadsheetApp.getActiveSheet().getRange(5, 2).getValue();
var row_update = SpreadsheetApp.getActiveSheet().getRange(6, 2).getValue();
var col_delete = SpreadsheetApp.getActiveSheet().getRange(5, 3).getValue();
var col_update = SpreadsheetApp.getActiveSheet().getRange(6, 3).getValue();
console.log(row_delete, row_update, col_delete, col_update)

How to make your script run when an edit is made

You can use an onEdit trigger, more information here:

https://developers.google.com/apps-script/guides/triggers

Reference

Upvotes: 0

Related Questions