I am very new to Javascript and Apps Script. I want to create a function that updates another sheet based on a date in a certain range of the active sheet. I run and no error but it doesn't transfer value from active sheet to sheet named "January", in different target url google sheet

data master post

output 1

output 2

output 3

function myFunction4() {
const spreadsheetIds = [
{ id: "1ShPxDW9qhz4aWgaQ1G9oz7w1yh0-Wfe2VItet95UYks", sheetNames: 
["cab1"] },
{ id: "13Dx3ZOpV7baSTadSApIrVVccN-bHrPlHu240Aux0fo0", sheetNames: 
["cab2"] },
{ id: "14EVlqaP1ilXwopgi0ESvp_IKkSyROSF22WzWAcNAJWc", sheetNames: 
["cab3", "cab4"] }
];

const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const srcSheet = srcSpreadsheet.getSheetByName("January");
if (!srcSheet) return;
const date = new Date();
const ssName = srcSpreadsheet.getName();
const range = srcSheet.getRange("A2:C"   srcSheet.getLastRow());
let values = range.getValues();
if (values.filter(r => r.join("")).length == 0) return;
values = values.map(r => [...r, date, ssName]);
range.clearContent();
for (let i = 0; i < spreadsheetIds.length; i  ) {
const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i].id);
for (let j = 0; j < spreadsheetIds[i].sheetNames.length; j  ) {
  const targetSheet = 
dstSpreadsheet.getSheetByName(spreadsheetIds[i].sheetNames[j]);
  if (targetSheet) {
    targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, 
  values[0].length).setValues(values);
  }
  }
  }
  }

copyto Google Sheets Script adding date and source data in the next column

sample video

which is desired

I want when I click the send button. can add Date and source in the next column

Date = date when sent

source = the name of the workbook that sent it

    otherSheetName.getRange(1,getJumlahKolom 1).setValue("Date").setFontWeight("bold").setHorizontalAlignment("center");  
    otherSheetName.getRange(1,getJumlahKolom 2).setValue("source").setFontWeight("bold").setHorizontalAlignment("center");

Date & source, the function you want to join to myFunction3()

CodePudding user response：

I believe your goal is as follows.

• You want to achieve your goal by modifying your showing script.

In this case, how about the following modification?

Modified script:

function myFunction3() {
  const spreadsheetIds = [
    { id: "1aUoRKCnztsZAvbMwSa8Zk-gB-zfn1KeQnJgWIGRVu24", sheetNames: ["cab1"] },
    { id: "1Eme5Rb9_5kbGaT-HTy5ThR1WYUeR1fkQbn2-8wY-uUY", sheetNames: ["cab2"] },
    { id: "150DduDdhFJLC0LL7iOihYa6V1vaZckvtRxJUqCFFV9Q", sheetNames: ["cab3", "cab4"] }
  ];

  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = srcSpreadsheet.getSheetByName("input");
  if (!srcSheet) return;
  const date = new Date();
  const ssName = srcSpreadsheet.getName();
  const range = srcSheet.getRange("A2:C"   srcSheet.getLastRow());
  let values = range.getValues();
  if (values.filter(r => r.join("")).length == 0) return;
  values = values.map(r => [...r, date, ssName]);
  range.clearContent();
  for (let i = 0; i < spreadsheetIds.length; i  ) {
    const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i].id);
    for (let j = 0; j < spreadsheetIds[i].sheetNames.length; j  ) {
      const targetSheet = dstSpreadsheet.getSheetByName(spreadsheetIds[i].sheetNames[j]);
      if (targetSheet) {
        targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
      }
    }
  }
}

• When this script is run, the values are retrieved from the source sheet, and the retrieved values are put to the destination sheet by adding 2 columns of date and source.

Note:

• Please confirm the source and destination sheet names again. Because it seems that the sheet names of your showing script are different from your sample.