Hello in my below link i have a user form were i can add, edit and delete a user.
Spreadsheet Link https://docs.google.com/spreadsheets/d/1tyz_3WeEkmY4WX8Xvx38nkLow8hcbCjPMLS1-NiPFgo/edit?usp=sharing
Dev Link https://script.google.com/macros/s/AKfycbxIT4fJPNyD5U2CKFzWCSho0tR2uuczq1hevzHawtg/dev
So now all the above fields like name, email, date of birth will be saved and can be re-edited.
I added 2 more column below Phone Number & Country
Daily Updates
and Daily Logs
Now when i edit a user and update today's activity in Daily Updates
like Something added on - 1-jan-2021
Now it should clear field in Daily Updates
and should be updated in Daily Logs field
.
If i update Something added on 2-jan-2021
in Daily Updates
again it should be cleared and appear in Daily Logs
field as previous and current
Something added on - 1-jan-2021
Something added on 2-jan-2021
So my goal is display all changes done to Daily Updates
field should be appear in Daily Logs
field as read only this can't be edited.
CodePudding user response:
I believe your goal is as follows.
- When the value is loaded by editing data from "Contact Details Database" and inputting the value to "Daily Updates" and clicking "Submit" button, you want to save the value to "Daily Logs" and want to clear "Daily Updates".
In this case, how about the following modification?
Modified script:
In this case, please modify the function updateData
in Google Apps Script side as follows.
From:
function updateData(values, spreadsheetId, range) {
var valueRange = Sheets.newValueRange();
valueRange.values = values;
var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, { valueInputOption: "RAW" });}
To:
function updateData(values, spreadsheetId, range) {
var len = values[0].length;
values[0][len - 1] = values[0][len - 1] ? `${values[0][len - 1]}\n${values[0][len - 2]}` : values[0][len - 2];
values[0][len - 2] = "";
var valueRange = Sheets.newValueRange();
valueRange.values = values;
var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, { valueInputOption: "RAW" });
}
- By this modification, when the data is updated, the value of "Daily Updates" is saved to "Daily Logs" as the additional value and "Daily Updates" is cleared.
Note:
- From
Daily Logs field as read only this can't be edited.
, in this case, please putreadonly="readonly"
to the textarea tag of "Daily Logs".
Added:
From the following reply,
IF i add more fields like Last name , job field, etc,,, i keep adding extra columns in spreadsheet.
In this case, how aobut using the column number as follows?
To:
function updateData(values, spreadsheetId, range) {
var colDailyChanges = 8; // Column "H"
var colDailyLogs = 9; // Column "I"
values[0][colDailyLogs - 1] = values[0][colDailyLogs - 1] ? `${values[0][colDailyLogs - 1]}\n${values[0][colDailyChanges - 1]}` : values[0][colDailyChanges - 1];
values[0][colDailyChanges - 1] = "";
var valueRange = Sheets.newValueRange();
valueRange.values = values;
var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, { valueInputOption: "RAW" });
}