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:
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();
}
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