I have created a Google Sheet for an in game Estate Agency.
The first tab "Residential Appraisal" is the data input form, and with the click of a button I want the data entered (C16:C56) to be archived on "Residential Appraisal Archive" on the next available row.
When the data is pasted it will need to be pasted as transposed and values only (due to formulas).
I am very new to the scripting side of things and have attempted to figure it out myself but got nowhere of any note. Please find the link below to the sheet.
https://docs.google.com/spreadsheets/d/1BF4Ao8L7FfitZFGX7QDfqbthmiu2A7CKXuJw0Av-0jY/edit?usp=sharing
Code:
function Reset_Residential() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('C16:C20').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C22').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C23').activate();
spreadsheet.getCurrentCell().setValue('10');
spreadsheet.getRange('C24').activate();
spreadsheet.getCurrentCell().setValue('Undetermined');
spreadsheet.getRange('C28:C38').activate();
spreadsheet.getActiveRangeList().check();
spreadsheet.getActiveRangeList().uncheck();
spreadsheet.getRange('C39:40').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C45').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C50').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C52').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};
function Reset_Buisness() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('C16:C21').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C23').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C24').activate();
spreadsheet.getCurrentCell().setValue('10');
spreadsheet.getRange('C25').activate();
spreadsheet.getCurrentCell().setValue('Undetermined');
spreadsheet.getRange('C26').activate();
spreadsheet.getCurrentCell().setValue('Please Select');
spreadsheet.getRange('C30:C31').activate();
spreadsheet.getActiveRangeList().check();
spreadsheet.getActiveRangeList().uncheck();
spreadsheet.getRange('C32:C33').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C38').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C43').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('C45').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};
function jumpToFirstEmptyBasic() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Appraisal Archive"); // << Change the sheet name to your own sheet.
var lastRow = sheet.getLastRow();
sheet.getRange(lastRow 1,1).activate();
}
function Residential_Archive() {
const ss = SpreadsheetApp.getActive();
const sourceRange = ss.getRange('Residential Appraisal!C16:C56');
const targetRange = ss.getRange('Results Historic!' ss.getRange('Audit correction!Y3').getValue());
sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
showMessage_('Values copied.');
}
CodePudding user response：
I believe your goal is as follows.
- You want to copy the values from the cells "C16:C56" of "Residential Appraisal" sheet to the sheet "Residential Appraisal Archive". At this time, you want to copy only the values without including the formulas.
- You want to run the script when a button is clicked.
When I saw your sheets of "Residential Appraisal" and "Residential Appraisal Archive", I could confirm the cells "C16:C56" of "Residential Appraisal", and I also confirmed that the sheet "Residential Appraisal Archive" has the empty columns of "F", "J", "L", "Z", "AF", and "AH". In this case, I thought that the values can be directly retrieved from the cells "C16:C56" and copy them to the destination sheet.
And also, I noticed that in your Spreadsheet, there are 2 pairs of source and destination sheets.
- source sheet: "Residential Appraisal", destination sheet: "Residential Appraisal Archive"
- source sheet: "Buisness Appraisal", destination sheet: "Buisness Appraisal Archive"
I thought that you might have wanted to use this script for the above 2 pairs. So in this answer, I would like to propose a sample script that can use these 2 pairs.
Sample script:
Please copy and paste the following script to the script editor of the Spreadsheet. And, please assign the function name of "sample" to the "Archive" button on the sheets of "Residential Appraisal" and "Buisness Appraisal". By this, when you click the button at "Residential Appraisal", the values are copied to the sheet "Residential Appraisal Archive". And, when you click the button at "Buisness Appraisal", the values are copied to the sheet "Buisness Appraisal Archive".
function sample() {
const obj = [
{srcSheetName: "Residential Appraisal", dstSheetName: "Residential Appraisal Archive"},
{srcSheetName: "Buisness Appraisal", dstSheetName: "Buisness Appraisal Archive"}
];
const ss = SpreadsheetApp.getActiveSpreadsheet();
const currentSheet = ss.getActiveSheet().getSheetName();
const sheet = obj.filter(({srcSheetName}) => srcSheetName == currentSheet);
if (sheet.length == 0) return;
const src = ss.getSheetByName(currentSheet);
const dst = ss.getSheetByName(sheet[0].dstSheetName);
src.getRange("C16:C56").copyTo(dst.getRange(dst.getLastRow() 1, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
}
Note:
- If you don't want to use this script at the sheet "Buisness Appraisal", please remove the function name from the button on the sheet "Buisness Appraisal".