CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Avan logic
Avan logic

Reputation: 17

Copying multiple non empty rows to a new sheet

I am trying to save the daily feedback that i get from a website from the "feedback" sheet into another google sheet that i called "Feedback Database"

Column A: the Date Column B: Time Column C: The email address Column D: the feedback

The data rows start from row 3.

What i am currently doing to save the data based on the basic knowledge that i have is:

  1. copying all the data in row 3 in the "feedback" sheet
  2. Saving it to the "database" sheet
  3. Getting back to the "feedback" sheet and deleting row 3 so that data in row 4 becomes in row 3.

I have another function that count the number of rows and run the saveFeedback_Data() function based on the number of rows.

I know that what i am doing in step 3 isn't a proper way, I would really appreciate it if you guys recommend a better way to perform this function with a commentary in order to understand.

Appreciate your help,

    function saveFeedback_Data() {
      
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('feedback'), true);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];
    
      
    
      var  Date= sheet.getRange('feedback !A3').getValue(); 
      var Time = sheet.getRange('feedback !B3').getValue();
      var Email = sheet.getRange('feedback !C3').getValue();
      var Feedback = sheet.getRange('feedback !D3').getValue();
      
      
      var sheet_dest = ss.getSheetByName("Database");
      sheet_dest.appendRow([Date,Time, Email,Feedback]);

    
       Logger.log('Issue Date : ' + Date + ' Issue time : ' + Time + ' Email Address : ' + Email + ' Feedback : ' + Feedback + '\n');
      
    
      //now deleting the row 

      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('feedback'), true);
      spreadsheet.getRange('3:3').activate();
      spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
      spreadsheet.getRange('A10').activate();

}

Upvotes: 0

Views: 55

Answers (1)

Cooper
Cooper

Reputation: 64082

Save Feedback Data

This will run about ten times faster

function saveFeedback_Data() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Feedback');
  const shsr = 3;
  const dbsh = ss.getSheetByName('Database');
  const rg = sh.getRange(shsr, 1, sh.getLastRow() - shsr + 1, 4);
  const vs = rg.getValues().filter(r => !r.every(e => !e));//gets all non blank rows at one time
  dbsh.getRange(dbsh.getLastRow() + 1, 1, vs.length, vs[0].length).setValues(vs);//moves all data at one time
  rg.clearContent();//clears all data at one time
}

You can goto to Google Apps Script Reference and using the search box find any function that you don't understand. If it's a pure JavaScript function the go here

Upvotes: 1

Related Questions