I have to submit all the data from UserForm to DataSheet (in flat method), even a blank cell also. right now I have almost achieved my task using the below script. But couldn't copy the blank value to Datasheet. Please guide
function SubmitData() {
const dstSpreadsheetId = "1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA"; // Destination Spreadsheet ID (WB-DataSheet).
const dstSheetName = "DataSheet";
const srcSheetName = "UserForm";
// Retrieve values from source sheet and create an array and search value.
const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const ranges = ["C3", "C7", "C8", "D8", "D6", "D4", "E8", "E19", "E20", "E21", "E22", "B10:E18", "B19:C22"];
const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(),{ ranges: ranges.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.flatMap(({ values, range }) => {
if (range.includes("B10:E18")) return values.flat();
if (range.includes("B19:C22")) return values.flat();
return values[0][1];
});
// Put the array to the destination sheet using the search value.
const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstSheetName);
const range = dstSheet.getRange("A2:A" dstSheet.getLastRow()).createTextFinder(search).findNext();
if (range) {
dstSheet.getRange(range.getRow(), 2, 1, values.length).setValues([values]);
} else {
dstSheet.getRange(dstSheet.getLastRow() 1, 1, 1, values.length 1).setValues([[search, ...values]]);
}
}
I have attached a Screenshot explaining with color code where the data should go. https://i.stack.imgur.com/Yrfje.jpg [SCREENSHOT][1]
Also Shared two spreadsheets with actual data (Userform and Datasheet) for your reference https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA/edit?usp=sharing
CodePudding user response:
For example, in your script, the following modification is your expected result?
From:
const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(),{ ranges: ranges.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.flatMap(({ values, range }) => {
if (range.includes("B10:E18")) return values.flat();
if (range.includes("B19:C22")) return values.flat();
return values[0][1];
});
To:
const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(),{ ranges: ranges.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.flatMap(({ values, range }) => {
if (range.includes("B10:E18")) return values ? values.flat() : [];
if (range.includes("B19:C22")) return values ? values.flat() : [];
return values ? values[0][0] : "";
});
if (!search) return;