Back

Technologies:

javascriptjavascript
avatar
Tolerim
a month ago

My goal is to transfer a fixed column and rows relative to it to the last row of another tab/sheet within the same spreadsheet, using app script.

I need to transfer fixed column and relative rows to another sheet/tabs within the same spreadsheet and clear the data range from the source sheet by utilizing app script. The code has executed, but the target sheet isn't displaying the transferred range.

Here's the code snippet:

function moveData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const invoiceDb = ss.getSheetByName("Invoice_DB");
  const invoiceDbLastRow = invoiceDb.getLastRow();

  let lastRow = sheet.getLastRow();
  let rangeValue = sheet.getSheetValues(5,1,lastRow,7);

  let invoiceDbCounter = 1

  for (var i = 5; i <= rangeValue.length; i++){

  let value = sheet.getRange(i,1,1,7).getValues();
  invoiceDb.getRange(invoiceDbLastRow + invoiceDbCounter,1,1,7).setValues(value);
  }
}
The source sheet is shown below, indicating the complete range to be moved to another sheet while clearing all data.

Source Sheet

The transferred range is intended to be displayed within the target sheet, which can be accessed through the following link:

Target Sheet

I have recently started working with AppScript, and I would appreciate any assistance in getting the code to function correctly.

Thank you very much!

Answers(2)
avatar
Vogi
a month ago
Verified Answer
Thank you for sharing your code snippet and providing a screenshot of your source and target sheets. Based on your code snippet, it appears that you are trying to move data from one sheet to another within the same spreadsheet while clearing all data from the source sheet using Google Apps Script. I noticed that you have defined a variable named rangeValue which contains the values of a range of cells in your source sheet. However, I couldn't find where this variable is used in your code snippet. To move data from one sheet to another within the same spreadsheet using Google Apps Script, you can use the setValues() method. This method sets a rectangular grid of values (two-dimensional array) starting at a given cell. Here's an updated version of your code snippet that should work: 
function moveData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const invoiceDb = ss.getSheetByName("Invoice_DB");
  const invoiceDbLastRow = invoiceDb.getLastRow();

  let lastRow = sheet.getLastRow();
  let rangeValue = sheet.getSheetValues(5,1,lastRow,7);

  let invoiceDbCounter = 1

  for (var i = 0; i < rangeValue.length; i++){

    invoiceDb.getRange(invoiceDbLastRow + invoiceDbCounter,1,1,7).setValues([rangeValue[i]]);
    invoiceDbCounter++;
  }

  sheet.getRange(5,1,lastRow,7).clearContent();
}
In this updated version of your code snippet: - The setValues() method is used to set the values of each row in rangeValue to the target sheet. - The clearContent() method is used to clear all data from the source sheet. I hope this helps! Let me know if you have any further questions or concerns.

Sources

How to Copy a Row to another Sheet with Google Apps ScriptJump from one spreadsheet to another using Google App ScriptCopy or move rows to another sheet automaticallyGoogle Apps Script: Move data between spreadsheetsGoogle Apps Script - Copy data to different worksheet and appendExtending Google Sheets | Apps Script | Google Developers
avatar
Tolerim
a month ago
It looks like you are trying to move data from one sheet to another in a Google Spreadsheet using Google Apps Script. However, you mentioned that the range wasn't showing up in the target sheet. One potential issue with your code is that you are starting your for loop at index 5, which may be skipping over the first 4 rows of data. Try starting your loop at index 1 instead: 
for (var i = 1; i <= rangeValue.length; i++){
  let value = sheet.getRange(i,1,1,7).getValues();
  invoiceDb.getRange(invoiceDbLastRow + invoiceDbCounter,1,1,7).setValues(value);
  invoiceDbCounter++;
}
Additionally, you mentioned that you want to clear the range from the initial sheet after moving the data. You can achieve this by using the clearContent() method on the range that you want to clear: 
let range = sheet.getRange(5,1,lastRow,7);
range.clearContent();
Put that code after the for loop and it will clear the content of the range after the data has been moved to the other sheet. Hope this helps! Let me know if you have any other questions.
;