I wrote an app script to copy data from Sheet1 to sheet2. Both sheets are on the same google workbook. Data on sheet1 was obtained from a sheet3 using the IMPORTRANGE function on google sheet. Sheet3 is on a different workbook. The script executed successfully but the data isnt showing on sheet2. How can I fix this? please see the code below:
function tester() {
const SPREADSHEET_ID = "SPREADSHEET_ID";
const SHEETS = {
API2: SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(`API2`),
Pricedata2: SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`Pricedata2`)
};
SHEETS.API2.activate(); // Activate the API2 sheet
SpreadsheetApp.flush(); // Refresh the data in the API2 sheet
const VALUES = {
API2: SHEETS.API2.getDataRange().getValues(),
Pricedata2: SHEETS.Pricedata2.getDataRange().getValues()
};
const [updatedHeader, ...updatedValues] = VALUES.Pricedata2;
const newRow = [new Date(), ...Array(updatedHeader.length - 1).fill("")];
VALUES.API2.slice(1)
.map(i => [i[8], i[10]])
.forEach(([symbol, Price]) => {
if (updatedHeader.includes(symbol)) {
newRow[updatedHeader.indexOf(symbol)] = Price;
}
});
const updatedData = [[...updatedHeader], ...updatedValues, newRow];
var fillUpdatedData = updatedData.map(row => row.length < updatedHeader.length ? row.concat(Array(updatedHeader.length - row.length).fill("")) : row);
try {
SHEETS.Pricedata2.getRange(1, 1, fillUpdatedData.length, fillUpdatedData[0].length).setValues(fillUpdatedData);
} catch(e) {
// try again
fillUpdatedData = updatedData.map(row => row.length < updatedHeader.length ? row.concat(Array(updatedHeader.length - row.length).fill("")) : row);
SHEETS.Pricedata2.getRange(1, 1, fillUpdatedData.length, fillUpdatedData[0].length).setValues(fillUpdatedData);
}
}
I have reviewed the code in detail. I also looked at the source data in sheet3 to see if there is an issue but nothing seems to be working