0

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

  • you might want to look at this workaround : https://stackoverflow.com/questions/11855960/triggered-functions-and-importrange/11857014#11857014 – Guillaume Devianne Mar 14 '23 at 22:41

0 Answers0